jiml27


I am trying to create a userform dynamically. I am having limited success, and have a few questions. For early binding, I use this code:

Code Snippet

Public UserForm1 as UserForm



The question, then, is how to instantiate the userform The code that I use for late binding does not work:

Code Snippet

set UserForm1 = ThisDocument.VBProject.VBComponents.Add(3)



and neither does a perfectly sensical "set UserForm1 = new userform"

with either binding, the userform that I have created does not seem to inherit all the actions from the userform class, like show and hide. Does anyone have any information about either of these

On another line of code investigatin, if anyone knows what Run-Time error 40044 is, I would be glad to hear. When I click on the help button, it brings up an empty window.

TIA,

Jim



Re: dynamic creation of userform

jiml27


Having no satisfaction, either here or elsewhere, on my early binding question, I am currently using late binding to create a userform. (That is still curious, as I have an O'Reilly book that is claiming that "Set UserForm1 as New UserForm" should work.) In any case, having worked through some additional difficulty, I have now returned to this simple question:

Why doesn't my dynamically created userform inherit all the actions, ie show, from the userform class

This might help: I just realized that when I am typing in the immediate window, it does show the "missing" attributes. So my userform does have them, but they are not accessible from inside my code module.

Tia,

Jim





Re: dynamic creation of userform

Andy Pope

Hi,

This will create, show and delete a userform.

Sub x()

Dim MyUserform As VBComponent

Set MyUserform = ThisWorkbook.VBProject.VBComponents.Add(3)

VBA.UserForms.Add(MyUserform.Name).Show

ThisWorkbook.VBProject.VBComponents.Remove VBComponent:=MyUserform

End Sub

Certain methods and events are not available when you create controls or userforms.


Can I ask why you need to create the userform using code






Re: dynamic creation of userform

jiml27

Thanks, that is interesting. One thing I don't understand, though, is the use of Dim inside the macro, and why if I put

Dim MyUserForm As VBProject

in the declarations, when I call

Set MyUserForm = ThisDocument.VBProject.VBComponents.Add(3)

I get type mismatch. In any case,

The userform itself does not necessarily need to be created by code, but it will have a variable # of controls on it, which are created with code, so I was doing the userform that way too. I believe I will go back to pre-creating the userform itself, and then just re-sizing it after the buttons are added, as if I am not mistaken (given what you said), I would not be able to do that to a code-created userform.

Thanks for your help.




Re: dynamic creation of userform

Andy Pope

The mismatch is simply because a VBComponent is not a VBProject object.

If you can create form and even controls before hand I would. For controls you can always not show any that are surplus.
Again with controls you may have problems with events not existing for dynamically created controls.

If you have not already I suggest you read Chip Pearson's page
http://www.cpearson.com/Excel/vbe.htm





Re: dynamic creation of userform

jiml27

Ha, Ha,

Of course I was misreading the type of object that I was supposed to declare. Sometime the simplest explanation is the right one.

I have gone back to pre-creating the userform, but since the buttons will be using information that is loaded fresh each time, I am not sure that I want to pre-create them, though I may yet do so. It seems a shame, though, after working up a method of looping through button creation, to throw it all away. The only problem being, of course, that I would like to loop through the buttons on the way out. I haven't been successful at this as yet, but I have ginned up a way to generate, separately, the name of the each button sequentially, ie, CommandButton1, 2, etc. I then wish to to use the variable that I have created to reference the individual CommandButtons. I picked up somewhere that I should use MyUserForm!buttonname or MyUserForm.[buttonname], but I have not been able to make either of these work. Unfortunately, I have been unable to find the page that I saw that on. Word seems to gladly accept the operator of "!", but I have no idea what it does, as it seems to be poorly documented.

Thanks for the link, that is interesting.





Re: dynamic creation of userform

Andy Pope


Here is some code to create 5 buttons, set there caption and finally delete when the form closes.
Note if you do create buttons on the fly you will still need to create a way to handle events as I assume you will want something to happen when the button is clicked.

Private Sub UserForm_Initialize()

Dim btnTemp As MSForms.CommandButton
Dim lngIndex As Long
Dim strName As String
Dim sngTop As Single
Dim sngLeft As Single
Dim sngHeight As Single
Dim sngWidth As Single
Dim sngGap As Single

sngLeft = 10
sngWidth = 100
sngHeight = 20
sngTop = 10
sngGap = 5
For lngIndex = 1 To 5
strName = "cmdMyButton" & lngIndex
Set btnTemp = Me.Controls.Add("Forms.CommandButton.1", strName, True)
With btnTemp
.Left = sngLeft
.Width = sngWidth
.Height = sngHeight
.Top = sngTop
End With
sngTop = sngTop + sngHeight + sngGap
Next

For lngIndex = 1 To 5
strName = "cmdMyButton" & lngIndex
Me.Controls(strName).Caption = "Button " & lngIndex
Next

End Sub


Private Sub UserForm_Terminate()

Dim lngIndex As Long
Dim strName As String

For lngIndex = 1 To 5
strName = "cmdMyButton" & lngIndex
Me.Controls(strName).Caption = "Button " & lngIndex
Next

End Sub


The ! can be used instead of the . (period) notation when referencing objects hierachy. This works in Excel but the dot method is more common.






Re: dynamic creation of userform

jiml27

Thanks, that is essentially the same code that I worked up after referencing many sources of documentation on the internet, though, of course, yours looks nicer. Fortunately, though, I do not need an "on click" event. I just check the buttons afterward for their value. The question, then, is how can I reference each button with strName and not a hardcoded reference like CommandButton1. Have you any idea




Re: dynamic creation of userform

Andy Pope

Use a variation of this example part where I set the caption of each button

For lngIndex = 1 To 5
strName = "cmdMyButton" & lngIndex
Me.Controls(strName).Caption = "Button " & lngIndex
Next



If the button has no events would labels not be more appropriate





Re: dynamic creation of userform

jiml27

Thanks, but unfortunately, when I call

If MyUserForm.controls(buttonname).value = true then . . .

I get the dreaded "Run-Time error '-2147024809 (80070057)': Could not find the specified object"

Have not been able to figure this error out previously. As to the buttons versus labels, I am under the (possibly mistaken) impression that I will be able to record the state of the button after I hide the userform, but (as near as I am aware), labels have no state. I noticed, though, while clicking the buttons, they do not show any sign of value. At this point, I believe I will retreat to OptionButtons instead of CommandButtons.

Thanks for all your help,

Jim




Re: dynamic creation of userform

Andy Pope

If you want a button that remembers its state you will need to use the toogle button.

If you use option buttons these normal work on a pick ONE from many approach. If you are allowed to pick more that one use Checkboxes.

You might also want to consider using a listbox. This can be set to display Option circle or Checkbox square. Plus with a large number of items you get automatic scroll bars.