Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
ActiveSheet.ShowAllData
This fails at the last line "ShowAllData method of worksheet class failed" if
there is no filtering active Private Sub CommandButton1_Click() 'Clears all filters Range("A3").Select ActiveSheet.ShowAllData End Sub End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
ActiveSheet.ShowAllData
This is a commandbutton placed on a worksheet?
If yes: with me if .filtermode then .showalldata end if end with If it's a commandbutton on a userform, then using Activesheet makes sense. oldjay wrote: This fails at the last line "ShowAllData method of worksheet class failed" if there is no filtering active Private Sub CommandButton1_Click() 'Clears all filters Range("A3").Select ActiveSheet.ShowAllData End Sub End Sub -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
ActiveSheet.ShowAllData
Hi oldjay,
This firstly tests if filtering dropdown arrows are on then tests if a filter is actually applied. Private Sub CommandButton1_Click() 'Clears all filters With ActiveSheet If .AutoFilterMode Then If .FilterMode Then ActiveSheet.ShowAllData End If End If End With End Sub -- Regards, OssieMac "oldjay" wrote: This fails at the last line "ShowAllData method of worksheet class failed" if there is no filtering active Private Sub CommandButton1_Click() 'Clears all filters Range("A3").Select ActiveSheet.ShowAllData End Sub End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
ActiveSheet.ShowAllData
You are getting the error, because there is no data being filtered. In other
words, although AutoFilter Mode is on all rows are visible. I don't normally use the On Error Resume Next, but this case it may make sense to use it. By the way, you don't have to select a sheet to run your code. Just reference your sheet by name. Put the sheet name in where I have Your Sheet Name Here or just use the code below (which is yours). Hope this helps! If so, let me know, click "YES" below. Private Sub CommandButton1_Click() 'Clears all filters On Error Resume Next Sheets("Your Sheet Name Here").ShowAllData On Error GoTo 0 End Sub Private Sub CommandButton1_Click() 'Clears all filters Range("A3").Select On Error Resume Next ActiveSheet.ShowAllData On Error GoTo 0 End Sub -- Cheers, Ryan "oldjay" wrote: This fails at the last line "ShowAllData method of worksheet class failed" if there is no filtering active Private Sub CommandButton1_Click() 'Clears all filters Range("A3").Select ActiveSheet.ShowAllData End Sub End Sub |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
ActiveSheet.ShowAllData
I figured there was a way to test if data is filtered or not. I hate using
the On Error Goto Next statement. Private Sub CommandButton1_Click() 'Clears all filters With Sheets("Your Sheet Name Here") If .AutoFilterMode And .FilterMode Then .ShowAllData End With End Sub -- Cheers, Ryan "OssieMac" wrote: Hi oldjay, This firstly tests if filtering dropdown arrows are on then tests if a filter is actually applied. Private Sub CommandButton1_Click() 'Clears all filters With ActiveSheet If .AutoFilterMode Then If .FilterMode Then ActiveSheet.ShowAllData End If End If End With End Sub -- Regards, OssieMac "oldjay" wrote: This fails at the last line "ShowAllData method of worksheet class failed" if there is no filtering active Private Sub CommandButton1_Click() 'Clears all filters Range("A3").Select ActiveSheet.ShowAllData End Sub End Sub |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
ActiveSheet.ShowAllData
That should have been just .ShowAllData. Don't need to repeat ActiveSheet in
the With / End With. Private Sub CommandButton1_Click() 'Clears all filters With ActiveSheet If .AutoFilterMode Then If .FilterMode Then .ShowAllData End If End If End With End Sub -- Regards, OssieMac |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
ActiveSheet.ShowAllData
Thanks for your contribution Ryan. You just forced me into testing this
because for some reason I had the idea that AutoFilterMode and FilterMode could not be used in the one line because I was sure that FilterMode produced an error if the filter dropdowns were not visible. However, my belief was not correct and I have now realized that the code can be simplified even more by just testing for FilterMode. Private Sub CommandButton1_Click() 'Clears all filters With ActiveSheet If .FilterMode Then .ShowAllData End With End Sub Tested in xl2002 and xl2007 and it works fine. -- Regards, OssieMac "Ryan H" wrote: I figured there was a way to test if data is filtered or not. I hate using the On Error Goto Next statement. Private Sub CommandButton1_Click() 'Clears all filters With Sheets("Your Sheet Name Here") If .AutoFilterMode And .FilterMode Then .ShowAllData End With End Sub -- Cheers, Ryan "OssieMac" wrote: Hi oldjay, This firstly tests if filtering dropdown arrows are on then tests if a filter is actually applied. Private Sub CommandButton1_Click() 'Clears all filters With ActiveSheet If .AutoFilterMode Then If .FilterMode Then ActiveSheet.ShowAllData End If End If End With End Sub -- Regards, OssieMac "oldjay" wrote: This fails at the last line "ShowAllData method of worksheet class failed" if there is no filtering active Private Sub CommandButton1_Click() 'Clears all filters Range("A3").Select ActiveSheet.ShowAllData End Sub End Sub |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
ActiveSheet.ShowAllData
All of these procedures remove filtering completely.
Is there a way to unfilter any particular column but leave the filter controls at the top for future use. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
ActiveSheet.ShowAllData
Hi Slim,
Following shows how to test if individual filter is on and how to turn off an individual filter. (You don't have to test if on before turning off; that is just part of my example. However, need to test for .AutoFilterMode otherwise the code errors if AutoFilter NOTon.). Sub FilterTest() With Worksheets("Sheet1") If .AutoFilterMode Then 'Test if filter 3 is applied If .AutoFilter.Filters(3).On Then 'Turn off filter 3 .AutoFilter.Range.AutoFilter Field:=3 End If End If End With End Sub One would think that .AutoFilter.Filters(3).On = False should work but it doesn't and I can't find any other method other than my example to turn if off. -- Regards, OssieMac |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Use a password in VBA ActiveSheet.protect & ActiveSheet.unprotect? | Excel Programming | |||
Troubles with ActiveSheet.ShowAllData | Excel Programming | |||
ShowAllData | Excel Discussion (Misc queries) | |||
ActiveSheet.ShowAllData shows everything - way to have hidden _not_ show up? | Excel Programming | |||
vba-showalldata | Excel Programming |