Bypass a function in a Macro
 

I am building a Macro that can run on a worksheet training matrix whether if
is filtered or not. It works beautifully as long as the file is filtered but
need to work both ways.

Here is the text I have for my macro so far

Sheets("Tech Plan").Select
ActiveSheet.Unprotect ("MPT8883900")
ActiveSheet.ShowAllData
ActiveSheet.Range("$A$10:$BO$569").AutoFilter Field:=53
ActiveSheet.Range("$A$10:$BO$569").AutoFilter Field:=53, Criteria1:="X"
ActiveSheet.Protect ("MPT8883900")

I am trying to bypass the ShowAllData line if it has already been
unfiltered. I have about 60 files to run this on and would appreciate any
help.

Bypass a function in a Macro
 

Could you not do something like

If ActiveSheet.FilterMode Then
ActiveSheet.ShowAllData
End If

--
Hope this helps,

Daniel Pineault
http://www.cardaconsultants.com/
For Access Tips and Examples: http://www.devhut.net
Please rate this post using the vote buttons if it was helpful.



"bevchapman" wrote:

I am building a Macro that can run on a worksheet training matrix whether if
is filtered or not. It works beautifully as long as the file is filtered but
need to work both ways.

Here is the text I have for my macro so far

Sheets("Tech Plan").Select
ActiveSheet.Unprotect ("MPT8883900")
ActiveSheet.ShowAllData
ActiveSheet.Range("$A$10:$BO$569").AutoFilter Field:=53
ActiveSheet.Range("$A$10:$BO$569").AutoFilter Field:=53, Criteria1:="X"
ActiveSheet.Protect ("MPT8883900")

I am trying to bypass the ShowAllData line if it has already been
unfiltered. I have about 60 files to run this on and would appreciate any
help.

Bypass a function in a Macro
 

hi
read up on the filter mode property in vb help. it's boolean and true if
the sheet contains a filtered list.
to bypass the showalldata line, you would need something like this....
if activesheet.filtermode = true then
activesheet.showalldata
else
do something else '?????
end if

regards
FSt1

"bevchapman" wrote:

I am building a Macro that can run on a worksheet training matrix whether if
is filtered or not. It works beautifully as long as the file is filtered but
need to work both ways.

Here is the text I have for my macro so far

Sheets("Tech Plan").Select
ActiveSheet.Unprotect ("MPT8883900")
ActiveSheet.ShowAllData
ActiveSheet.Range("$A$10:$BO$569").AutoFilter Field:=53
ActiveSheet.Range("$A$10:$BO$569").AutoFilter Field:=53, Criteria1:="X"
ActiveSheet.Protect ("MPT8883900")

I am trying to bypass the ShowAllData line if it has already been
unfiltered. I have about 60 files to run this on and would appreciate any
help.

Bypass a function in a Macro
 

With worksheets("SomeSheetNameHere")
'to remove the filter and the arrows
.AutoFilterMode = False

'or to just show all the data and keep the arrows
If .FilterMode Then
.ShowAllData
End If
End With


Or you could ignore errors, too:

on error resume next
ActiveSheet.ShowAllData 'keeps the arrows, shows all the data.
on error goto 0

bevchapman wrote:

I am building a Macro that can run on a worksheet training matrix whether if
is filtered or not. It works beautifully as long as the file is filtered but
need to work both ways.

Here is the text I have for my macro so far

Sheets("Tech Plan").Select
ActiveSheet.Unprotect ("MPT8883900")
ActiveSheet.ShowAllData
ActiveSheet.Range("$A$10:$BO$569").AutoFilter Field:=53
ActiveSheet.Range("$A$10:$BO$569").AutoFilter Field:=53, Criteria1:="X"
ActiveSheet.Protect ("MPT8883900")

I am trying to bypass the ShowAllData line if it has already been
unfiltered. I have about 60 files to run this on and would appreciate any
help.

--

Dave Peterson