Run Macros based on cell data entry
 

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.

Run Macros based on cell data entry
 

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


--
HTH,
Barb Reinhardt



"OsmoseTom" wrote:

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.

Run Macros based on cell data entry
 

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



OsmoseTom wrote:
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.

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200808/1

Run Macros based on cell data entry
 

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



"OsmoseTom" wrote:

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.

Run Macros based on cell data entry
 

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

--
HTH,
Barb Reinhardt



"OsmoseTom" wrote:

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.

Run Macros based on cell data entry
 

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?

"Barb Reinhardt" wrote:

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


--
HTH,
Barb Reinhardt



"OsmoseTom" wrote:

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.