programming the VBE for a new event procedure

M

mark kubicki

i'm trying to add code to give function to a newly created command button on
the form "frmTimeClock"
but, am not quite getting it right... (?)

the error occurs at line 7 (57017, event handler is invalid)

----------------------------------------------
1 Private Sub CommandButton1_Click()
2 Dim StartLineNum As Long
3 Dim mycmd As MSForms.CommandButton

4 Set mycmd = frmTimeClock.Controls.Add("forms.commandbutton.1")
5 mycmd.Caption = mycmd.Name

6 With ActiveWorkbook.VBProject.VBComponents("frmTimeClock").CodeModule
7 StartLineNum = .CreateEventProc("click", mycmd.Name) + 1
8 .InsertLines StartLineNum, _
9 "msgbox""You done it"",vbOKOnly"
10 End With

11 End Sub
 
T

Tom Ogilvy

? "msgbox""You done it"",vbOKOnly"
msgbox"You done it",vbOKOnly

do you just need some spaces?
 
M

mark kubicki

i had to change the line to be 2-part
msgtext = "You done it"
.InsertLines StartLineNum, (MsgBox(msgText, vbOKOnly))
this eliminated a error i was getting with the code
however, the event handler error is still there

i tried this alternate approach which did create the code, but the code does not function when i press the newly created command button
(ALL OF THIS IS HAPPENING ON THE SAME FORM WHICH REMAINS LOADED AND OPEN FOR THE DURATION -is this part of the problem?)


Set VBCodeMod = ThisWorkbook.VBProject.VBComponents("frmTimeClock").CodeModule
With VBCodeMod
msgtext = "you done it"
StartLineNum = .CountOfLines + 1
.InsertLines StartLineNum, "Private sub " & mycmd.Name & "_click()" & Chr(13) & _
"msgbox (msgtext)" & Chr(13) & _
"end sub"
End With

additionally, after running this procedure, i will get this message upon clicking another (but not all) of the other command buttons on the sheet:
"object invoked disconnected from client"

UGH !


--------------------------------------------------------------------------------
 
T

Tom Ogilvy

This isn't something I have attempted, but I was able to get a working procedure into the userform code module from a button on the form. I say working, because I dropped the form and added the button manually (the code was still there) and ran the form and the event fired. As a trouble shooting aid, I put in a textbox and a commandbutton to put the code from the module on the form. This ran fine before I programmatically added the button, but if I did it after I added the button (and code), I got the error you report. My code to create the event is in the userform code module as I suspect yours is. On Chip Pearson's site

http://www.cpearson.com/excel/vbe.htm

he cautions against this:
"Also, you may get unpredictable results if you attempt to modify a code module's code from that same module. That is, having code in Module1 modify the contents of Module1. I recommend that you do not do this."

My personal preference is to create all the controls up front with the appropriate events and then make them visible or not depending on the situation. This can also be done by controlling the height of the userform. I am sure you have seen dialogs that have a button to show detail and the dialog get much longer with additional controls when the button is pushed. This is simply done by having a complete userform with the height set to show only the top portion of the form, then changing it to include the remainder when the button is pushed.

John Walkenbach shows how to create a userform completely with code, but he does this before he attempts to show the form and uses the designer object which is equivalent to doing it manually as I understand it. Once everything is in place, he shows the form.

So if you still think you need to modify the code, you might drop the form, return to the calling routine where you call code to make the changes, then show the form again. I didn't try that, but if I had to, I think that is the next thing I would try.

--
Regards,
Tom Ogilvy


i had to change the line to be 2-part
msgtext = "You done it"
.InsertLines StartLineNum, (MsgBox(msgText, vbOKOnly))
this eliminated a error i was getting with the code
however, the event handler error is still there

i tried this alternate approach which did create the code, but the code does not function when i press the newly created command button
(ALL OF THIS IS HAPPENING ON THE SAME FORM WHICH REMAINS LOADED AND OPEN FOR THE DURATION -is this part of the problem?)


Set VBCodeMod = ThisWorkbook.VBProject.VBComponents("frmTimeClock").CodeModule
With VBCodeMod
msgtext = "you done it"
StartLineNum = .CountOfLines + 1
.InsertLines StartLineNum, "Private sub " & mycmd.Name & "_click()" & Chr(13) & _
"msgbox (msgtext)" & Chr(13) & _
"end sub"
End With

additionally, after running this procedure, i will get this message upon clicking another (but not all) of the other command buttons on the sheet:
"object invoked disconnected from client"

UGH !


------------------------------------------------------------------------------
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top