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
   DataCriteria = "*"
Else
   DataCriteria = ComboBox1.Value
End If
Range("G9:G" & rCol).AutoFilter 1, DataCriteria


Application.Calculation = xlAutomatic
Application.ScreenUpdating = True


End Sub

 

[kbishop94]

How about

Perfect!! Many thanks, sir.

 

[Fluff]

Glad to help & thanks for the feedback

 

[kbishop94]

Ok, I was playing around with it and attempting to add another Combobox (Combobox2) to apply the same filtering technique but for Column J ("Issued by"). So I see that its not as simple as just changing the names/values to match the new criteria.(?) Is there a 'trick' when trying to have mutiple combo boxes filtering different columns? Thanks (again)

My new code:

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
   DataCriteria = "*"

Else
   DataCriteria = ComboBox1.Value

End If

Range("G9:G" & rCol).AutoFilter 1, DataCriteria

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
   DataCriteria1 = "*"

Else
   DataCriteria1 = ComboBox2.Value
End If

Range("J9:J" & sCol).AutoFilter 1, DataCriteria1

Application.Calculation = xlAutomatic
Application.ScreenUpdating = True

End Sub

 

[Fluff]

What are the 1st & last columns in your data?

 

[kbishop94]

What are the 1st & last columns in your data?

Column A and Column Q. Column J is what I was trying to apply the filter to.

Here is the additional code from the Workbook module that I left out from my previous post:

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"
.AddItem "AEGIS"
.AddItem "ALACK"
.AddItem "ALBEMARLE"
.AddItem "AMC"
.AddItem "AMTRAK"

continues....  (300+ more)

.Value = "Show all Customers"
End With


With Sheets("Seatex Incident Log").ComboBox2
.AddItem "Show all: Issued By"
.AddItem "AL BROOKS"
.AddItem "ANSELMO GARCIA"
.AddItem "CAMERON MILLER"
.AddItem "CAROLYN GONZALEZ"
.AddItem "CINDY MOREAU"
.AddItem "CINDY GARCIA"

continues...  (50+ more names)


.Value = "Show all: Issued By"
End With


Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True


End Sub
    
Private Sub Workbook_Close()


Application.DisplayFullScreen = False
ActiveWindow.DisplayHeadings = True


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


End Sub

 

[Fluff]

Try this

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
   DataCriteria = "*"

Else
   DataCriteria = ComboBox1.Value

End If

Range("A9:Q" & rCol).AutoFilter 7, DataCriteria

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
   DataCriteria1 = "*"

Else
   DataCriteria1 = ComboBox2.Value
End If

Range("A9:Q" & sCol).AutoFilter 10, DataCriteria1

Application.Calculation = xlAutomatic
Application.ScreenUpdating = True

End Sub

 

[kbishop94]

Try this

At first it didnt work... I was getting "Run-time error '1004' AutoFilter method of Range Class Failed"

But I realized that once I highlighted the row with the headers and selected the Filter button on the Data tab, it works fine. But, I dont really understand why. The code before you posted (when it was just 1 column) worked fine without having to "turn on" the Filter on the Data tab(?) Its not a problem as the Comboboxs both work now as a way for the user to select the criteria he/she needs, it was just a little 'cleaner' looking the other way. thank you so much for all your help. -Keith

 

[kbishop94]

I just found another issue... it doesnt like blanks in the columns that are filtered. Example: I select a customer, it filters and hides all the ones that do not match the criteria... but then when i select the 'show all customers' choice, it only shows the rows that have a value/text in that column. (which makes sense now that I have thought about it because you told it with your code to look for a wildcard ("*") and that doesnt take inconsideration blanks.
Is it possible to have blanks show up (unhide) as well as the wildcard? Thank you (again, and again... )