Hide a sheet based on a cell condition
 

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.

Hide a sheet based on a cell condition
 

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

"noord453" skrev i meddelelsen
...
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.

Hide a sheet based on a cell condition
 

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" wrote:

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

"noord453" skrev i meddelelsen
...
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.

Hide a sheet based on a cell condition
 

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

---
Per


"noord453" skrev i meddelelsen
...
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" wrote:

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

"noord453" skrev i meddelelsen
...
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.

Hide a sheet based on a cell condition
 

Per,

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

Gerard

"Per Jessen" wrote:

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

---
Per


"noord453" skrev i meddelelsen
...
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" wrote:

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

"noord453" skrev i meddelelsen
...
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.