VBA: How to dynamically assign a macro to a button based on cell value changes?
I'm trying to debug I am trying to create a dynamic button in Excel using VBA that changes its assigned macro based on the value of a specific cell. For example, if cell A1 has the value "Option1", the button should execute 'Macro1', and if A1 has "Option2", it should execute 'Macro2'. I am working with issues with the button not updating its assigned macro when the cell value changes. I have set up an event handler for the Worksheet_Change event, but it seems that after changing the button's macro, it doesn't execute the new macro as expected. Here’s what I have so far: ```vba Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Me.Range("A1")) Is Nothing Then Dim btn As Object Set btn = Me.OLEObjects("Button1") If Target.Value = "Option1" Then btn.Object.OnAction = "Macro1" ElseIf Target.Value = "Option2" Then btn.Object.OnAction = "Macro2" End If End If End Sub ``` After changing the button's action, I expected it to run the new macro when clicked, but it still runs the old one. I also noticed that when I debug, it seems like the button's action gets correctly assigned, but the new macro does not execute. I’ve ensured that the macros 'Macro1' and 'Macro2' are both defined in a standard module. Am I missing something in the event handler or button configuration? Any advice to ensure the button reliably updates and executes the correct macro would be greatly appreciated! This is part of a larger desktop app I'm building. Any ideas how to fix this?