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?
 
tureConnect With a Mentor Commented:
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
 
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
All Courses

From novice to tech pro — start learning today.