|
Trigger Macro based on single Cell change
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 |
Trigger Macro based on single Cell change
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 On 27 Nov., 18:16, gtslabs wrote: 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 |
Trigger Macro based on single Cell change
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 -- Gary''s Student - gsnu200816 |
Trigger Macro based on single Cell change
On Nov 27, 12:38*pm, Per Jessen wrote:
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 On 27 Nov., 18:16, gtslabs wrote: 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- Hide quoted text - - 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. |
Trigger Macro based on single Cell change
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 On 27 Nov., 19:31, gtslabs wrote: On Nov 27, 12:38*pm, Per Jessen wrote: 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 On 27 Nov., 18:16, gtslabs wrote: 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- Hide quoted text - - 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.- Skjul tekst i anførselstegn - - Vis tekst i anførselstegn - |
Trigger Macro based on single Cell change
Per Jessen wrote:
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 On 27 Nov., 18:16, gtslabs wrote: 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 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 |
| All times are GMT +1. The time now is 12:08 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com