Adding code to click event 4 runtime commandbutton (VBA for Word 2000)

Topic

New Reply

I am adding a row of controls in a “For each” loop. I just realized I needed to add a CommandButton. No problem. However, how do I add code to the Click event for each of these dynamically added CommandButtons? I don’t know in advance how many of these buttons will be created.

I am using the following code in the middle of the “For each” loop to add the CommandButton:

Set MyCommandButton1 = frmAnalyzeListFormat.Controls.Add(“Forms.CommandButton.1”, “MyCommandButton1_” & MyCommandButton1Number)
MyCommandButton1.Height = 18
MyCommandButton1.Width = 45
MyCommandButton1.Top = PreviousLabelRowHeight + 17
MyCommandButton1.Left = 282
MyCommandButton1.Caption = “Show Me”

P.S. The code for the click event will be the same for each button, but I want to add a button for each row to prevent confusion and for usability.

Thanks!! Troy

Reply | Quote

Replies

Troy,

You can programmatically write an event procedure for each of the dynamically-created controls. Since each of the command buttons is going to run the same procedure when clicked, the click event procedure code required for each command button would be simple, along the lines of:

Private Sub MyCommandButton1_Click
‘call a procedure which can be in a standard module:
ProcedureToCall
End Sub

As far as dynamically writing this code itself, if you set a reference in your code project to the VBA Extensibility library, this will give you the tools to create code to write code.

The following is a snippet from a project I did earlier this year; this involved programmatically creating toolbar controls as well as OnAction code for them into a standard module. What you’re doing is a bit different in that you’re creating controls on a form, and need to programatically create event procedure code in the userform’s private module.

So the code from my project is different from what you need, but might give you some ideas as a starting point:

'Now add OnAction procedures for each control:
Set modDynCode = objCurProj.VBComponents("modDynCode")
With modDynCode.CodeModule
    'First, delete any pre-existing code in the code module:
    lngCodeLineCt = modDynCode.CodeModule.CountOfLines
    If lngCodeLineCt > 0 Then
       modDynCode.CodeModule.DeleteLines 1, Count:=lngCodeLineCt
    End If
    
    'Create a procedure keyed to each control:
    For e = 1 To CommandBars(strPopupName).Controls.Count
        .AddFromString _
            ("Public Sub ctlBarControl" & e & _
                "_OnAction" _
               & vbCr & "cbrBar.Controls(" & e & ").Visible" _
               & "= Not cbrBar.Controls(" & e & ").Visible" _
                & vbCr & "End Sub")
    Next e
End With

Gary

Reply | Quote

Edited by TroyWells on 08-Dec-01 20:02.

That sounds great, but have a few questions:

1) I found this library by going to Tools/References and selected “Microsoft Visual Basic for Applications Extensibility 5.3”, but I don’t see that it added anything to my References folder for the project. Should it be?

2) Also, there is one thing I forgot to tell you that may or may not affect anything: I need this to work with BOTH Word 97 and Word 2000. Any problems?

3) Is FindListFormatProcedure in my code below (same as modDynCode in your code) supposed to be the name of the module containing these click events or is this somehow automatically added to userform’s (in my case frmAnalyzeListFormat) code?

4)The following is the code that I added. NOTE the line of code that kicks me out of the sub where I add the button code. NOTE also the line of code the errors when removing the code:

‘As the button is created I am adding code for that button. This made things much simpler.
Set MyCommandButton1 = frmAnalyzeListFormat.Controls.Add(“Forms.CommandButton.1”, “MyCommandButton1_” & MyCommandButton1Number)
MyCommandButton1.Height = 18
MyCommandButton1.Width = 45
MyCommandButton1.Top = PreviousLabelRowHeight + 17
MyCommandButton1.Left = 282
MyCommandButton1.Caption = “Show Me”
‘Add code for click event.
Dim FindListFormatProcedure As VBComponent
Dim objCurrProj As VBProject
‘ Set objCurrProj = ActiveDocument
****************************************************************
‘The following line of code kicks me out of the sub:
Set FindListFormatProcedure = objCurrProj.VBComponents(“FindListFormatProcedure”)
*********************************************************************
With FindListFormatProcedure.CodeModule
.AddFromString _
(“Public Sub MyCommandButton1_” & MyCommandButton1Number & “_Click” & vbCr _
& “P = ” & MyCommandButton1Number & vbCr _
& “FindListExample” & vbCr _
& “End Sub”)
End With
******************************

