Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Hide cell values based on a condition in another cell | Excel Worksheet Functions | |||
Hide Columns Based on Condition | Excel Programming | |||
Hide Columns Based on Condition | Excel Programming | |||
Hide rows based on a condition | Excel Programming | |||
Hide a row based on one cell's condition | Excel Worksheet Functions |