Run a macro based on a cell value

[Lisa C.]

I have a worksheet named "Sub-Form". On this worksheet, in cell "P10", I
have added a data validation list. When "Standard System" is selected from
the list in cell P10, I want to run a macro titled "SubForm1" that hides or
unhides rows on the worksheet. When "Display System" is selected from the
list in cell P10, I want to run a macro titled "SubForm2" that hides or
unhides a different set of rows. I know how to code the hiding and unhiding
of rows. What I need to know is how to get the value that is selected in
cell P10 (Standard System, Display System) to trigger the appropriate macro
to run. Another option is to have only one macro that is triggered by a
non-blank value in cell P10 that has IF/THEN statements based on what value
is in the cell ("Standard System", "Display System"), but I don't know how to
trigger the macro or write the IF/THEN statements.

 

[Gord Dibben]

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Me.Range("P10")) Is Nothing Then Exit Sub
Select Case Target.Value
Case "Display System"
SubForm1
Case "Standard System"
SubForm2
End Select
End Sub

This is sheet event code. Right-click on the sheet tab and "View Code"

Copy/paste into that module. Alt + q to return to the Excel Window.


Gord Dibben MS Excel MVP