The following code I put in other events (such as the click event of a Cancel button I have) to delete the code:
Dim FindListFormatProcedure As VBComponent
Dim objCurrProj As VBProject
Dim lngCodeLineCt As Integer
‘Set objCurrProj = ActiveDocument
**********************************************
‘The following 2 lines of code gives me an “Object variable or With block variable not set” error.
Set FindListFormatProcedure = objCurrProj.VBComponents(“FindListFormatProcedure”)
With FindListFormatProcedure.CodeModule
*******************************************************
‘first delete any existing code.
lngCodeLineCt = FindListFormatProcedure.CodeModule.CountOfLines
If lngCodeLineCt > 0 Then
FindListFormatProcedure.CodeModule.DeleteLines 1, Count:=lngCodeLineCt
End If
End With

Thanks for all your help!!
Troy

Reply | Quote

Hi Troy,

1) If you put a checkmark next to “Microsoft Visual Basic for Applications Extensibility 5.3” and OK’ed the dialog, then next time you open the References dialog, you should see it is checked and towards the top of the list of references. It’s not going to add a reference that’s visible in the References folder of the project – I think that only references to templates will show up there. Another place where you will now see a reference is if you go to the Object Browser (F2) – click on the listbox that’s headed “All Libraries”, and you should see one called “VBIDE”. Click on that and you should be able to examine the objects, properties and methods pertaining to that.

2) Word 97 vs Word 2K – that may cause a problem. If memory serves, the one for ’97 is 5.2 and the one for 2K is 5.3. This will make things more complicated, and having never done it, all I know about it is what I’ve read here, but it may be possible to use late binding rather than set the reference during design time – i.e. have the code test for which version of Word is running, and then use CreateObject to get to the appropriate library – a topic unto itself.

3) In my code example “modDynCode” is the name of the code module (which name I had previously set manually), into which the code is going to get programmatically written. So in your code, you would want to use the name of the userform’s module.

4) Probably the code is kicking you out because you’re using the name of a procedure, rather than the name of the userform module.

Another key question: you’re adding controls to the userform programmatically; is part of this procedure (earlier on), the actual programatic creation of the userform itself? – cause if it is, you may not be able to reference that userform’s code module, as part of the same procedure later on. That’s because the VBIDE doesn’t appear to “register” components immediately when they’re added – at least when adding controls manually, sometimes I’ve found Ilve needed to save and close the project, and then come back in, before some of the properties relating to a new control are recognized by the compiler.

The DeleteLines code is probably not relevant for your procedure; I only included it in the snippet for interest’s sake and ’cause it might be useful in some other situation. Once again though, the reason it’s giving an error is probably because you are using the name of a procedure, rather than the module itself. There’s no direct way, using Extensibility, to reference an individual procedure; all you can do directly is reference the code module itself.

Hope this helps,
Gary

Reply | Quote

Thanks for your patience and time. I really appreciate it, and am learning a lot!!

I do have some followup to your responses as follows:

1) Thanks!! I think I’ve got it.

2) I’ll check that out.

3) You say “modDynCode” is the module you setup manually. I assume by that you mean you created an empty module by that name. I did the same name, although the name I gave it probably gave you the impression that it was the name of a procedure.

You say “you would want to use the name of the userform’s module.” By that do you mean the module that appears when you right-click on the form in Project Explorer and click “View Code”? That would make sense to me. However, how do you reference that? Do you have to dim it as something and/or set it as something? I couldn’t figure out how to access that.

