Excel-Active X Combobox, Selecting Sheets RRS feed

  • Question

  • Hi, 
    Am a newbie in trying to automate Excel. 
    I have a workbook with 50 worksheets and a menu sheet. 
    On the menu sheet I created an Actixe X combobox filling it 
    with the fifty sheets. 
    I wish to be able to select from the combo box to go to a 
    particular sheet. I am using the following code which works fine 
    if I select and click but when I enter an alphabet it goes direct 
    to the first sheet with which the alphabet starts rather then giving 
    me any options to select. 
    Private Sub ComboBox1_Click()
    If Me.ComboBox1.Value <> "" Then Sheets(Me.ComboBox1.Value).Activate
    ComboBox1.Value = ""
    End Sub

    Please note I have posted this on other blogs. 

    Saturday, January 5, 2013 2:12 PM

All replies

  • I'd use a validation dropdown in a cell instead of an ActiveX combo box, and use the Worksheet_Change event to activate the selected sheet.

    Or create hyperlinks to the sheets.

    Regards, Hans Vogelaar

    Saturday, January 5, 2013 2:41 PM