code for hiding rows based on selection from combobox... got it working(!) but.....

[kbishop94]

My Combobox lists the customers that are shown in column G (see pics below). Based on the value that is selected, all the rows except those listing that customer(value) are hidden.
So... it all works fine, *but* I cant figure out how when the first choice of "Show all Customers" is selected that ALL the rows be shown (unhidden)

my code:

WORKBOOK CODE:

Sub Workbook_Open()


Worksheets("Seatex Incident Log").Activate


Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = False


ActiveWindow.ScrollColumn = 1


With Rows("10:356")
    .Hidden = False
End With
    
With Sheets("Seatex Incident Log").ComboBox1
.AddItem "Show all Customers"
.AddItem "Internal Seatex Incident"
.AddItem "AEGIS"
.AddItem "A.I.M.P"
.AddItem "A-1 CHEMICAL EQUIP. & SUPPL"
.AddItem "ACP"

(this continues for another 300+ customers)


.Value = "Show all Customers"


End With


Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True


End Sub
    
Private Sub Workbook_Close()

SHEET1 CODE:

Private Sub ComboBox1_Change()


Application.Calculation = xlAutomatic
Application.ScreenUpdating = False


ActiveWindow.ScrollColumn = 1


Dim DataCriteria As String


Dim rCol As Long
    rCol = ActiveSheet.UsedRange.Rows.Count
    For Each Cell In Range(Cells(10, 7), Cells(rCol, 7))


DataCriteria = ComboBox1.Value


If Cell = DataCriteria Then Rows(Cell.Row).Hidden = False Else Rows(Cell.Row).Hidden = True


Next


Application.Calculation = xlAutomatic
Application.ScreenUpdating = False


End Sub



Application.DisplayFullScreen = False
ActiveWindow.DisplayHeadings = True


With Rows("10:356")
    .Hidden = False
End With


End Sub

my spreadsheet (for visual explanation)

spreadsheet before making a selection (showing all rows):

spreadsheet after making a selection:

currently when choosing "show all customers" (I need this so when the selection "show all customers" is chosen ALL rows are shown.)

 

[Fluff]

How about

Private Sub ComboBox1_Change()

Dim rCol As Long
Dim DataCriteria As String

Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False

ActiveWindow.ScrollColumn = 1

rCol = ActiveSheet.UsedRange.Rows.Count

If ComboBox1.Value = "Show all Customers" Then
   Range("A9:Q" & rCol).AutoFilter 7
Else
   Range("A9:Q" & rCol).AutoFilter 7, ComboBox1.Value

End If



Application.Calculation = xlAutomatic
Application.ScreenUpdating = True

End Sub



Private Sub ComboBox2_Change()

Dim sCol As Long
Dim DataCriteria1 As String

Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False

ActiveWindow.ScrollColumn = 1

sCol = ActiveSheet.UsedRange.Rows.Count
If ComboBox2.Value = "Show all: Issued By" Then
   Range("A9:Q" & sCol).AutoFilter 10

Else
    Range("A9:Q" & sCol).AutoFilter 10, ComboBox2.Value
End If


Application.Calculation = xlAutomatic
Application.ScreenUpdating = True

End Sub