4) No the form is already created. I am only resizing the form as I go and adding controls. Also, as I said for #3 above, I am referencing a module, but it is a stand alone module, not the module for the userform. I think if you can help me reference the correct module, that will take care of my problems.

Thanks again!!
Troy

Reply | Quote

Hi again,

Glad to help.

Based on something in your first post:

<>

– I’m figuring that the dynamically-created code has to go into the userform’s code module, and not a standard module, because a CommandButton_Click event procedure must be in the userform’s code module, and not in a standard module. (You can call a procedure that’s in a standard module, from within the event procedure, but the event procedure itself must be in the userform’s code module.)

Also what I’m figuring is that if you’re going to dynamically create code, it will have to be the entire event procedure (or all of the event procedures if there are to be several), because there’s no direct way to deal with individual procedures, using the extensibility features.

So the code I posted sets an object (variable) reference to a standard code module:

Set modDynCode = objCurProj.VBComponents(“modDynCode”)

But in your case you would want to set this reference to the userform’s code module:

Set modDynCode = objCurProj.VBComponents(“frmUserFormName”)

– I’ve never done this with a userform’s code module, but am assuming (hoping) that it works the same as with a standard module – let me know if that works.

Hope the above clarifies.

Regards,
Gary

Reply | Quote

Thanks again for the help!!

However, I am still getting kicked out at line of code where I set the MODULE as the userform module. This is the specific line of code:

Set FindListFormatModule = objCurrProj.VBComponents(“frmAnalyzeListFormat”)

I’ve also tried:

Set frmAnalyzeListFormat = objCurrProj.VBComponents(“frmAnalyzeListFormat”)

Both times I get kicked out of the sub at this point.

Perhaps there is something I’m not dimming properly or setting? I’ve included the entire block of code. This is all I have related to adding this code. Let me know if I’m missing something here. Am I supposed to set objCurrProj = to something?

Dim FindListFormatModule As VBComponent
Dim objCurrProj As VBProject
Set FindListFormatModule = objCurrProj.VBComponents(“frmAnalyzeListFormat”)
With FindListFormatModule.CodeModule
.AddFromString _
(“Public Sub MyCommandButton1_” & MyCommandButton1Number & “_Click” & vbCr _
& “P = ” & MyCommandButton1Number & vbCr _
& “FindListExample” & vbCr _
& “End Sub”)
End With

Thanks!!
Troy

Reply | Quote

Hi again,

It looks like you’re missing a Set statement i.e. after declaring the objCurrProj variable, you need to use a Set statement to assign something to it. Sorry, I left that (important) part out of the code snippet I posted – it’s a bit complicated ’cause I was determining the active document’s attached template, setting an object reference to that template, then opening that template as a document, and finally setting a reference to that template’s VB Project (this was because in my project, I needed to run the code not only from a different module, but from a different template):

Dim objCurDoc As Document
Dim objAttTmp As Template
Dim objOpenTmp As Document
Dim strTmplPath As String
Dim objCurProj As VBProject

strTmplPath = ActiveDocument.AttachedTemplate.FullName
Set objAttTmp = Templates(strTmplPath)
Set objOpenTmp = objAttTmp.OpenAsDocument
Set objCurProj = objOpenTmp.VBProject

I don’t know if this convoluted approach is necessary for what you’re doing; possibly you can run the code from a different module, in the same VB Project (aka template) as contains the module you’re trying to write code into, so you don’t need to open the template as a document (but I’m not sure….).

But in any case, you’ve got to set something to objCurProj first. You’ll need to play around to get something that works; depends on where you’re running the code from. If nothing else works, you could try code like the above.

Gary

Reply | Quote

Edited by TroyWells on 12-Dec-01 03:29.

Thanks for the info!!

I actually plan on my project being loaded as a global template. Then I would run the code from a standalone module in that global template to write code to the userform also in that global template.

The code to add the click event code for each button works great, but I have a problem with the code to delete the code I added. See below:

