Excel Userform Problem 3

[snikde]

Hi,

I am adding commandbuttons and other controls at runtime to a userform in Excel. Once added the buttons do not work based on the subroutine that should catch the event. Example below:

Sub PushButton_click()
Beep
End Sub

Private Sub UserForm_Click()
Beep
End Sub

Private Sub UserForm_activate()
Set myButton = Me.Controls.Add("Forms.CommandButton.1")

With myButton
.Left = 5
.Top = 65
.Height = 20
.Width = 50
.Name = "PushButton"
.Caption = "Store Map"
Debug.Print .Name
End With
End Sub

Clicking the form makes it beep but not the button. What am I missing?

TIA

 

[VBAjedi]

I don't understand why you need to add a button at runtime if the event code for that button can be added ahead of time. Could you clarify what you are trying to accomplish?

VBAjedi

 

[snikde]

Good Question , Long story. Since in VBA you cannot create an object array like you can with VB I am trying to make a series of text fields that I know will be the last objects created on the form. That way I an get the object count for the form and assume the last n items are the ones I need to parse. Now, I figured while I was at it I could add the buttons on the fly in to the frame too. So far no luck. I could just leave it as I had with the buttons created on the form before run time but I find it strange that you can create the button, name it, and position it all on the fly but not utilize it to run code.

 

[VBAjedi]

Ok - now that I know what you are after, I think you will find that thread707-572718 addresses your situation pretty well. There's some really good stuff particularly in the second half of the thread.

What it boils down to is that you will need to use a custom class to get your control array and it's events working. Don't worry, an example is provided.

Let me know if (for some reason) that thread doesn't help!



VBAjedi

 

[combo]

Hi,
Visual Basic does a lot of things behind the scene and this is an example. It looks like VBA creates hidden WithEvents variables for each control added at design time, their names correspond to controls' names (but in general it is not required).
This does not take place when creating controls at runtime, this is why your code does not work. It is necessary to add WithEvents declaration explicitly and an event handler(s). To make your code work for single button (userform module):

Private WithEvents CommandButton1 As CommandButton

Private Sub CommandButton1_Click()
MsgBox "click"
End Sub

Private Sub UserForm_Click()
Set CommandButton1 = Me.Controls.Add("Forms.CommandButton.1", "cmdX")
End Sub

If you create reasonable number of controls (known upper limit) at runtime, you can prepare the form to handle events by adding more WithEvents declarations and appropriate event procedures (via code module objects and events lists). Runtime controls assigned to such variables will respond to events.
Note that you can't handle all control's events this way.

combo

 

[VBAjedi]

Well-written post, Combo - have a star.

VBAjedi

 

[snikde]

Thanks guys! Great info and I have solved the problem with the info.