GIP
08-19-2010, 07:09 AM
I'm in excel 2003.
I have two worksheets in my excel workbook, "home" and "Data".
In the "home" sheet I have a combo box asking the user to choose one of 4 stages of operation. The combo box is related to cell A26 in the “home” sheet.
In the " Data " sheet I have data from row 7 - 120 in column F to V. Column Headers are in row 6.
Based on that choice that the user makes in the combo box in the "home" sheet, certain rows in the " Data " sheet should 'disappear from view' and the best way that I can think to do this is with Autofilter.
For example, if they choose “stage 1” in the combo box, then all rows that are (in column G) labeled as anything else other than “stage 1” will disappear.
That is the theory, but I don't seem able to actually make it happen. My code seems to work as soon as there is a change in the combo box and choose the correct cells for the autofilter, but then it doesn’t actually filter anything! Anyone able to help out by letting me know where the errors are in my code below, or providing a different code in order to accomplish my task?
Am I possibly using the wrong field number?
Code says basically:
- unprotect the worksheet,
- remove filters (in case they were already there),
- based on what the user chooses, add filters and filter the rows required in the “Data” sheet,
- select a cell in the "home" sheet
Code is as follows:
Private Sub ComboBox1_Change()
Application.ScreenUpdating = False
Worksheets("Data ").Unprotect ("password")
Sheets("Data ").AutoFilterMode = False
Dim sel1 As Variant
Set sel1 = Worksheets("home").Cells(1, 26)
Sheets("Data ").Range("F6:V6").AutoFilter Field:=2, Criteria1:=sel1
Worksheets("Data ").Protect Password:="password", DrawingObjects:=True, _
UserInterfaceOnly:=True, AllowFormattingCells:=True, _
AllowFormattingColumns:=True, AllowFormattingRows:=True
Worksheets("home").Cells(24, 10).Select
End Sub
Edited 20-Aug-10 by geekgirlau. Reason: insert vba tags
I have two worksheets in my excel workbook, "home" and "Data".
In the "home" sheet I have a combo box asking the user to choose one of 4 stages of operation. The combo box is related to cell A26 in the “home” sheet.
In the " Data " sheet I have data from row 7 - 120 in column F to V. Column Headers are in row 6.
Based on that choice that the user makes in the combo box in the "home" sheet, certain rows in the " Data " sheet should 'disappear from view' and the best way that I can think to do this is with Autofilter.
For example, if they choose “stage 1” in the combo box, then all rows that are (in column G) labeled as anything else other than “stage 1” will disappear.
That is the theory, but I don't seem able to actually make it happen. My code seems to work as soon as there is a change in the combo box and choose the correct cells for the autofilter, but then it doesn’t actually filter anything! Anyone able to help out by letting me know where the errors are in my code below, or providing a different code in order to accomplish my task?
Am I possibly using the wrong field number?
Code says basically:
- unprotect the worksheet,
- remove filters (in case they were already there),
- based on what the user chooses, add filters and filter the rows required in the “Data” sheet,
- select a cell in the "home" sheet
Code is as follows:
Private Sub ComboBox1_Change()
Application.ScreenUpdating = False
Worksheets("Data ").Unprotect ("password")
Sheets("Data ").AutoFilterMode = False
Dim sel1 As Variant
Set sel1 = Worksheets("home").Cells(1, 26)
Sheets("Data ").Range("F6:V6").AutoFilter Field:=2, Criteria1:=sel1
Worksheets("Data ").Protect Password:="password", DrawingObjects:=True, _
UserInterfaceOnly:=True, AllowFormattingCells:=True, _
AllowFormattingColumns:=True, AllowFormattingRows:=True
Worksheets("home").Cells(24, 10).Select
End Sub
Edited 20-Aug-10 by geekgirlau. Reason: insert vba tags