Referencing a cell from another worksheet in VBA

[a-man]

Hi,

I have a drop down list in sheet 'Reinforcement' located in cell G4. When this cell selection changes, VBA code hides/unhides certain rows.

In sheet 'summary', i would like to reference the drop down list in 'Reinforcement' in cell G4 to hide/unhide rows. Below is the successful code used when hiding/unhiding rows in 'Reinforcement':

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Me.Range("G4")) Is Nothing Then Exit Sub

Application.EnableEvents = False 'to prevent endless loop
Sheets("1. Reinforcement Calculation").Unprotect Password:="xxxxxxx"
Select Case [g4].Value
  Case "Standard Calculation"
    Rows("128:138").EntireRow.Hidden = True
    Rows("101:127").EntireRow.Hidden = False
  Case "Select"
    Rows("101:127").EntireRow.Hidden = False
  Case "Fancy Tap"
    Rows("104:127").EntireRow.Hidden = True
    Rows("128:138").EntireRow.Hidden = False
End Select
Sheets("1. Reinforcement Calculation").Protect Password:="xxxxxxx"

Application.EnableEvents = True
End Sub

How do i change this code so that it refers to G4 in the 'Reinforcement' sheet when the code is running in the 'summary' sheet?

[HaHoBe]

Hi, a-man,

should only be Sheet Reinforcement be monitored? If so you could add thee same lines to hide/unhoide but add a qualifiers for sheet Summary there like

'....
  Case "Standard Calculation"
    Rows("128:138").EntireRow.Hidden = True
    Sheets("Summary").Rows("128:138").EntireRow.Hidden = True
    Rows("101:127").EntireRow.Hidden = False
    Sheets("Summary").Rows("101:127").EntireRow.Hidden = False

If the code should run on either shee I would set a Variable for teh worksheet on which it shuould be hgandled like

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Me.Range("G4")) Is Nothing Then Exit Sub
Dim ws As Worksheet
Application.EnableEvents = False 'to prevent endless loop
If Target.Parent.Name = "1. Reinforcement Calculation" Then
  Set ws = Sheets("Summary")
Else
  Set ws = Sheets("1. Reinforcement Calculation")
End If
ActiveSheet.Unprotect Password:="xxxxxxx"
ws.Unprotect Password:="xxxxxxx"
Select Case [g4].Value
  Case "Standard Calculation"
    Rows("128:138").EntireRow.Hidden = True
    Rows("101:127").EntireRow.Hidden = False
    ws.Rows("128:138").EntireRow.Hidden = True
    ws.Rows("101:127").EntireRow.Hidden = False
  Case "Select"
    Rows("101:127").EntireRow.Hidden = False
    ws.Rows("101:127").EntireRow.Hidden = False
  Case "Fancy Tap"
    Rows("104:127").EntireRow.Hidden = True
    Rows("128:138").EntireRow.Hidden = False
    ws.Rows("104:127").EntireRow.Hidden = True
    ws.Rows("128:138").EntireRow.Hidden = False
End Select
ActiveSheet.Protect Password:="xxxxxxx"
ws.Protect Password:="xxxxxxx"
Set ws = Nothing
Application.EnableEvents = True
End Sub

Assuming that both sheets are protected with the same password (otherwise use a variable/array to handle the indivdual passwords).

Ciao,
Holger

[a-man]

Holger - Once again, thank you!