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...

 

[Rino702]

Replace the line

Set target = Range("H2")
by
If target.address = Range("H2").address then


and add at the end of your code
End If
​

 

[pgc01]

Hi

If target.Value = "1" Then

Does this mean that H2 values are text values?

You are comparing with the text value "1", not the number value 1.

Please clarify.

 

[mattmickle]

Hi

If target.Value = "1" Then

Does this mean that H2 values are text values?

You are comparing with the text value "1", not the number value 1.

Please clarify.

H2 is the result of a vlookup formula. I don't believe they are text, but either way, the macro seems to work, other than the re-calculation.

 

[mattmickle]

Replace the line

Set target = Range("H2")
by
If target.address = Range("H2").address then


and add at the end of your code
End If
​

I'll give that a try and let you know if it works.

 

[Fluff]

A worksheet change event will fire whenever you manually change any cell on that sheet.
However it will not detect changes made by formulae.
If the Vlookup is dependant on E2 try

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address(0, 0) <> "E2" Then Exit Sub

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


If Target.Offset(, 3).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.Offset(, 3).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.Offset(, 3).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.Offset(, 3).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.Offset(, 3).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.Offset(, 3).Value = "36" Then
    ActiveSheet.Range("A30:A238").EntireRow.Hidden = False
    Range("A30:A186").EntireRow.Hidden = True
    Range("A213:A238").EntireRow.Hidden = True
End If

End Sub

PS NEVER use vba keywords (such as Target) for variables, it only leads to trouble

 

[mattmickle]

neither of those suggestions worked. the macro didn't do any of the hiding or unhiding.
maybe a little more information:
Cell H2 is a value(right) formula dependent on the value of a dropdown in merged cells E2:G2
In E2:G2, you choose "CORP 0002" and it populates H2 with the value "2" which kicks off the macro.
This is a workaround since I haven't been able to find another way for VBA to determine a change using the dropdown input.

 

[Fluff]

Is the dropdown a data validation dropdown, or a forms/activex combobox?

 

[mattmickle]

data validation drop down.

 

[pgc01]

Hi

Did you try the code testing a number value instead of text?

If Target.Offset(, 3).Value = 1 Then