‘This is the code I am using to add the code for the command button.
Public GlobTemp As Template
Public GlobTempPath As String
Public objCurProj As VBProject
Public FindListFormatModule As VBComponent
———————————————————————-
GlobTempPath = “E:My DocumentsGoalsFormatDocuments.dot” ‘ActiveDocument.AttachedTemplate.FullName
Set GlobTemp = Templates(GlobTempPath)
Set objCurProj = GlobTemp.VBProject
Set FindListFormatModule = objCurProj.VBComponents(“frmAnalyzeListFormat”)
With FindListFormatModule.CodeModule
.AddFromString _
(“Public Sub MyCommandButton1_” & MyCommandButton1Number & “_Click” & vbCr _
& “P = ” & MyCommandButton1Number & vbCr _
& “FindListExample” & vbCr _
& “End Sub”)
End With

‘This is the code that I am using to delete the code I added above. See the note for the problem.
Dim P As Integer
Dim lngCodeLineCt As Integer
lngCodeLineCt = 0
GlobTempPath = “E:My DocumentsGoalsFormatDocuments.dot” ‘ActiveDocument.AttachedTemplate.FullName
Set GlobTemp = Templates(GlobTempPath)
Set objCurProj = GlobTemp.VBProject
Set FindListFormatModule = objCurProj.VBComponents(“frmAnalyzeListFormat”)
With FindListFormatModule.CodeModule
For P = 1 To 1 ‘UBound(ListFormatArray, 2)
lngCodeLineCt = lngCodeLineCt +

‘NOTE: The line of code below is where I am getting a “Sub or Function not defined” error.
FindListFormatModule.CodeModule.ProcCountLines(“MyCommandButton1_” & P, vbext_pk_Proc)

Next P
If lngCodeLineCt > 0 Then
FindListFormatModule.CodeModule.DeleteLines 1, Count:=lngCodeLineCt
End If
End With

ONE OTHER QUESTION: based on what you said in an earlier post about the VBA Extensibility library:

“2) Word 97 vs Word 2K – that may cause a problem. If memory serves, the one for ’97 is 5.2 and the one for 2K is 5.3. This will make things more complicated, and having never done it, all I know about it is what I’ve read here, but it may be possible to use late binding rather than set the reference during design time – i.e. have the code test for which version of Word is running, and then use CreateObject to get to the appropriate library – a topic unto itself.”

I’ve looked at the help on this and can’t find the syntax to add a reference to the library. I have code to check the version. All I need is to add the reference. (I assume I need to do this to make sure the code that uses this library will work on other PCs. If not let me know.) Will this adding of the reference only need to happen once? I assume the addition of the reference will cause the template to want to be saved upon closing Word.

If I can take care of these two things, I think this issue can finally be closed (with much gratitude to you for what I’ve learned along the way).

Thanks again!!
Troy

Reply | Quote

Hi Troy,

I got hit with a work tsunami on Monday so can only do quick hits here this week.

Anyway, I’m kind of stuck understanding this code:[indent]

---

With FindListFormatModule.CodeModule
For P = 1 To 1 ‘UBound(ListFormatArray, 2)
lngCodeLineCt = lngCodeLineCt +

‘NOTE: The line of code below is where I am getting a “Sub or Function not defined” error.
FindListFormatModule.CodeModule.ProcCountLines(“MyCommandButton1_” & P, vbext_pk_Proc)

Next P
If lngCodeLineCt > 0 Then
FindListFormatModule.CodeModule.DeleteLines 1, Count:=lngCodeLineCt
End If
End With

---

[/indent]- it seems like there are things missing there(?)
Also, why are you deleting the code, after inserting it?

Regarding setting a reference programatically, do a search on this forum for that topic – there were a couple of good threads on that earlier this year, and you’ll find some solutions there most likely.

Lastly, have a look at the completely different approach suggested by “VBADUDE” – I don’t have time to digest it tonight, but that might turn out to be the way it’s supposed to be done!

Regards,
Gary

Reply | Quote

