Simple VBA question...

Hi all,

I am posting this for my fiancee so I don't know any of the details, sorry.  If more info is needed please ask and I will pass it on....

The problem with the 'Display All' part of the program is if everything is
already displayed the macro I have created is not possible (i.e. 'Data',
'Filter', 'Show All').  Is there a way you can put in an 'if,' 'then,'
scenario?  If so can you please write this code for me.  Thank you.
------------
Sub Sort_by_baseprob()
'
' Sort_by_baseprob Macro
' Macro recorded 12/01/2000 by Alison Watkiss
'

'
    Sheets("Consulting General").Select
    Cells.Select
    Range("A9").Activate
    Selection.Sort Key1:=Range("I2"), Order1:=xlDescending,
Key2:=Range("F2") _
        , Order2:=xlDescending, Header:=xlYes, OrderCustom:=1, MatchCase:= _
        False, Orientation:=xlTopToBottom
    Range("A2").Select
    ActiveSheet.Next.Select
    Cells.Select
    Selection.Sort Key1:=Range("I2"), Order1:=xlDescending,
Key2:=Range("F2") _
        , Order2:=xlDescending, Header:=xlYes, OrderCustom:=1, MatchCase:= _
        False, Orientation:=xlTopToBottom
    Range("A1").Select
    ActiveSheet.Next.Select
    Cells.Select
    Range("A2").Activate
    Selection.Sort Key1:=Range("I2"), Order1:=xlDescending,
Key2:=Range("F2") _
        , Order2:=xlDescending, Header:=xlYes, OrderCustom:=1, MatchCase:= _
        False, Orientation:=xlTopToBottom
    Range("A2").Select
    ActiveSheet.Next.Select
    Cells.Select
    Range("A2").Activate
    Selection.Sort Key1:=Range("I2"), Order1:=xlDescending,
Key2:=Range("F2") _
        , Order2:=xlDescending, Header:=xlYes, OrderCustom:=1, MatchCase:= _
        False, Orientation:=xlTopToBottom
    Range("A2").Select
    ActiveSheet.Next.Select
    Cells.Select
    Range("A2").Activate
    Selection.Sort Key1:=Range("I2"), Order1:=xlDescending,
Key2:=Range("F2") _
        , Order2:=xlDescending, Header:=xlYes, OrderCustom:=1,
MatchCase:=False _
        , Orientation:=xlTopToBottom
    Range("A2").Select
    ActiveSheet.Next.Select
    Cells.Select
    Range("A2").Activate
    Selection.Sort Key1:=Range("I2"), Order1:=xlDescending,
Key2:=Range("F2") _
        , Order2:=xlDescending, Header:=xlYes, OrderCustom:=1,
MatchCase:=False _
        , Orientation:=xlTopToBottom
    Range("A2").Select
    Sheets("Consulting General").Select
End Sub
Sub DisplayBase3orMore()
'
' DisplayBase3orMore Macro
' Macro recorded 12/01/2000 by Alison Watkiss
'

'

    Sheets("Consulting General").Select
    Selection.AutoFilter Field:=9, Criteria1:=">=3", Operator:=xlAnd
    ActiveSheet.Next.Select
    Selection.AutoFilter
    Selection.AutoFilter Field:=9, Criteria1:=">=3", Operator:=xlAnd
    ActiveSheet.Next.Select
    Selection.AutoFilter Field:=9, Criteria1:=">=3", Operator:=xlAnd
    ActiveSheet.Next.Select
    Selection.AutoFilter Field:=9, Criteria1:=">=3", Operator:=xlAnd
    ActiveSheet.Next.Select
    Selection.AutoFilter Field:=9, Criteria1:=">=3", Operator:=xlAnd
    ActiveSheet.Next.Select
    Selection.AutoFilter Field:=9, Criteria1:=">=3", Operator:=xlAnd
    Sheets("Consulting General").Select
End Sub
Sub Display_All()
'
' Display_All Macro
' Macro recorded 12/01/2000 by Alison Watkiss
'

