Filter rows on sheet 2 when a certain option is chosen on sheet 1

[GIP]

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:

[VBA]
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

[/VBA]

Edited 20-Aug-10 by geekgirlau. Reason: insert vba tags

[Bob Phillips]

Can you post the workbook for us to play with GIP?

[GIP]

I have attached the worksheet!!!

Many thanks

[geekgirlau]

[vba]Private Sub ComboBox1_Change()
With Sheets("Data")
.Unprotect ("password")

On Error Resume Next
.ShowAllData
On Error GoTo 0 ' should have some error handling

.Range("F6").AutoFilter Field:=2, Criteria1:=Worksheets("home").Range("Z1").Formula

.Protect Password:="password", DrawingObjects:=True, UserInterfaceOnly:=True, _
AllowFormattingCells:=True, AllowFormattingColumns:=True, _
AllowFormattingRows:=True
End With
End Sub
[/vba]

You'll notice that at no stage is the sheet "Data" selected - you don't have to move to a cell or sheet in order to change it.

This would also be improved if you named some objects:

• In the VBE window, you can give the sheet a name that you can use to refer to it in code. Worksheets("Data") could then become something like shData and Worksheets("home") could be shHome.
• Use some named ranges. Worksheets("home").Cells(1, 26) could become something like shHome.Range("Stage"). This has the added bonus of not having to change your code if you move cells around on your sheets.

[GIP]

Thanks I'll take a look at this. Appreciate your time!!!