Create Events for indeterminate number of controls

Topic

New Reply

Here’s a good head-scratcher:

I need to create Click events for each control on a popup commandbar which is being created programmatically.

Following the instructions in the excellent “MS Office 2000 VB Programmer’s Guide”, by MS Press (highly recommended) I’ve created an Events class to respond to control click events.
The book’s sample code indicates that the class needs to contain a Public WithEvents declaration for each control on the commandbar. This all works great.

Here’s the tricky part: I don’t know, as I’m writing the code, how many controls there will be on the popup commandbar – the number of controls in the popup is based on current conditions in the user environment and therefore I can’t determine beforehand how many controls it will contain – but I do need a Click event for each control, once the popup is created.

I can easily enough have my code loop through all the controls in the new commandbar, and for each control use a Set statement to link to the control’s events, but the sticking point is that I can’t determine beforehand how many references to controls need to be declared back in the Events class.

Is there an efficient way to do this? – I could arbitrarily say that the popup will contain a max of say 20 items, and write 20 WithEvents declarations, and 20 Control_Click procedures, in the class module, but that doesn’t seem good form.

Gary

Reply | Quote

Replies

Hi Gary,
Forgive my ignorance, but what is the difference between a Click event of a commandbar control and the OnAction property? Or am I completely missing the point (it has been a long day)?

Reply | Quote

Hi Rory,

Good question. Since posting the original question a few weeks ago, I put this one aside, and my brain (which is staging a Monday-induced work slowdown) is now having a hard time remembering that specific detail – I’ll need to go back to the project and check.

It involved programatically creating a popup menu (with an indeterminate number of items) and assigning macros to run when an item on the menu was clicked. Either I tried and rejected the OnAction property in this context, or else I simply didn’t think of it! I’ll have to go back to the code and give that a try.

Thanks,
Gary

Reply | Quote

Hi Rory,

I meant to follow up on this post from a couple of weeks ago and obviously – forgot.

I still can’t cite the practical differences between a Control Click event and an On Action macro – my initial emphasis on using a Click event for this project, may have simply been the influence of reading the Office 2K VBA book – it emphasized the Click event since this was new to Office 2K.

In the end though I did end up using the On Action method – this project involved code that creates more code on the fly – and it turns out much easier to do this in the context of an On Action macro in a standard module, than trying to program events in a class module on the fly – actually it’s pretty easy to write the class module code on the fly, but I kept crashing Word when I tried to initialize the class that the same procedure had just built!

Reply | Quote

Hi Gary,
Thanks for the reply. I kept meaning to look this up in the MS VBA book as (to my simple mind) it seemed unnecessarily complex to have to create a click event for a control whose sole purpose is to be clicked! But, as usual, I got distracted by more pressing needs and completely forgot
Now that you’ve reminded me, I will (probably!) get around to it as there must be a reason for it…

Reply | Quote

Hi Calacuccia,

Thanks for the suggestion.
What I’d come up with in the interim since posting the question was to use either:

– Add the code on the fly as you suggest
– Handle the menu items (they’re on a popup menu) via API functions. There’s an article by Scott Driza in the latest issue of MS Ofc & VBA Developer magazine that describes creating a popup thesaurus in Excel. The methods he explains could be adapted for the sort of thing I’m trying to do.

Because this was a “fooling around” sort of project, I put it aside at that time and haven’t picked it up since then. Probably it can be done either of the above ways; it might be worth trying them both. If I get something interesting running I’ll post again.

Gary

Reply | Quote

Hi Calacuccia,

Just following up: I’ve had a chance to pick up this practice project, and can report that it’s quite practical to use the VBA extensibility library to among other things, create code on the fly.

I was able to dynamically write all the code for a class module including declarations, with the number of declarations and event procedures varying depending on environment conditions when the procedure starts running.

But I ran into a problem trying to initialize this class (kept crashing Word) so settled for using the same approach to write procedures in a standard module, which are assigned to various toolbar controls via OnAction.

Here are some relevant snippets of code:

'This is an object reference to a class module that we need
'to add code to, this is not (yet) initializing the class:
Set clsNewEvents = objCurProj.VBComponents("clsDynEvents")

'Delete any pre-existing code in the class module (from last time macro ran):
lngCodeLineCt = clsNewEvents.CodeModule.CountOfLines
If lngCodeLineCt > 0 Then
    clsNewEvents.CodeModule.DeleteLines 1, Count:=lngCodeLineCt
End If

This next bit is nested in the middle of a loop counter that does various things for each control on a particular menu (“c” is the counter variable):

    'Add event declarations for this control:
    With clsNewEvents.CodeModule
        .AddFromString _
            ("Public WithEvents ctlBarControl" & c & " As Office.CommandBarButton")

    End With

And:

'Now add event procedures for each control:
For e = 1 To CommandBars(strPopupName).Controls.Count
    With clsNewEvents.CodeModule
        .AddFromString _
            ("Private Sub ctlBarControl" & e & _
                "_Click(ByVal Ctrl As Office.CommandBarButton, CancelDefault As Boolean)" _
                & vbCr & "MsgBox ""Clicked on popup control " & e _
                & vbCr & "End Sub")
    End With
Next e

As I mentioned, I ended up abandoning all this! I’ll try to post the completed project when I can – besides the programming interest, it provides a (hopefully) useful custom version of a built-in Word feature.

Gary

Reply | Quote