'
    Sheets("Consulting General").Select
    ActiveSheet.ShowAllData
    Sheets("Defence").Select
    ActiveSheet.ShowAllData
    ActiveSheet.Next.Select
    ActiveSheet.ShowAllData
    Sheets("Multimedia").Select
    ActiveSheet.ShowAllData
    Sheets("Training").Select
    ActiveSheet.ShowAllData
    Sheets("Brisbane").Select
    ActiveSheet.ShowAllData
    Sheets("Consulting General").Select
End Sub
Sub SortBase()
'
' SortBase Macro
' Macro recorded 12/01/2000 by Alison Watkiss
'

'
    Sheets("Consulting General").Select
    Cells.Select
    Range("A2").Activate
    Selection.Sort Key1:=Range("I2"), Order1:=xlDescending, Header:=xlYes, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
    Range("A2").Select
    ActiveSheet.Next.Select
    Cells.Select
    Range("A2").Activate
    Selection.Sort Key1:=Range("I2"), Order1:=xlDescending, Header:=xlYes, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
    Range("A1").Select
    ActiveSheet.Next.Select
    Cells.Select
    Range("A2").Activate
    Selection.Sort Key1:=Range("I2"), Order1:=xlDescending, Header:=xlYes, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
    Range("A2").Select
    ActiveSheet.Next.Select
    Cells.Select
    Range("A2").Activate
    Selection.Sort Key1:=Range("I2"), Order1:=xlDescending, Header:=xlYes, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
    Range("A2").Select
    ActiveSheet.Next.Select
    Cells.Select
    Range("A2").Activate
    Selection.Sort Key1:=Range("I2"), Order1:=xlDescending, Header:=xlYes, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
    Range("A2").Select
    ActiveSheet.Next.Select
    Cells.Select
    Range("A2").Activate
    Selection.Sort Key1:=Range("I2"), Order1:=xlDescending, Header:=xlYes, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
    Range("A2").Select
    Sheets("Consulting General").Select
End Sub
Sub SortProbability()
'
' SortProbability Macro
' Macro recorded 12/01/2000 by Alison Watkiss
'

'
    Sheets("Consulting General").Select
    Cells.Select
    Range("A2").Activate
    Selection.Sort Key1:=Range("F2"), Order1:=xlDescending, Header:=xlYes, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
    Range("A2").Select
    ActiveSheet.Next.Select
    Cells.Select
    Range("A2").Activate
    Selection.Sort Key1:=Range("F2"), Order1:=xlDescending, Header:=xlYes, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
    Range("A1").Select
    ActiveSheet.Next.Select
    Cells.Select
    Range("A2").Activate
    Selection.Sort Key1:=Range("F2"), Order1:=xlDescending, Header:=xlYes, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
    Range("A2").Select
    ActiveSheet.Next.Select
    Cells.Select
    Range("A2").Activate
    Selection.Sort Key1:=Range("F2"), Order1:=xlDescending, Header:=xlYes, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
    Range("A2").Select
    ActiveSheet.Next.Select
    Cells.Select
    Range("A2").Activate
    Selection.Sort Key1:=Range("F2"), Order1:=xlDescending, Header:=xlYes, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
    Range("A2").Select
    ActiveSheet.Next.Select
    Cells.Select
    Range("A2").Activate
    Selection.Sort Key1:=Range("F2"), Order1:=xlDescending, Header:=xlYes, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
    Range("A2").Select
    Sheets("Consulting General").Select
End Sub

Sub Unsort()
'
' Unsort Macro
' Macro recorded 12/01/2000 by Alison Watkiss
'

'
    Sheets("Consulting General").Select
    Cells.Select
    Range("A9").Activate
    Selection.Sort Key1:=Range("I2"), Order1:=xlDescending,
Key2:=Range("F2") _
        , Order2:=xlDescending, Header:=xlYes, OrderCustom:=1, MatchCase:= _
        False, Orientation:=xlTopToBottom
    Range("A2").Select
    ActiveSheet.Next.Select
    Cells.Select
    Selection.Sort Key1:=Range("I2"), Order1:=xlDescending,
