Macro to hide a sheet based on selection from drop-down menu

[SJMM11]

Hi there,

I am new to VBA and was hoping someone might be able to help me. I have a drop-down list in cell C4 on Sheet1 and when a user selects a specific item "-" I would like this to hide Sheet3. If the user selects any other item I would like Sheet3 to be visible.

Ideally this would be automatic and the user wouldn't be able to manually 'unhide'. The reason for this is that Sheet3 is a results sheet which is dependent on the value in C4 on Sheet1.

I have tried searching other threads and utilising suggestions for similar problems but alas my limited knowledge of VBA means I am not having any success.

If anyone could assist it would be greatly appreciated.

Many Thanks

Simon

 

[JLGWhiz]

Copy this code to your worksheet code module for the sheet with the dropdown box. To access the code module, right click the sheet name tab, then click 'View Code' in the pop up menu.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Adderess = "$C$4" Then
Sheets.Visible = True
 If Range("C4").Value = "-" Then
  Sheets("Sheet3").Visible = False
 End if
End If
End Sub

 

[SJMM11]

Thank you very much JLGWhiz. I did as suggested (and noticed there might have been typo in spelling Address) but am still getting a:

Run-time error '1004':
Method Visible of object Sheets failed.

Any ideas what I might be doing wrong?

Many Thanks

Simon

Copy this code to your worksheet code module for the sheet with the dropdown box. To access the code module, right click the sheet name tab, then click 'View Code' in the pop up menu.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Adderess = "$C$4" Then
Sheets.Visible = True
 If Range("C4").Value = "-" Then
  Sheets("Sheet3").Visible = False
 End if
End If
End Sub

 

[JLGWhiz]

Thank you very much JLGWhiz. I did as suggested (and noticed there might have been typo in spelling Address) but am still getting a:

Run-time error '1004':
Method Visible of object Sheets failed.

Any ideas what I might be doing wrong?

Many Thanks

Simon

Try it like this

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$C$4" Then
Sheets("Sheet3").Visible = True
    If Range("C4").Value = "-" Then
       Sheets("Sheet3").Visible = False
    End If
End If
End Sub