Results 1 to 5 of 5
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    UDF function and macro (2003)

    I am trying to use the attached code. It is supposed to display a dialog box providing details about the filtered list. My spreadsheet header starts on row 3 (I would rather have it start on row 1 but to test this procedure, I tried to adapt to what the code said). I turned the autofilter on and selected some criteria and then ran the macro - CountFilteredRows. I keep getting the message 'Please turn on the AutoFilter feature". The autofilter is on. I put the code in my Personal.xls sheet. Can you tell me why this code isn't working?

    I figured it out - the code should be in "This workbook". How would I adjust the code so that it is in the Personal.xls to be available for all spreadsheets?

    Thank you

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: UDF function and macro (2003)

    The macro works as intended when I try it, so we'd have to see your workbook (the one with the autofiltered list) to know what goes wrong.

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: UDF function and macro (2003)

    The code works when I put the code in ThisWorkbook of the spreadsheet. I would like to change the code to look at ThisWorkbook of the Personal.xls sheet. I thought I could change Set sht = ThisWorkbook.ActiveSheet to Set sht = Personal.xls!ThisWorkbook.ActiveSheet or am I all wrong??

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: UDF function and macro (2003)

    You don't want the code to look at ThisWorkbook of Personal.xls, for Personal.xls is only meant as a container for macros. It should be invisible. Use

    Set sht = ActiveWorkbook.ActiveSheet

  5. #5
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: UDF function and macro (2003)

    Thanks Hans - that worked.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •