|
Hide Sheet when cell equals
Hello....I hope you can help....thanks in advance!
I have the following code. I want to add a feature to it so that when M12 equals "AR2" Sheet1 and Sheet 2 hide and Sheet 3 and Sheet4 unhide. And if M12 does not equal "AR2 then Sheet1 and Sheet2 unhide and Sheet3 and Sheet4 hide. Is this possible? Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim myRange As Range If Target.Count 1 Then Exit Sub Set myRange = Me.Range("M12") If Intersect(Target, myRange) Is Nothing Then Exit Sub If myRange.Value = "AR2" Then On Error Resume Next Me.Rows("60:87").EntireRow.Hidden = False Me.Rows("88:109").EntireRow.Hidden = True On Error GoTo 0 Else On Error Resume Next Me.Rows("88:109").EntireRow.Hidden = False Me.Rows("60:87").EntireRow.Hidden = True On Error GoTo 0 End If End Sub |
Hide Sheet when cell equals
On Nov 9, 9:13*am, jpzachar
wrote: Hello....I hope you can help....thanks in advance! I have the following code. *I want to add a feature to it so that when M12 equals "AR2" Sheet1 and Sheet 2 hide and Sheet 3 and Sheet4 unhide. * And if M12 does not equal "AR2 then Sheet1 and Sheet2 unhide and Sheet3 and Sheet4 hide. *Is this possible? Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim myRange As Range If Target.Count 1 Then Exit Sub Set myRange = Me.Range("M12") If Intersect(Target, myRange) Is Nothing Then Exit Sub If myRange.Value = "AR2" Then * * On Error Resume Next * * Me.Rows("60:87").EntireRow.Hidden = False * * Me.Rows("88:109").EntireRow.Hidden = True * * On Error GoTo 0 Else * * On Error Resume Next * * Me.Rows("88:109").EntireRow.Hidden = False * * Me.Rows("60:87").EntireRow.Hidden = True * * On Error GoTo 0 End If End Sub What sheet are AR2 and M12? |
Hide Sheet when cell equals
Hi,
Maybe this Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count 1 Then Exit Sub If Target.Address = "$M$12" Then If Target.Value = "AR2" Then On Error Resume Next Rows("60:87").EntireRow.Hidden = False Rows("88:109").EntireRow.Hidden = True Sheets("Sheet1").Visible = False Sheets("Sheet2").Visible = False Sheets("Sheet3").Visible = True Sheets("Sheet4").Visible = True On Error GoTo 0 Else On Error Resume Next Rows("88:109").EntireRow.Hidden = False Rows("60:87").EntireRow.Hidden = True Sheets("Sheet1").Visible = True Sheets("Sheet2").Visible = True Sheets("Sheet3").Visible = False Sheets("Sheet4").Visible = False On Error GoTo 0 End If End If End Sub Mike "jpzachar" wrote: Hello....I hope you can help....thanks in advance! I have the following code. I want to add a feature to it so that when M12 equals "AR2" Sheet1 and Sheet 2 hide and Sheet 3 and Sheet4 unhide. And if M12 does not equal "AR2 then Sheet1 and Sheet2 unhide and Sheet3 and Sheet4 hide. Is this possible? Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim myRange As Range If Target.Count 1 Then Exit Sub Set myRange = Me.Range("M12") If Intersect(Target, myRange) Is Nothing Then Exit Sub If myRange.Value = "AR2" Then On Error Resume Next Me.Rows("60:87").EntireRow.Hidden = False Me.Rows("88:109").EntireRow.Hidden = True On Error GoTo 0 Else On Error Resume Next Me.Rows("88:109").EntireRow.Hidden = False Me.Rows("60:87").EntireRow.Hidden = True On Error GoTo 0 End If End Sub |
Hide Sheet when cell equals
Hi,
"AR2" is a string and the OP is testing for it in Range M12 of the sheet with the code in. Mike "dbKemp" wrote: On Nov 9, 9:13 am, jpzachar wrote: Hello....I hope you can help....thanks in advance! I have the following code. I want to add a feature to it so that when M12 equals "AR2" Sheet1 and Sheet 2 hide and Sheet 3 and Sheet4 unhide. And if M12 does not equal "AR2 then Sheet1 and Sheet2 unhide and Sheet3 and Sheet4 hide. Is this possible? Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim myRange As Range If Target.Count 1 Then Exit Sub Set myRange = Me.Range("M12") If Intersect(Target, myRange) Is Nothing Then Exit Sub If myRange.Value = "AR2" Then On Error Resume Next Me.Rows("60:87").EntireRow.Hidden = False Me.Rows("88:109").EntireRow.Hidden = True On Error GoTo 0 Else On Error Resume Next Me.Rows("88:109").EntireRow.Hidden = False Me.Rows("60:87").EntireRow.Hidden = True On Error GoTo 0 End If End Sub What sheet are AR2 and M12? |
Hide Sheet when cell equals
Perfecto!!!! thanks
"Mike H" wrote: Hi, Maybe this Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count 1 Then Exit Sub If Target.Address = "$M$12" Then If Target.Value = "AR2" Then On Error Resume Next Rows("60:87").EntireRow.Hidden = False Rows("88:109").EntireRow.Hidden = True Sheets("Sheet1").Visible = False Sheets("Sheet2").Visible = False Sheets("Sheet3").Visible = True Sheets("Sheet4").Visible = True On Error GoTo 0 Else On Error Resume Next Rows("88:109").EntireRow.Hidden = False Rows("60:87").EntireRow.Hidden = True Sheets("Sheet1").Visible = True Sheets("Sheet2").Visible = True Sheets("Sheet3").Visible = False Sheets("Sheet4").Visible = False On Error GoTo 0 End If End If End Sub Mike "jpzachar" wrote: Hello....I hope you can help....thanks in advance! I have the following code. I want to add a feature to it so that when M12 equals "AR2" Sheet1 and Sheet 2 hide and Sheet 3 and Sheet4 unhide. And if M12 does not equal "AR2 then Sheet1 and Sheet2 unhide and Sheet3 and Sheet4 hide. Is this possible? Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim myRange As Range If Target.Count 1 Then Exit Sub Set myRange = Me.Range("M12") If Intersect(Target, myRange) Is Nothing Then Exit Sub If myRange.Value = "AR2" Then On Error Resume Next Me.Rows("60:87").EntireRow.Hidden = False Me.Rows("88:109").EntireRow.Hidden = True On Error GoTo 0 Else On Error Resume Next Me.Rows("88:109").EntireRow.Hidden = False Me.Rows("60:87").EntireRow.Hidden = True On Error GoTo 0 End If End Sub |
Hide Sheet when cell equals
Glad I could help
"jpzachar" wrote: Perfecto!!!! thanks "Mike H" wrote: Hi, Maybe this Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count 1 Then Exit Sub If Target.Address = "$M$12" Then If Target.Value = "AR2" Then On Error Resume Next Rows("60:87").EntireRow.Hidden = False Rows("88:109").EntireRow.Hidden = True Sheets("Sheet1").Visible = False Sheets("Sheet2").Visible = False Sheets("Sheet3").Visible = True Sheets("Sheet4").Visible = True On Error GoTo 0 Else On Error Resume Next Rows("88:109").EntireRow.Hidden = False Rows("60:87").EntireRow.Hidden = True Sheets("Sheet1").Visible = True Sheets("Sheet2").Visible = True Sheets("Sheet3").Visible = False Sheets("Sheet4").Visible = False On Error GoTo 0 End If End If End Sub Mike "jpzachar" wrote: Hello....I hope you can help....thanks in advance! I have the following code. I want to add a feature to it so that when M12 equals "AR2" Sheet1 and Sheet 2 hide and Sheet 3 and Sheet4 unhide. And if M12 does not equal "AR2 then Sheet1 and Sheet2 unhide and Sheet3 and Sheet4 hide. Is this possible? Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim myRange As Range If Target.Count 1 Then Exit Sub Set myRange = Me.Range("M12") If Intersect(Target, myRange) Is Nothing Then Exit Sub If myRange.Value = "AR2" Then On Error Resume Next Me.Rows("60:87").EntireRow.Hidden = False Me.Rows("88:109").EntireRow.Hidden = True On Error GoTo 0 Else On Error Resume Next Me.Rows("88:109").EntireRow.Hidden = False Me.Rows("60:87").EntireRow.Hidden = True On Error GoTo 0 End If End Sub |
| All times are GMT +1. The time now is 11:38 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com