Show and Hide sheets Combo box action (2007)

Topic

New Reply

Hi loungers,

I have wrote a routine that is using a active x control combo box which has the following code
Option Explicit

Private Sub cmbSheetSwitch_Change()
Dim vFieldSheets
Dim vOfficeSheets

Application.ScreenUpdating = False

vFieldSheets = Array(“FIELD REPORT”)

vOfficeSheets = Array(“COVER”, “PROCEDURE”, “DESIGN”, “CALCS”, “PRICING”, “TC”)

If cmbSheetSwitch.Value = “” Then Exit Sub

On Error GoTo ErrHandler
If Me.cmbSheetSwitch.Value = “FIELD REPORT” Then
Sheets(vOfficeSheets).Visible = False
Sheets(vFieldSheets).Visible = True

ElseIf Me.cmbSheetSwitch.Value = “PROPOSAL” Then
Sheets(vOfficeSheets).Visible = True
Sheets(vFieldSheets).Visible = False
End If

Exit Sub
ErrHandler:
MsgBox “ERROR”
End Sub

the idea is to pick a activity from the drop down Proposal or Field Reort in this case.

the code is activated on combobox change and show or hide the sheets that are in my varibles vfieldsheets and vofficeseets.

how ever the code seems to always bounce to my error hndler

any suggestions?

Reply | Quote

Replies

Temporarily comment out the line

On Error GoTo ErrHandler

by inserting an apostrophe ‘ at the beginning. When the error occurs, click Debug. Please report exactly what it says, and which line is highlighted.

Reply | Quote

Run-time error ‘1004’
Application-defined or object-defined error

Sheets(vOfficeSheets).Visible = True is highlighted yellow

Reply | Quote

Apparently Excel doesn’t like to make multiple sheets visible at once. You can replace the offending line with

Dim s
For Each s In vOfficeSheets
Sheets(s).Visible = True
Next s

Reply | Quote

well thank you Hans

Reply | Quote