Hide a sheet based on a cell condition

  • Thread starter Thread starter noord453
  • Start date Start date
N

noord453

Hi I want to hide or unhide a sheet, based on the condition set in a cell on
a other sheet. The question could be show other sheet yes/no. and based on
the answer the sheet is shown, or hidden. The workbook, and the visible
sheets are protected only some cells are free to edit.
 
Hi

Look at this code. As it's a event code it has to go into the code sheet for
the desired sheet (the sheet where the condition to hide/unhide the other
sheet is).

Private Sub Worksheet_Change(ByVal Target As Range)
Dim AnswerCell As Range
Set AnswerCell = Range("A1")
If Target.Address = AnswerCell.Address Then
If AnswerCell = "Yes" Then
Sheets("Sheet2").Visible = False
ElseIf AnswerCell = "No" Then
Sheets("Sheet2").Visible = True
End If
End If
End Sub

Hopes this helps
 
Per,

Thanks, it works. However since the workbook and its sheets are protected
with a password, and there are others using this excel sheet, without them
knowing this password, it is when run giving the following error
"Run-time error '1004':

Unable to set the Visible property of the Worksheet class"

How to overcome this situation?
 
Hi

Let the macro unprotect an protect again as required.

You say that both workbook and sheets are protected. It is only required to
unprotect sheet2 in my example.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim AnswerCell As Range
pWord = "JustMe"
Set AnswerCell = Range("A1")
If Target.Address = AnswerCell.Address Then
'ActiveWorkbook.Unprotect Password:=pWord
If AnswerCell = "Yes" Then
With Sheets("Sheet2")
.Unprotect Password:=pWord
.Visible = False
.Protect Password:=pWord
End With
ElseIf AnswerCell = "No" Then
With Sheets("Sheet2")
.Unprotect Password:=pWord
.Visible = True
.Protect Password:=pWord
End With
End If
'ActiveWorkbook.Protect Password:=pWord
End If
End Sub

Hopes this helps
 
Hi Gerard

Try to step through the code using F8 and verify that the sheet is
unprotected, before the code is trying to before trying to hide/unhide the
sheet.

Is it same error on both Yes and No in A1?

Per
 
Per,

It is working now, thanks. One question is remaing how to hide the VB script
because it is showing the password now.

I will search the previous questions and answers but if you know the answer,
or if you can direct me in right direction?

Regards
Gerard
 
Gerard,

Thanks for your reply, I'm glad you made it work.

You have to protect the project.

To do that, open the VBA editor, right click on the sheet and select
VBAProject Properties.

Select the Protection Tab and check "Lock project for viewing" and apply
password.

Save and close the workbook to enable the protection.

Per
 
Back
Top