Run Macros based on cell data entry

[OsmoseTom]

The workbook I created contains 40 tabs. There is one for each department
and an instruction tab. I have setup a macro to hide all the tabs except the
Instruction upon opening the workbook. My company has 14 different
department managers. Each one is responsible only for their assigned
departments. I have created 14 macros to make their assigned tabs visible.
I would like to run the macros based on entry into a cell. My idea is to
have 14 different passwords. Each manager would have to key in their
password then the appropriate macro would run to show the corresponding tabs.


This is the code I have so far.

Private Sub Worksheet_Change(ByVal Target As Range)
Set c2 = Range("C2")
Set t = Target
If Intersect(t, c2) Is Nothing Then Exit Sub
v = c2.Value

If v = "FN103179" Then
Call Finance

If v = "MP101982" Then
Call ComputerSupport
End If

If c = "BB164977" Then
Call Payroll
End If

End If
End Sub

The Finance macro runs after entering FN103179 in cell 2. However the other
macros do not run after entering their passwords.

Please tell me what I am doing wrong.

Thanks.

 

[Barb Reinhardt]

Why have 14 different macros? You could do something like this
Sub Test()
Dim WS As Worksheet
Dim myEntry As String

myEntry = "Cell Entry"

For Each WS In ThisWorkbook.Worksheets
Debug.Print WS.Name, WS.Visible
If Not WS.Name = "Instruction" And _
Not WS.Name = myEntry Then
'This would change based upon what was entered int he cell
If Not WS.Visible = xlSheetHidden Then
WS.Visible = xlSheetHidden
End If
Else
If Not WS.Visible = xlSheetVisible Then
WS.Visible = xlSheetVisible
End If
End If
Next WS

End Sub

 

[dustinbrearton via OfficeKB.com]

try changing the other if statements to elseif statements.

If v = "FN103179" Then
Call Finance

ElseIf v = "MP101982" Then
Call ComputerSupport
ElseIf c = "BB164977" Then
Call Payroll
End If

 

[Barb Reinhardt]

I also noticed that you had c = for Payroll and v= for the others. That
could be another problem.

 

[Barb Reinhardt]

I misread part of the first message

If v = "FN103179" Then
Call Finance
ElseIf v = "MP101982" Then
Call ComputerSupport
ElseIf c = "BB164977" Then
Call Payroll
Else
MsgBox("You entered an incorrect password")
End If

 

[OsmoseTom]

Barb,

Thanks for your quick response. The specs I was given was to use a password
structure. The idea behind the macros was to have all the worksheets visible
for a department manager. Some managers are responsible for 6 departments.

Any idea how to fix my original code?

 

[OsmoseTom]

Thank you Barb & Dustin. The ElseIf did the trick.