Microsoft excel help

I need to create an exit message on a file which appears before the file is saved reading Have all filters been removed? With yes allowing them to save and no cancelling the save request. Could someone tell me the best code to write to make this happen please? Thanks
«1

Comments

  • stragglebod
    stragglebod Posts: 1,324
    First Post Name Dropper First Anniversary
    Forumite
    This shows you how to run code when the worksheet closes:
    https://www.thespreadsheetguru.com/blog/2014/3/31/run-vba-macros-when-a-spreadsheet-opens-or-closes

    This code will check if the worksheet is filtered and if so remove the filter:
    If ActiveSheet.AutoFilterMode Then ActiveSheet.AutoFilter.ShowAllData
    


    You can probably extend that to loop through all worksheets in the workbook.
  • This shows you how to run code when the worksheet closes:
    https://www.thespreadsheetguru.com/blog/2014/3/31/run-vba-macros-when-a-spreadsheet-opens-or-closes

    This code will check if the worksheet is filtered and if so remove the filter:
    If ActiveSheet.AutoFilterMode Then ActiveSheet.AutoFilter.ShowAllData
    
    You can probably extend that to loop through all worksheets in the workbook.
    For Each ws in ActiveWorkBook.WorkSheets
    If ws.AutoFilterMode Then ws.AutoFilter.ShowAllData
    Next
    
  • lee8040
    lee8040 Posts: 554 Forumite
    For Each ws in ActiveWorkBook.WorkSheets
    If ws.AutoFilterMode Then ws.AutoFilter.ShowAllData
    Next
    

    So if I copy that exact in editor then what exactly will that do when the person try's to save the file and close it? Thanks
  • neilmcl
    neilmcl Posts: 19,460
    First Anniversary Name Dropper First Post
    Forumite
    lee8040 wrote: »
    So if I copy that exact in editor then what exactly will that do when the person try's to save the file and close it? Thanks
    It'll remove all filters.
  • lee8040
    lee8040 Posts: 554 Forumite
    When it's closed? So when the files reopened the option to use a filter is there as need to filter the data daily
  • rmg1
    rmg1 Posts: 3,123
    Name Dropper First Anniversary First Post
    Forumite
    There are a couple of events you could use.
    The BeforeSave event is the one you'll need according to your requirements, although there is also a WorkbookOpen event which might come in useful.
    With those two events, you could either:-
    1) Clear the filters before you save (could be annoying if you save the workbook and want to carry on working as it will clear the filter and you'll have to reapply it)
    2) Clear the filters when you first open the workbook (might be a better option).

    The code posted by WaywardDriver needs to go inside one of these events and then (when you open the workbook), you'll need to enable macros (they are blocked by default). It will leave the filters available but nothing will be filtered (i.e. it will show all rows).
    :wall: Flagellation, necrophilia and bestiality - Am I flogging a dead horse? :wall:

    Any posts are my opinion and only that. Please read at your own risk.
  • rmg1 wrote: »
    There are a couple of events you could use.
    The BeforeSave event is the one you'll need according to your requirements, although there is also a WorkbookOpen event which might come in useful.
    With those two events, you could either:-
    1) Clear the filters before you save (could be annoying if you save the workbook and want to carry on working as it will clear the filter and you'll have to reapply it)
    2) Clear the filters when you first open the workbook (might be a better option).

    The code posted by WaywardDriver needs to go inside one of these events and then (when you open the workbook), you'll need to enable macros (they are blocked by default). It will leave the filters available but nothing will be filtered (i.e. it will show all rows).
    Agree with above but may depend on user. If it was only me (or you) I'd Close the workbook and obey the prompt to Save. If someone other than yourself may need to consider how to make foolproof.
  • lee8040
    lee8040 Posts: 554 Forumite
    Does anyone no a code that displays a message "have filters been removed?" Yes allows them to save no returns them to the sheet?
  • Dime_Bar
    Dime_Bar Posts: 584
    Name Dropper First Post First Anniversary Combo Breaker
    Forumite
    So you just want to ask them have they removed the filters? Not remove them automatically or check automatically?
    The only way of finding the limits of the possible is by going beyond them into the impossible.
    Arthur C. Clarke
  • neilmcl
    neilmcl Posts: 19,460
    First Anniversary Name Dropper First Post
    Forumite
    lee8040 wrote: »
    Does anyone no a code that displays a message "have filters been removed?" Yes allows them to save no returns them to the sheet?
    Why would you want to display a message to ask them if you going to force them do so anyway - bad design.
This discussion has been closed.
Meet your Ambassadors

Categories

  • All Categories
  • 342.5K Banking & Borrowing
  • 249.9K Reduce Debt & Boost Income
  • 449.4K Spending & Discounts
  • 234.6K Work, Benefits & Business
  • 607.1K Mortgages, Homes & Bills
  • 172.8K Life & Family
  • 247.4K Travel & Transport
  • 1.5M Hobbies & Leisure
  • 15.8K Discuss & Feedback
  • 15.1K Coronavirus Support Boards