| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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? |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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? |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| cell on sheet 1 equals cell on sheet two | Excel Discussion (Misc queries) | |||
| In Excel, how do I make a cell equals the sheet name? | Excel Worksheet Functions | |||
| If a cell equals _, at the next row that equals _, return value fr | Excel Worksheet Functions | |||
| If cell is left blank, or equals zero, then cell equals a different cell | Excel Discussion (Misc queries) | |||
| How do you hide a row if a certain cell equals zero? | Excel Programming | |||
Linear Mode
