Hide Sheet when cell equals

J

jpzachar

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
 
D

dbKemp

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
Click to expand...

What sheet are AR2 and M12?
 
M

Mike H

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
 
M

Mike H

Hi,

"AR2" is a string and the OP is testing for it in Range M12 of the sheet
with the code in.

Mike
 
J

jpzachar

Perfecto!!!! thanks

Mike H said:
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
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

when certain rows are hidden, can I also hide a combobox? 6
VB - With protected sheet 3
Code error to mirror data from one table to another 6
Hiding / unhiding rows based on change of a cell 8
Hide/Show Rows based on Cell Value with Data Validation 3
run macro when drop down box equals 2
macro 2
Hide Rows with Formulas 2

Top