It turns on the Auto Filter, if one is not in place. Protect Password:="password", _ Contents:=True, User Interface Only:=True End With End Sub Sub Count Vis Rows() 'by Tom Ogilvy Dim rng As Range Set rng = Active Sheet. The zipped file is in xlsm format, and contains macros. This Workbook_Open macro checks for an Auto Filter on the sheet named Data. Sub Count Sheet Auto Filters() Dim i ARM As Long 'counts all worksheet autofilters 'even if all arrows are hidden If Active Sheet. Print "Auto Filter Mode: " & i ARM End Sub To see all the macros from this page, download the Auto Filter Worksheet Macros workbook. That allows macros to change the sheet, but users cannot make changes manually. Then, it protects the Data sheet, and sets the protection to user interface only. Be sure to enable macros when you open the workbook, if you want to test the macros.Class Module: 'These constants define the default restoration behaviors for the class Private Const DEFAULT_RESTORE_CALCULATION = True Private Const DEFAULT_RESTORE_DISPLAY_ALERTS = True Private Const DEFAULT_RESTORE_ENABLE_EVENTS = True Private Const DEFAULT_RESTORE_SCREEN_UPDATING = True 'Set this to true to ensure a persisted state is restored, even if the consumer forgets to restore Private Const RESTORE_ON_TERMINATE = True 'Private members stored in a Type Private this As TMembers Private Type TMembers Calculation As Xl Calculation State Display Alerts As Boolean Enable Events As Boolean Screen Updating As Boolean Restore CA As Boolean Restore DA As Boolean Restore EE As Boolean Restore SU As Boolean Is Persisted As Boolean Is Restored As Boolean End Type 'Set the default restoration behaviours on intialize Private Sub Class_Initialize() this. Restore DA End Property Public Property Let Restore Display Alerts(Value As Boolean) this. Restore DA = Value End Property Public Property Get Restore Enable Events() As Boolean Restore Enable Events = this. NOTE: These macros do not turn the Auto Filter off. You can change the field number in the i Show variable, to leave a different field's arrow visible. Auto Filter Field:=i, _ Visibledropdown:=True Else c. Screen Updating = True End Sub The following Excel Auto Filter VBA procedure shows the arrows for all fields except field 2.
The following macro hides the arrows for fields 1, 3 and 4 -- Case 1, 3, 4 You can change the field numbers in the first Case statement, to hide different arrows. Screen Updating = True End Sub Sub Show ALLArrows() 'shows all arrows in headng row Dim c As Range Dim i As Integer Dim rng As Range Set rng = Active Sheet. Sub Show Arrows Except One() 'shows all arrows except ' in specified field number Dim c As Range Dim rng As Range Dim i As Long Dim i Hide As Long Set rng = Active Sheet. Screen Updating = True End Sub Sub Copy Filter() 'by Tom Ogilvy Dim rng As Range Dim rng2 As Range With Active Sheet.
I have an Excel file that generates reports using VBA.
A portion of that process copies data from 1 file to append into another then saves the destination file.
These 2 users recently received new desktop computers with 32 bit Windows 7, MS Office 2013 (15.0.4787.1002).
For these 2 users, the VBA prompts with the "Do you want to Save the file? When they click Yes, the procedure completes but the file is not saved.