Hello,
Have worked a bit with this based upon this post → How to programmatically get/set a form control’s events.
Have attached a sample Calc file based upon this to demo. Has four buttons. First is to display a message on click. Second and third buttons are to modify what macro is called by the first button and the message changes accordingly. The last button is to clear the macro completely from the first button. This is a problem as sometimes the clear button must be clicked two or even three times. Have tried various failed remedies in the macro but no complete resolution.
Also on a Sheet 2 of the document are most of the controls with associated Listener, Event Method and Control Event.
Edit:
Corrected sample -------------- ModifyButtonEvents.ods
Code cleaned up and fix applied to last button:
As the answer in the link states:
…you can’t just update the .ScriptCode property. You need to remove, then re-add the event.
Edit 2020-01-04:
@Lupp in regard to the listing of event types I will try to explain what I found.
There are more than three Listener Types. Have noted that in my sample provided there is a second sheet which lists most controls with the associated Listener, Event Method and Control Event. I have found no method to get these by simply examining a control. I actually created this listing by generating a control and attaching a macro to every event in that control.
After obtaining the index of the control (as you have in code in comment) used a simple routine to list the information:
aControlEvents = oObj1.getScriptEvents(controlIndex)
LowBound = LBound(aControlEvents)
UpBound = UBound(aControlEvents)
Row = 50
For x = LowBound to UpBound
with aControlEvents(x)
oSheet.GetCellRangeByName("C" & Row).String = .ListenerType
oSheet.GetCellRangeByName("D" & Row).String = .EventMethod
oSheet.GetCellRangeByName("E" & Row).String = .AddListenerParam
oSheet.GetCellRangeByName("F" & Row).String = .ScriptType
oSheet.GetCellRangeByName("G" & Row).String = .ScriptCode
end with
Row = Row + 1
Next
Did this for every control listed on Sheet2
of the sample and created the resulting matrix.
Edit #2 2020-01-04:
Updated sample ----- ModifyButtonEvents.ods
The mods were to Sheet2 which contain the events and now has all controls and added ClassID (control constant).
There are no events for a Hidden Control.
There are two ClassID #9’s - Text Box and Formatted Field.