Macro that operates from Combo Box result

Posted by Tony H on August 02, 2001 3:13 PM

Hi All,

As you Guru's know Combo Boxes generate a number based on list selection, I would like to create a macro using this number (Drp Down Selection) to jump to a worksheet in the same workbook. Can anyone help ? Would be most appreciated if someone could get me started! This site rocks!!

Cheers,

Tony

Posted by Ivan F Moala on August 02, 2001 7:26 PM

One way to do this is;

Private Sub CombSheets_Change()
ThisWorkbook.Sheets(CombSheets.Text).Activate
End Sub

Private Sub UserForm_Initialize()
Dim Sh As Worksheet

CombSheets.Clear
For Each Sh In ThisWorkbook.Sheets
CombSheets.AddItem Sh.Name
Next
End Sub

On userform iniatilize it loads in all sheets
names including hidden sheets.
Selecting the sheet via the dropdown box
activates the sheet unless it is hidden.
If you HAVE hidden sheets then just loop through
and don't add....or change code to show......

eg If Sh.visible = false then sh.visible = true

HTH

Ivan