Visible tab threw drop down selection

  • Thread starter Thread starter Chad
  • Start date Start date
C

Chad

Hello, I want to have a main tab that I would select an employees name from a
drop down list "The employee name would be the name of a tab" Then it would
make the tab visible then I would have a button to close or make it visible =
false again and only show the main screen where I need to select an employee.
How would I acomplish this?

Thanks!
 
One way to accomplish this is in three steps:

1. Create a validation list of employees using Data->Valication...
Under "Allow" select "List" and select the range of your employee names
2. Paste the following macro into VBA
This macro will hide the worksheet with the same name as what you have
selected in your validation drop-down list if it is visible and will unhide
it if it is not visible.
If your validation set is in "A1":

Sub HideUnhide()

Dim employee as Range

Set employee = Range("A1")

If Sheets(employee.value).Visible = True Then
Sheets(employee.Value).Visible = False
Else: Sheets(employee.Value).Visible = True
End If

End Sub

3. Assign this macro to a button or shortcut for easy use.

The macro is untested, but should work. Good luck!
 
I'm pretty new to event programming, but if you paste the following in the
worksheet portion of the VBA editor (right click on the worksheet you want
the code an click "View Code") you don't have to run it per say each time you
select a new employee. It will do this automatically. Enjoy!

Private Sub Worksheet_Change(ByVal Target As Range)

Set Target = Range("B8")

If Sheets(Target.Value).Visible = True Then
Sheets(Target.Value).Visible = False
Else: Sheets(Target.Value).Visible = True
End If

End Sub
 
Back
Top