Hide a sheet based on a cell condition

[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.

 

[Per Jessen]

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

 

[noord453]

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?

 

[Per Jessen]

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

 

[noord453]

Per,

Thank you so much, but I am still getting the same error message.

Gerard

 

[Per Jessen]

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

 

[noord453]

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

 

[Per Jessen]

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