I am working on customizing a workbook to allow users to 'view' the results of their data entry on a series of summary worksheets in the same workbook. I accomplished most of the customization by simply creating buttons and assigned macros to the them to direct the user to the summary sheets. This is working just fine, however, I would like to create a Dropdown list, to include different branches of the organization, and allow users to just view the summaries for that particular branch.

Could someone help me out? I want to be able to create the Dropdown list, allow the user to select an item from the list, use that as input to go to the sheet where the item (branch) summary is stored.

I tried creating a Form ComboBox and referencing a range that contains the branches, but I do not know how to use the selected item from the Combobox to direct the user to the view sheet.

Help!!!




Enrique D. Navas
SPARCC
Technical Services Consultant
Stark County Educational Service Center
2100 38th Street NW
Canton, OH 44709
Tel. (330) 492-8136 x-373

Enrique D. Navas
SPARCC
Technical Services Consultant
Stark County Educational Service Center
2100 38th Street NW
Canton, OH 44709
Tel. (330) 492-8136 x-373

Hi,

I think it's better way to use Dropdown list from Control Toolbox.

After you have placed Dropdown list to your worksheet, write the code below

Private Sub ComboBox1_GotFocus()
    Dim Page As Worksheet
    ComboBox1.Clear
    For Each Page In Sheets
        ComboBox1.AddItem Page.Name
    Next
End Sub

Private Sub ComboBox1_Change()
    On Error Resume Next
    Sheets(ComboBox1.Text).Activate
End Sub

I hope this will help you
-vemaju