Activating Hidden Worksheets From Combo Box Selections

[davidhall]

I have a combo box that has several names in it for the user to select. Based on what they select, for instance, the name Jennifer, then a hidden worksheet should appear. The title of the worksheet that should appear is the employees name. Any suggestions?

 

[JoeMo]

Assuming your combo box is named ComboBox1 (change to suit) and is an Active-X control:

Private Sub ComboBox1_Change()
Dim shtNam As String
shtNam = ComboBox1.Text
ThisWorkbook.Sheets(shtNam).Visible = True
End Sub

 

[davidhall]

Thanks. Couple quick questions. Will that still work if its not an active x control? Also, if the selected name from the combo box is the name and the sheet name is Sheet1 for person A and Sheet2 for person B, how can I make that work so that I don't have to change the multiple worksheets to correspond to the persons name? I can setup two columns side by side with row A listing the persons name and row B stating the corresponding worksheet.

 

[JoeMo]

If you use a forms control rather than an Active-X control, then you can't use the change event to trigger making the sheet visible. If you don't want to name the sheets after the employees you can create a look-up table as you suggested and use something like this in the code:

Dim lkuprng as range
set lkuprng = Range("your look-up range between these quotes")
' previous code
shtNam =worksheetfunction.VLookup(ComboBox1.Text,lkuprng,2,0)
'previous code