VBA to show/hide worksheet based on a cell in another worksheet

[Sunny London]

Can you help me - what is the VBA to show/hide one worksheet based on a cell in another worksheet?

e.g. if a certain cell is populated, can that trigger a macro to automatically show another worksheet or, if the cell is not populated can the macro run automatically to hide the other worksheet? Thanks.

 

[VoG]

Try this: right click the sheet tab, select View Code and paste in

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address(False, False) = "A1" Then Sheets("Sheet4").Visible = Target.Value <> ""
End Sub

Change the cell reference and sheet name to suit.

 

[Sunny London]

Thanks, I need it to be spelt out a bit more though, I'm new to VBA.

I've made my query more specific, in case that helps.

In my 'Data Input' worksheet I've named cell B41 'ForEx1' which contains a drop down menu, if the selction is blank I want worksheets "Foreign Currency 1" and "Foreign Currency 2" to be hidden. But if the user selects one of the options from the drop down list, e.g. 'Euro', 'Yen' or 'GBP', I want worksheets "Foreign Currency 1" and "Foreign Currency 2" to be visible.

Can you advise on what I put where in the VBA code you suggested.

Many thanks.

 

[VoG]

Right click the Data Input tab, select View Code and paste in

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address(False, False) = "B41" Then
    Sheets("Foreign Currency 1").Visible = Target.Value <> ""
    Sheets("Foreign Currency 2").Visible = Target.Value <> ""
End If
End Sub

 

[Sunny London]

That worked a treat, but I have one other related query.

I have another cell, B44 and when B44 is blank I want 2 different worksheets "Foreign Sales" and "Foreign Payments" hidden.

I tried replicating the VBA you gave me, but it either needs a new Sub name or I need another loop included in the first macro. I've tried different things but none is working, could you help again please?

 

[VoG]

Try

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address(False, False) = "B41" Then
    Sheets("Foreign Currency 1").Visible = Target.Value <> ""
    Sheets("Foreign Currency 2").Visible = Target.Value <> ""
ElseIf Target.Address(False, False) = "B44" Then
    Sheets("Foreign Sales").Visible = Target.Value <> ""
    Sheets("Foreign Payments").Visible = Target.Value <> ""
End If
End Sub

 

[Sunny London]

Brilliant, that worked, thank you very much.