WHOA – while I don’t disagree with the earlier advice, I think there is a much easier way. You can create a class of CommandButtons using the “WithEvents” keyword and trap the events in the class module. Dynamically writing code on the fly is a funny thing. I’m doing the same thing. I got the code out of the Learn Word 2000 VBA book – the code is in there verbatim. It’s covered in about two pages that you could easily browse at the store without spending the $$$ book.

Reply | Quote

alright – this is a straight rip off

…..
Now, let

Reply | Quote

Alright, this is killing me. I TOTALLY disagree with this “WORD MVP” posts. If I’m reading this correctly, he is suggesting programmatically adding the SAME event procedure every time a button gets added???

Make your life much easier and safer – do things correctly and use the class module.

Reply | Quote

Hey, VBADude:

Don’t make a big deal of the “Word MVP” label, and do bear in mind that I’m learning here too, just like everyone else! (as well as volunteering my free time to help others).

If you can point to a more efficient way to achieve something, then we’re all happy to hear it – that’s what this place is for.

Posting a sizable chunk from someone’s book, without permission or attribution, might not be a very good idea, and might not be in line with the Lounge’s rules. Better to get permission first, or else make the effort to explain in your own words.

Cheers,
Gary

Reply | Quote

Hi,
Yes the same event procedure is being created each time, inasmuch as it’s a Button_Click event procedure, but the actual code contained within that procedure is different each time.
I would also point out that, whilst everyone is free to disagree with any suggestion made, the written word is open to different interpretation than that which is intended. Your use of quotes around Word MVP, for example, could be construed as insulting, whether it was intended that way or not. The MVP title is not given lightly (well, except in my case ) but rather as a mark of respect for a long history of valuable contribution to the Lounge. This is not to say that MVPs should be treated differently to anyone else – the Lounge Rules state in section 9 “just try to be courteous”. This applies to everyone equally at all times.
Thanks.

Reply | Quote

I’ve worked for an hour to try to get this work with a class module, but I guess I don’t have quite enough knowledge of the subject to get it to work. Not to mention I am running out of time to complete this project. Otherwise, I’d love to bang at it til I got it, and later, I’m sure I’ll do that.

All I want is to add code for each programmatically added command button, and then be able to remove the code when I remove the command buttons later. The command buttons are added in a for each loop that loops through one row in a 2 dimensional array. They are added based on various list formatting characteristics found in the document. If I cancel or choose to reanalyze the document, I want to clear all the buttons (no problem) and all the associated code.

The only thing I haven’t got to work is removing the code.

If you could either help me get that one line of code working where I need to remove it, or add more detail to explaning how to do this in a class module, I’d appreciate it!!

My existing code that is working except for one line is at:

http://www.wopr.com/cgi-bin/w3t/showthread…&vc=1#Post99271

Thanks again!!
Troy

P.S. I know you were excited because of your understanding in the area of class modules. I get excited when I know better ways of doing things too. However, you might just want to be careful how you express that excitement so that it doesn’t come across as disrespecting those of us that don’t have that understanding. OK, enough panking!!

Reply | Quote

sorry guys – i’m new to the forum and get a little “punch” sometimes – i thought as a “Word MVP” you were delivering the gospel directly from Microsoft. I have a lot of anxiety when it comes to dealing with MS on Office issues.

here is the actual file

Reply | Quote

No problem – class modules are a good solution and it’s always good to have more than one way of achieving the same goal!
FYI, the Lounge is not affiliated to MS in any way (as you may be able to tell from reading some of the posts! ) – all the MVPs here are Woody’s MVPs, not MSMVPs (though some of them do drop in from time to time).

Reply | Quote

Hi again,

Just wanted to append this link to John Walkenbach’s site – this is a downloadable demo for creating userform controls and the code behind them, programatically – this is the same method I had suggested to Troy.

You can also find this information in his book Excel 2000 Power Programming with VBA – on pp. 738-745. A book well worth purchasing, by the way.

If this method is as backward as you suggest, at least I’m in good company!

Gary

Reply | Quote