Trigger Macro based on single Cell change

[gtslabs]

Trigger Macro based on single Cell change

I have a range named "Method". it is only 1 cell and contains the Data
Validation Letters A,B,C,D and is used on the form as a dropdown list.

I want to trigger the following code based on the value (A,B,C or D).
Right now it uns every time I change any cell value in the worksheet.
Can this be done?

Private Sub Worksheet_Change(ByVal Target As Range)

If Me.Range("Method").Value = "D" Then
Rows("14:19").Select
Selection.EntireRow.Hidden = False
Rows("51:53").Select
Selection.EntireRow.Hidden = False

Else

Rows("51:53").Select
Selection.EntireRow.Hidden = True
Rows("14:19").Select
Selection.EntireRow.Hidden = True
End If
End Sub

 

[Per Jessen]

This should do it:

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = Range("Method").Address Then
If Me.Range("Method").Value = "D" Then
Rows("14:19").EntireRow.Hidden = False
Rows("51:53").EntireRow.Hidden = False
Else
Rows("51:53").EntireRow.Hidden = True
Rows("14:19").EntireRow.Hidden = True
End If
End If
End Sub

Regards,
Per

 

[Gary''s Student]

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("Method")) Is Nothing Then Exit Sub
If Me.Range("Method").Value = "D" Then
Rows("14:19").Select
Selection.EntireRow.Hidden = False
Rows("51:53").Select
Selection.EntireRow.Hidden = False

Else

Rows("51:53").Select
Selection.EntireRow.Hidden = True
Rows("14:19").Select
Selection.EntireRow.Hidden = True
End If
End Sub

 

[gtslabs]

This should do it:

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = Range("Method").Address Then
    If Me.Range("Method").Value = "D" Then
        Rows("14:19").EntireRow.Hidden = False
        Rows("51:53").EntireRow.Hidden = False
    Else
        Rows("51:53").EntireRow.Hidden = True
        Rows("14:19").EntireRow.Hidden = True
    End If
End If
End Sub

Regards,
Per









- Show quoted text -

Using this approach I get an error when I try to use a CopyDown on ant
other cells in the worksheet.
I get a Type Mismatch error.

 

[Per Jessen]

Hi

My code shouldn't cause this error...

Post the entire code for further comments.

If you use a select statement in your code, I think you should add
"Application.EnableEvents=false" at the top of the macro, just
remember to set = True at the end.

Regards,
Per

 

[Dana DeLouis]

This should do it:

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = Range("Method").Address Then
If Me.Range("Method").Value = "D" Then
Rows("14:19").EntireRow.Hidden = False
Rows("51:53").EntireRow.Hidden = False
Else
Rows("51:53").EntireRow.Hidden = True
Rows("14:19").EntireRow.Hidden = True
End If
End If
End Sub

Regards,
Per

Hi. Just a slightly different version of the same code...

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
'// Hide rows if NOT "D"
Range("14:19, 51:53").EntireRow.Hidden = _
(Me.Range("Method").Value <> "D")
Application.EnableEvents = True
End Sub

= = =
Dana DeLouis