Hi Experts,
Not sure if this can be done or not, and the Question Title filed wasn't long enough to say everything I wanted to do here, so let me expand that a bit:
I am looking for a way to have a UserForm appear whenever the ActiveSheet &/or Workbook changes AND the sheet has hidden rows (filtered or manually hidden) OR has hidden columns, and offer 3 command buttons {Unhide &/or ShowAll filtered rows; Unhide columns; Unhide Both Rows and columns}.
I have experimented with trying to use the following methods to detect this state (dealing with AutoFilters only) but could either not get them to work without errors or if they got triggered on a given sheet where the AutoFilters were enabled (I think that happened with # 2) below), the statements following "Then" were supposed to make appear depressed the toolbar button that called a larger macro of which this is a part (as a kind of visual cue), but then that button still appeared depressed when I switched to a sheet that had no AutoFilters. (I might mention that this code resides in an xla module that deploys a custom toolbar with the button calling this macro residing thereon).
So striking out with the depressing the toolbar button approach, I thought maybe I could accomplish what I wanted, and possibly more, by displaying this UserForm with expanded controls and hopefully more robust in it's detection abilities of hidden data ... I have other code I can use to make the form auto dismiss itself (I'll problably add the ability for the user to specify the interval after which it will dismiss). I have done parts of this process before in other contexts (and hopefully can apply same to this effort), but the trouble I am having right now is just getting the detection engine to work, and have it work anytime the ActiveSheet &/or Workbook changes. I guess I might have to consider, too, whether the ActiveSheet is protected (can I somehow, still display this message, even if the controls are not going to be activated unless the sheet is unprotected?). And since this app has to work across Excel 2K to Excel 2003, I know the Worksheet protection structure has changed across those versions, too, complicating this task further ... I have developed some other code that deals with this issue that I can probably employ to determine the Excel version and, if applicable, protection component status so I guess the major issue right now is how to design the detection part of this process as outlined in the 2nd paragraph above? I can probably do most of the rest of this myself ...
Hope this was clear and thanks for any suggestions!
Jeff
partial code follows:
1) If FilterMode = True Then
2) If ActiveSheet.FilterMode = True Then
3) If IsFiltered(ws:="ActiveShee
t") Then
Private Function IsFiltered(ws As Worksheet) As Boolean
'Courtesy of Patrick Matthews at another EE topic
Dim af As AutoFilter
Dim f As Filter
Set af = ws.AutoFilter
IsFiltered = False
For Each f In af.Filters
If f.On Then
IsFiltered = True
Exit Function
End If
Next
End Function
