+ Reply to Thread
Results 1 to 10 of 10

Auto reapply Autofilter

  1. #1
    Registered User
    Join Date
    10-02-2012
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    13

    Arrow Auto reapply Autofilter

    Hell everyone. So I'm making a spreadsheet for work that record...well a bunch of stuff, that's not really important. Anyway I've set up an autofilter that hides an entire row if it's first cell (i.e. the detail no) is blank. I can get it to work quite easily in excel 2010 using any basic macro, but the minute it's put on the work computer, which uses Excel 2003, I get runtime error 438 which I'm assuming means the macro is not compatible. Unfortunately I'm not very macro savvy, so any huge macros I've managed to find online I haven't been able to work for myself. Also I need to protect the document, so it needs to work along with that. I've attached my file of the document, any help would be appreciated. Thanks.

    TEST.xls

  2. #2
    Valued Forum Contributor
    Join Date
    09-25-2011
    Location
    Eastbourne, England
    MS-Off Ver
    Excel 2010
    Posts
    1,089

    Re: Auto reapply Autofilter

    The obvious answer is to update the work computer to Excel 2010 ;>)

    If it is just the "hide" part of the macro that is failing, perhaps you can record your own macro on the work computer to hide rows and check the syntax. You ciould then amend the macro accordingly (bearing in mind that the macro may then not work on your computer).

    I'm sorry, but not having access to Excel 2003, I cannot be of more assistance.

    Regards

    Alastair

  3. #3
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    work: 2016 on Win10 (notebook), private: 2019 on Win10 (desktop), 2019 on Win11 (notebook)
    Posts
    8,197

    Re: Auto reapply Autofilter

    Hi, Loobrush

    why
    Hell
    Excel prior to 2007 doesnīt know about
    Please Login or Register  to view this content.
    To apply a filter in 2003 you should use something like
    Please Login or Register  to view this content.
    to apply the Autofilter and set the Criteria on Column B for the Bandname.

    To switch the Autofilter off you may either use
    Please Login or Register  to view this content.
    or
    Please Login or Register  to view this content.
    Ciao,
    Holger
    Use Code-Tags for showing your code: [code] Your Code here [/code]
    Please mark your question Solved if there has been offered a solution that works fine for you

  4. #4
    Registered User
    Join Date
    10-02-2012
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    13

    Re: Auto reapply Autofilter

    Quote Originally Posted by aydeegee View Post
    The obvious answer is to update the work computer to Excel 2010 ;>)

    If it is just the "hide" part of the macro that is failing, perhaps you can record your own macro on the work computer to hide rows and check the syntax. You ciould then amend the macro accordingly (bearing in mind that the macro may then not work on your computer).

    I'm sorry, but not having access to Excel 2003, I cannot be of more assistance.

    Regards

    Alastair
    Yea, that was the first thing I tried, he doesn't want to go for it, upgrading costs money and all that. I did offer up a solution to record a macro for auto refresh, but then that would require the use of a button to trigger it and he doesn't want that, he wants it automatic.

    Quote Originally Posted by HaHoBe View Post
    why
    Sorry about that, I was raised by wolves so I'm not very good with people, I probably should have began with 'hello' instead. xP

    Quote Originally Posted by HaHoBe View Post
    To apply a filter in 2003 you should use something like
    Please Login or Register  to view this content.
    to apply the Autofilter and set the Criteria on Column B for the Bandname.
    Thanks, I tried that code, but now I'm getting runtime error 1004; application defined or object error?

  5. #5
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    work: 2016 on Win10 (notebook), private: 2019 on Win10 (desktop), 2019 on Win11 (notebook)
    Posts
    8,197

    Re: Auto reapply Autofilter

    Hi, Loobrush,

    no problem as long as we have something we may howl to together (oh no, not the moon ).

    Maybe try this code which didnīt bring up the run-time error on testing in your sample workbook:
    Please Login or Register  to view this content.
    On Sheets Design, Fitting, and PartMark the Autofilter is set up to start on Row 2 while in the other sheets it will start in Row 1.

    HTH,
    Holger

  6. #6
    Registered User
    Join Date
    10-02-2012
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    13

    Re: Auto reapply Autofilter

    Quote Originally Posted by HaHoBe View Post
    Hi, Loobrush,

    no problem as long as we have something we may howl to together (oh no, not the moon ).

    Maybe try this code which didnīt bring up the run-time error on testing in your sample workbook:
    Please Login or Register  to view this content.
    On Sheets Design, Fitting, and PartMark the Autofilter is set up to start on Row 2 while in the other sheets it will start in Row 1.

    HTH,
    Holger
    I think we're getting there slowly. The issue is that the way the sheet works is that placing a tick in any of the checkboxes in 'Detail no.' will refresh the corresponding sheets autofilter, so is there something I can use in place of 'activesheet' as the sheet itself is not going to be active when i want it refreshed?

    [Edit] Also I'm not entirely sure what happened but it sort of deleted the rows it was filtering aswell for some reason. Not sure why. :/
    Last edited by Loobrush; 03-17-2013 at 12:27 PM.

  7. #7
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    work: 2016 on Win10 (notebook), private: 2019 on Win10 (desktop), 2019 on Win11 (notebook)
    Posts
    8,197

    Re: Auto reapply Autofilter

    Hi, Loobrush,

    please do not quote whole posts which are located directly over your post (see forum rules for more information).

    If itīs not the active sheet may just replace it by something like (here for Sheet Planning in C2)
    Please Login or Register  to view this content.
    Ciao,
    Holger

  8. #8
    Registered User
    Join Date
    10-02-2012
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    13

    Re: Auto reapply Autofilter

    Sorry about that.

    At any rate that seems to have worked perfectly on the planning sheet, how would I go about amending that to work for all of them?

  9. #9
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    work: 2016 on Win10 (notebook), private: 2019 on Win10 (desktop), 2019 on Win11 (notebook)
    Posts
    8,197

    Re: Auto reapply Autofilter

    Hi, Loobrush,

    to my opinion re-think the layout of the workbook. You choose Form-controls for each option - I might have taken an UserForm from VBA with a Listbox (for the names of the sheets) and MultiSelect 2-frmMultiSelectExtended for an array of choices or a ComboBox.

    If you want to stay with your form-controls (I think you selected the cells in row 2 for the sheet names) maybe change the code to read
    Please Login or Register  to view this content.
    and call the changed macro from within every macro you have created so far. Please note that the cell values have to be equal to the sheetnames (I tested with I2 and a run time error was raised as the cell shows 'Manual Milling' while the sheet is called 'ManualMilling').

    Ciao,
    Holger

  10. #10
    Registered User
    Join Date
    10-02-2012
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    13

    Re: Auto reapply Autofilter

    In hindsight that might have been the better option, but I didn't think that far ahead when I was making it, I wasn't even aware I was going to be using any complicated macros. :P

    I'll give that code a go and see if it works, else it seems like I'll have to resort to using a button, my employers worst enemy, but that's how it'll have to be. Thanks for all your help Holger.

    Ciao.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1