Issue with worksheet_change

[mattmickle]

Hi all,
Thanks in advance.
I have a macro that I wrote that works...sorta.

Sub worksheet_change(ByVal target As Range)

Set target = Range("H2")


If target.Value = "1" Then
    ActiveSheet.Range("A30:A238").EntireRow.Hidden = False
    Range("A56:A238").EntireRow.Hidden = True
End If


If target.Value = "2" Then
    ActiveSheet.Range("A30:A238").EntireRow.Hidden = False
    Range("A30:A55").EntireRow.Hidden = True
    Range("A82:A238").EntireRow.Hidden = True
End If


If target.Value = "4" Then
    ActiveSheet.Range("A30:A238").EntireRow.Hidden = False
    Range("A30:A81").EntireRow.Hidden = True
    Range("A108:A238").EntireRow.Hidden = True
End If


If target.Value = "9" Then
    ActiveSheet.Range("A30:A238").EntireRow.Hidden = False
    Range("A30:A107").EntireRow.Hidden = True
    Range("A134:A238").EntireRow.Hidden = True
End If


If target.Value = "10" Then
    ActiveSheet.Range("A30:A238").EntireRow.Hidden = False
    Range("A30:A133").EntireRow.Hidden = True
    Range("A160:A238").EntireRow.Hidden = True
End If


If target.Value = "13" Then
    ActiveSheet.Range("A30:A238").EntireRow.Hidden = False
    Range("A30:A159").EntireRow.Hidden = True
    Range("A187:A238").EntireRow.Hidden = True
End If


If target.Value = "36" Then
    ActiveSheet.Range("A30:A238").EntireRow.Hidden = False
    Range("A30:A186").EntireRow.Hidden = True
    Range("A213:A238").EntireRow.Hidden = True
End If

So, based on the value of the Target cell H2, it hides and unhides certain portions of the worksheet. It does that. But, anytime I make any change any where on the sheet, the macro kicks off again, giving the same results. It's an annoying little thing, but I wanted to see if there was a way around it.

H2 is based on a drop-down input cell in E2. Select Corp 0036 and it populates H2 with the value 36. I think what's happening is that the sheet is re-calculating anytime I make a change and thus kicking off the macro.

thanks for any help you can provide...

 

[mattmickle]

just did. same result. nothing happened.

 

[Fluff]

If you are just looking for the last character in E2 try

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address(0, 0) <> "E2" Then Exit Sub
Select Case Right(Target.Offset(, 3).Value, 1)
   Case "1"
      Range("A30:A238").EntireRow.Hidden = False
      Range("A56:A238").EntireRow.Hidden = True
   Case "2"
      Range("A30:A238").EntireRow.Hidden = False
      Range("A30:A55").EntireRow.Hidden = True
      Range("A82:A238").EntireRow.Hidden = True
   Case "4"
      Range("A30:A238").EntireRow.Hidden = False
      Range("A30:A81").EntireRow.Hidden = True
      Range("A108:A238").EntireRow.Hidden = True
   Case "9"
      Range("A30:A238").EntireRow.Hidden = False
      Range("A30:A107").EntireRow.Hidden = True
      Range("A134:A238").EntireRow.Hidden = True
   Case "10"
      Range("A30:A238").EntireRow.Hidden = False
      Range("A30:A133").EntireRow.Hidden = True
      Range("A160:A238").EntireRow.Hidden = True
   Case "13"
      Range("A30:A238").EntireRow.Hidden = False
      Range("A30:A159").EntireRow.Hidden = True
      Range("A187:A238").EntireRow.Hidden = True
   Case "36"
      Range("A30:A238").EntireRow.Hidden = False
      Range("A30:A186").EntireRow.Hidden = True
      Range("A213:A238").EntireRow.Hidden = True
End Select
End Sub

 

[mattmickle]

tried that too. nothing hides at all. the worksheet_change event does not recognize that the drop down changed, which is why i slaved it to the value next to it. i might just have to live with it "flashing" the macro while i'm working it.

 

[Fluff]

Forgot to change this

Select Case Right(Target.Value, 1)

However if the event is not triggering either events have been disabled, or the dropdown is not DV.
Try this and then see if it works

Sub Myreset()
   Application.EnableEvents = True
End Sub