need to add textboxes to userform on the fly

[Stuart Holley]

Hi

I need to add textboxes to a userform on the fly

if the user selects 4 out of 7 items from a listbox I need to create 8
textboxes on the userform

This is the bit I need to create on the fly dependant on number of
items selected.
Individual textbox with item selected then a blank text box so user
can but details in



Slot Location " "
Manufacture " "
Software Drivers " "
Firmware Version " "


Any pointers would be gratefully received.

Many thanks

Stuart

 

[Bob Phillips]

Stuart,

Suggestion.

Add the textboxes at the start, not on the fly, and make them visible or not
as required.

Textbox.Visible = False 'True

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Greg Wilson]

Further to Bob's response, my understanding is that you
cannot add nor delete a control on an existing UF at run
time. Your options are to create the entire UF on the
fly, or, alternatively, you can programmatically change
the visible property of controls on an existing UF.
Therefore, according to my understanding, Bob's advise is
mandatory for an existing UF - not a suggestion.

Also, as I discovered after waisting considerable effort:
A UF created on the fly cannot connect with a Class Module.

I may be corrected on the above. If so, I'll be very much
appreciative of being set straight.

Regards,
Greg

-----Original Message-----
Hi

I need to add textboxes to a userform on the fly

if the user selects 4 out of 7 items from a listbox I need to create 8
textboxes on the userform

This is the bit I need to create on the fly dependant on number of
items selected.
Individual textbox with item selected then a blank text box so user
can but details in



Slot

Location "
"

Manufacture " "
Software

Drivers " "

Firmware

Version " "

 

[Bob Phillips]

Greg,

It is indeed possible to add controls tom a userform on the fly.Here is an
example

Private Sub CommandButton1_Click()
Dim newButton As MSForms.Control
Select Case True
Case chkText.Value
Set newButton = Me.Controls.Add("Forms.Textbox.1")
newButton.Name = "New Textbox"
Case chkButton.Value
Set newButton = Me.Controls.Add("Forms.CommandButton.1")
newButton.Caption = "newCmd"
Case chkCheckbox.Value
Set newButton = Me.Controls.Add("Forms.Checkbox.1")
newButton.Caption = "Another Checkbox"
End Select

With newButton
.Left = 100
.Top = 50
.Visible = True
End With

End Sub

You can even create code to handle these new controls.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Greg Wilson]

Sorry Bob, my memory didn't serve me.

I recently finished writing a very challenging and
frustrating UF that completely transforms itself to
reflect the contents of a data base. I abandoned the
attempt to create it on the fly because of complications.
The whole affair became a huge head banger and there's
been a lot of water under the bridge since. I did
ultimately succeed by adding all possible controls at
design time and adapting their Visible, Top and Left
properties to suit at run time.

I found that, for a UF created on the fly, you couldn't
connect with a Class Module. I also found that you
couldn't "Remove" a control at run time that was added at
design time. (I'm really confused if you correct me on
this ). I thought I recollected that you couldn't add one
either. Mental exhaustion mixed with stupidity.

Much appreciate the pointer.

Regards,
Greg Wilson
(VBA amateur)

 

[Bob Phillips]

Hi Greg,

Sorry Bob, my memory didn't serve me.

I did
ultimately succeed by adding all possible controls at
design time and adapting their Visible, Top and Left
properties to suit at run time.

Even though it is possible to create forms and controls on the fly, this is
my preferred way of doing it as well.

I found that, for a UF created on the fly, you couldn't
connect with a Class Module.

This is the second time I have read this recently. What exactly do you mean
by it?

Regards

Bob

 

[Greg Wilson]

It turns out I was wrong on both counts. As you can tell,
I'm easily confused.

In preparing to reply to your post I wrote the code to
create a simple UF with 5 command buttons. Not that you
need to see it but the code follows. According to my
claim that a UF created on the fly can't connect with a
Class Module the following code won't work. However,
obviously it does. I'm at a complete loss at this point
as to what was wrong when I attempted this in the project
I described. Some error that wasn't being flagged, I
guess, was preventing it. The code is now long gone. I
spent hours in the attempt!

This situation perfectly epitomizes why I post here.
There is virtually no one else in my life to compare notes
with. My erroneous conclusion would otherwise have
handicapped me for who knows how long? I really
appreciate you gurus for your tireless support of the
Excel/VBA community.

Don't panic, I'm not threatenng to become a 'real'
programmer. Just trying to add to my skill set.

Regards,
Greg



'Class Module code:-
Public WithEvents ButtonGroup As CommandButton

Private Sub ButtonGroup_Click()
MsgBox "Clicked"
End Sub

'On-the-fly UF code:-
Sub MakeUF()
Dim UF As Object, Ctrl As CommandButton
Dim i As Integer, CM As Object
Dim Line As Integer, Code As String

Set UF = Application.VBE.ActiveVBProject.VBComponents.Add
(3)
UF.Properties("Height") = 200
UF.Properties("Width") = 150
For i = 1 To 5
Set Ctrl = UF.Designer.Controls.Add
("Forms.CommandButton.1")
With Ctrl
.Height = 18
.Width = 50
.Left = 10
.Top = i * 25
.Caption = "Click me"
End With
Next
Set CM = UF.CodeModule
Line = CM.CountOfLines
Code = "Dim Buttons(1 To 5) As New Class1"
Code = Code & vbCr & "Dim i As Integer"
Code = Code & vbCr & "Private Sub Userform_Initialize()"
Code = Code & vbCr & "For i = 1 To 5"
Code = Code & vbCr & "Set Buttons(i).ButtonGroup = Controls
(""CommandButton"" & i)"
Code = Code & vbCr & "Next"
Code = Code & vbCr & "End Sub"
CM.InsertLines Line, Code
VBA.UserForms.Add(UF.Name).Show
ThisWorkbook.VBProject.VBComponents.Remove UF
End Sub

 

[Bob Phillips]

We're all learning Greg. There was a post lat yesterday/early today on how
to force a UDF to keep one's preferred case. Not a big deal, but nice to
know that we can, and how.

Keep working at it.

Regards

Bob