Run a Macro based on the Value of a Cell in a Worksheet

[Snake Eyes]

I have a worksheet that can be duplicated and renamed any number of times within the same workbook.
I've created a macro that runs other macros based on the value of one cell (AC2) in the sheet.
Cell AC2 is an in cell dropdown (data validation) with preset values.
This macro works when assigned to a button in the sheet.
When I manually select a value from the dropdown options in AC2, the macro runs as it should when I press the button.

I would like to automate this process by having the macro run whenever I change the value of AC2 by selecting another value in the dropdown and therefore eliminate the button press.
How do I do this?

I've done a little research and have found that these lines should help but they do nothing when I put them at the top of the module.
I'm also concerned that this code may apply only to the one sheet and I need this to be part of a template that, as I mentioned, could be copied and renamed any number of times.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim PlanOption As String
PlanOption = Range("AC2").Value

Any assistance would be appreciated.

 

[rollis13]

If you paste this macro in the "ThisWorkBook" module it will trigger for changes in cell AC2 of every sheet. As preparatory activity you need to update your macro so that it's ranges reference the active sheet that can be retrevied from the object "Sh" (example: PlanOption = Range("AC2").Value becomes PlanOption = Sh.Range("AC2").Value )

Option Explicit
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    If Not Intersect(Target, Sh.Range("AC2")) Is Nothing Then
        '
        'here goes your macro
        '
    End If
End Sub

 

[Snake Eyes]

Thanks for the tip rollis13.
These instructions seem to work for any selection except when AC2 is blank.
These sheet blanks out the proper cell and then fails with an error.

In the Microsoft Excel Objects folder in ThisWorkBook I entered this calling the macro.

Option Explicit
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    If Not Intersect(Target, Sh.Range("AC2")) Is Nothing Then
    '
      Call ShowPlanData.ShowPlanData
    '
  End If
    
End Sub

In the ShowPlanData macro there is a line that starts the process to clear cells and seems to be the issue.

    If Range("AC2") = "" Then

' Unprotects the worksheet

    ActiveSheet.Unprotect
'
'   Clears the Contents of the Data Cells

    Range("W12:AD40").Select
    Selection.ClearContents
    
    Range("AC2").Select
    Selection.ClearContents
'
    Range("W12:AD40").Select
    Application.CutCopyMode = False
    Selection.ClearContents

ElseIf Range("AC2") = "Hold for a Few Weeks" Then
Call HoldStock_Plan1.HoldStock_Plan1


ElseIf Range("AC2") = "Hold for a Few Months" Then
Call HoldStock_Plan2.HoldStock_Plan2


ElseIf Range("AC2") = "Sell As Soon As Possible" Then
Call SellStock_Plan1.SellStock_Plan1

ElseIf Range("AC2") = "Sell Within a Few Months" Then
Call SellStock_Plan2.SellStock_Plan2


  End If

End Sub

When I remove this line If Range("AC2") = "" Then the rest of the macro errors because the ElseIf lines require the If line.
Any suggestions?

 

[rollis13]

You didn't consider the second sentence of my suggestion. If you're going to use a "Call", you'll also need to convey the name of the sheet you're working on so that you can refer to the correct range. This corresponds to the information contained in the object "Sh".
Otherwise you could, instead of using the "Call", merge your macro and update the range references as I showed in my post.