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

how to have 2 drop down lists filter rows 2
when certain rows are hidden, can I also hide a combobox? 6
VB - With protected sheet 3
Working outlook via vba code with xlsx - error 0
Code error to mirror data from one table to another 6
run macro when drop down box equals 2
Totals rows shouldn't become invisible when filtering 3
Unhide/hide based on drop down list 0

Top