Key2:=Range("F2") _
        , Order2:=xlDescending, Header:=xlYes, OrderCustom:=1, MatchCase:= _
        False, Orientation:=xlTopToBottom
    Range("A1").Select
    ActiveSheet.Next.Select
    Cells.Select
    Range("A2").Activate
    Selection.Sort Key1:=Range("I2"), Order1:=xlDescending,
Key2:=Range("F2") _
        , Order2:=xlDescending, Header:=xlYes, OrderCustom:=1, MatchCase:= _
        False, Orientation:=xlTopToBottom
    Range("A2").Select
    ActiveSheet.Next.Select
    Cells.Select
    Range("A2").Activate
    Selection.Sort Key1:=Range("I2"), Order1:=xlDescending,
Key2:=Range("F2") _
        , Order2:=xlDescending, Header:=xlYes, OrderCustom:=1, MatchCase:= _
        False, Orientation:=xlTopToBottom
    Range("A2").Select
    ActiveSheet.Next.Select
    Cells.Select
    Range("A2").Activate
    Selection.Sort Key1:=Range("I2"), Order1:=xlDescending,
Key2:=Range("F2") _
        , Order2:=xlDescending, Header:=xlYes, OrderCustom:=1,
MatchCase:=False _
        , Orientation:=xlTopToBottom
    Range("A2").Select
    ActiveSheet.Next.Select
    Cells.Select
    Range("A2").Activate
    Selection.Sort Key1:=Range("I2"), Order1:=xlDescending,
Key2:=Range("F2") _
        , Order2:=xlDescending, Header:=xlYes, OrderCustom:=1,
MatchCase:=False _
        , Orientation:=xlTopToBottom
    Range("A2").Select
    Sheets("Consulting General").Select
End Sub


GabeinOZAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

tureCommented:
GabeinOZ,

Use ActiveSheet.FilterMode, like this:

Sub Display_All()
  Sheets("Consulting General").Select
  If ActiveSheet.FilterMode Then   ActiveSheet.ShowAllData
  Sheets("Defence").Select
  If ActiveSheet.FilterMode Then   ActiveSheet.ShowAllData
  ActiveSheet.Next.Select
  If ActiveSheet.FilterMode Then   ActiveSheet.ShowAllData
  Sheets("Multimedia").Select
  If ActiveSheet.FilterMode Then   ActiveSheet.ShowAllData
  Sheets("Training").Select
  If ActiveSheet.FilterMode Then   ActiveSheet.ShowAllData
  Sheets("Brisbane").Select
  If ActiveSheet.FilterMode Then   ActiveSheet.ShowAllData
  Sheets("Consulting General").Select
End Sub

Ture Magnusson
Karlstad, Sweden
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
antratCommented:
hi GabeinOZ

how about something like below

Sub Display_All()
'
' Display_All Macro
' Macro recorded 12/01/2000 by Alison Watkiss
Sheets("Consulting General").Select
If FiterMode = true then
ActiveSheet.ShowAllData
End if
Sheets("Defence").Select
If FiterMode = true then
    ActiveSheet.ShowAllData
end if
    ActiveSheet.Next.Select
If FiterMode = true then
    ActiveSheet.ShowAllData
End if
     Sheets("Multimedia").Select
If FiterMode = true then
    ActiveSheet.ShowAllData
end if
    Sheets("Training").Select
If FiterMode = true then
    ActiveSheet.ShowAllData
end if
    Sheets("Brisbane").Select
 If FiterMode = true then
ActiveSheet.ShowAllData
end if
    Sheets("Consulting General").Select
End Sub


antrat

0
tureCommented:
:o)
0
GabeinOZAuthor Commented:
Ok Ture My girl says that both your answers work so its first in best dressed....Thanks for the help mate

Cheers,
Gabe
0
tureCommented:
You are welcome! I'm glad that I could be of assistance.

/Ture
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Office

From novice to tech pro — start learning today.