Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Oct 2005
    Location
    Calgary, Alberta
    Posts
    205
    Thanks
    0
    Thanked 1 Time in 1 Post

    Show and Hide sheets Combo box action (2007)

    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?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Show and Hide sheets Combo box action (2007)

    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.

  3. #3
    2 Star Lounger
    Join Date
    Oct 2005
    Location
    Calgary, Alberta
    Posts
    205
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Show and Hide sheets Combo box action (2007)

    Run-time error '1004'
    Application-defined or object-defined error

    Sheets(vOfficeSheets).Visible = True is highlighted yellow

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Show and Hide sheets Combo box action (2007)

    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

  5. #5
    2 Star Lounger
    Join Date
    Oct 2005
    Location
    Calgary, Alberta
    Posts
    205
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Show and Hide sheets Combo box action (2007)

    well thank you Hans

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •