| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
run macro when drop down box equals
I have the following macro that I want to run when cell M9 equals either
"AR1" or "AR2". Is this possible? thanks! Sub Macro7() If Range("M9").Value = "AR1" Then Rows("60:89").Select Selection.EntireRow.Hidden = False Rows("90:112").Select Selection.EntireRow.Hidden = True Else Rows("90:112").Select Selection.EntireRow.Hidden = False Rows("60:89").Select Selection.EntireRow.Hidden = True End If End Sub |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
run macro when drop down box equals
You're close. Right click on the worksheet tab where the data is being
changed and select View Code. Paste this in 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("M9") If Intersect(Target, myRange) Is Nothing Then Exit Sub If myRange.Value = "AR1" Then On Error Resume Next Me.Rows("60:89").EntireRow.Hidden = False Me.Rows("90:112").EntireRow.Hidden = True On Error GoTo 0 Else On Error Resume Next Me.Rows("90:112").EntireRow.Hidden = False Me.Rows("60:89").EntireRow.Hidden = True On Error GoTo 0 End If End Sub I added the On error statements becuase IIRC, when the cells are already hidden and you try to hide them, you get an error. -- HTH, Barb Reinhardt If this post was helpful to you, please click YES below. "jpzachar" wrote: I have the following macro that I want to run when cell M9 equals either "AR1" or "AR2". Is this possible? thanks! Sub Macro7() If Range("M9").Value = "AR1" Then Rows("60:89").Select Selection.EntireRow.Hidden = False Rows("90:112").Select Selection.EntireRow.Hidden = True Else Rows("90:112").Select Selection.EntireRow.Hidden = False Rows("60:89").Select Selection.EntireRow.Hidden = True End If End Sub |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
run macro when drop down box equals
Thank you!!!! so much
"Barb Reinhardt" wrote: You're close. Right click on the worksheet tab where the data is being changed and select View Code. Paste this in 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("M9") If Intersect(Target, myRange) Is Nothing Then Exit Sub If myRange.Value = "AR1" Then On Error Resume Next Me.Rows("60:89").EntireRow.Hidden = False Me.Rows("90:112").EntireRow.Hidden = True On Error GoTo 0 Else On Error Resume Next Me.Rows("90:112").EntireRow.Hidden = False Me.Rows("60:89").EntireRow.Hidden = True On Error GoTo 0 End If End Sub I added the On error statements becuase IIRC, when the cells are already hidden and you try to hide them, you get an error. -- HTH, Barb Reinhardt If this post was helpful to you, please click YES below. "jpzachar" wrote: I have the following macro that I want to run when cell M9 equals either "AR1" or "AR2". Is this possible? thanks! Sub Macro7() If Range("M9").Value = "AR1" Then Rows("60:89").Select Selection.EntireRow.Hidden = False Rows("90:112").Select Selection.EntireRow.Hidden = True Else Rows("90:112").Select Selection.EntireRow.Hidden = False Rows("60:89").Select Selection.EntireRow.Hidden = True End If End Sub |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| If a cell equals then macro | Excel Programming | |||
| If a cell equals _, at the next row that equals _, return value fr | Excel Worksheet Functions | |||
| if a:a (range) equals january and c:c equals gas then add g:g ($) | Excel Worksheet Functions | |||
| how to run a macro when a calculation equals a certin number | Excel Worksheet Functions | |||
| custom filter does not work when selecting 'equals' X AND 'equals' | Excel Discussion (Misc queries) | |||
Linear Mode
