Manipulate Controls added at runtime

[thomasdavisz]

The following code adds 5 spinbuttons next to 5 labels into a frame.
How can I make each spinbox control ( +1 and -1) the number displayed
in the label next to it?

For x = 0 To 5

Set LControl = Frame1.Controls.Add("Forms.label.1")
With LControl
..Caption = 100
..Top = 10 + 30 * x
..Left = 100
..Height = 11
..Width = 15
End With

Set SBControl = Frame1.Controls.Add("Forms.spinbutton.1")
With SBControl
..Top = 6 + 30 * x
..Left = 115
..Height = 18
..Width = 12
End With
Next


Thanx,

Thomas

 

[Peter T]

Hi Thomas,

Add a class module named Class1
' code in Class1

Public WithEvents spin As MSForms.SpinButton
Dim lbl As MSForms.Label
Dim id As Long

Public Property Set prop(lab As MSForms.Label)
Set lbl = lab
End Property
Public Property Let propped(n As Long)
id = n
End Property

Private Sub spin_Change()
lbl.Caption = spin.Value
Select Case id
Case 0
'process whatever
Case 1
Case 2
'etc to Case 5
End Select

' if in a frame grandparent to ref the form
spin.Parent.Parent.Caption = id & " " & spin.Value
End Sub


' userform code
' assumes already contains a "tall" frame named Frame1

Dim clsScrolls(0 To 5) As New Class1

Private Sub UserForm_Initialize()
For x = 0 To 5

Set lcontrol = Frame1.Controls.Add("Forms.label.1")
With lcontrol
..Caption = 100
..Top = 10 + 30 * x
..Left = 100
..Height = 11
..Width = 15
End With

Set sbcontrol = Frame1.Controls.Add("Forms.spinbutton.1")
With sbcontrol
..Top = 6 + 30 * x
..Left = 115
..Height = 18
..Width = 12
..Value = 100
..Max = 200
..Min = -100
..SmallChange = 1 ' default
End With
Set clsScrolls(x).spin = sbcontrol
Set clsScrolls(x).propLab = lcontrol
clsScrolls(x).propID = x

Next
End Sub

Regards,
Peter T

PS

The following code adds 5 spinbuttons next to 5 labels

0 to 5 = 6

 

[thomasdavisz]

Hey Peter, thanx a lot.

Just in case someone else wants to use this code, i had to modify the
end of your code replaced by:

Set clsScrolls(x).spin = sbcontrol
Set clsScrolls(x).prop = lcontrol
clsScrolls(x).propped = x

Once again, thank you.

Thomas

 

[Peter T]

That was my speel chekcer messing things up!

Before posting it read, in the class -

Public Property Set propLab(lab As MSForms.Label)

Public Property Let propID(n As Long)

Glad you got it working

Regards,
Peter T

 

[thomasdavisz]

Peter,

I have two additional questions:

1) About "Dim clsScrolls(0 To 5) As New Class1" ... what if the
number 5 wasn't a constant, it may vary

2) I have 6 labels with 6 spinbuttons, if I need a 7 th label (also
created at runtime) displaying the sum of the 6 labels

Hope you can help me with this,

Thomas

 

[Peter T]

First, for others trying this, ensure Frame1 size is at least w150 x h200.

Might be more sensible to rename clsScrolls > clsSpins (I originally added
scrollbars before re-reading the question).

I have two additional questions:

1) About "Dim clsScrolls(0 To 5) As New Class1" ... what if the
number 5 wasn't a constant, it may vary

You can Redim & ReDim Preserve the array just as you would any other array,
eg

Dim clsSpins() As New Class1 '

' in the inti event
Dim cntSpins as long
cntSpins = 6

Redim Preserve clsSpins(0 to 6) 'in reality never need to preserve in the
init event

for x = 0 to cntSpins

In some other routine to add a new class to the array Redim Preserve again.

However if you have an unknown or varying number might be easier to use a
Collection (particularly if you are going to delete controls during
runtime - after deleting simply Remove from the collection)

'top of the form module
Dim colSpins As New Collection

In the init routine comment or delete everything relating to the array

Dim clsSpin As Class1

'loop & code to add controls to the frame, in the loop

Set clsSpin = New Class1
Set clsSpin.spin = sbcontrol
Set clsSpin.propLab = lcontrol
clsSpin.propID = x
colSpins.Add clsSpin, CStr(x)

adding the key "x" is optional, might be usful if need to refer to specific
objects (ie class & control) in the collection elsewhere. See collection's
in help

2) I have 6 labels with 6 spinbuttons, if I need a 7 th label (also
created at runtime) displaying the sum of the 6 labels

Add a label named Label1 to Frame1, for testing add at design but change
later to add at runtime (you would need to re-define it's name after adding)

in the form module

Public Sub SpnTotal()
Dim cnt As Long

'' array method
'Dim x As Long
'For x = Lbound(clsSpins) To UBound(clsSpins)
'cnt = clsScrolls(x).spin.Value + cnt
'Next

'collection method
Dim cls As Class1
For Each cls In colSpins
cnt = cls.spin.Value + cnt
Next

Me.Frame1.Label1.Caption = cnt
End Sub

'in the spin_Change event in Class1
UserForm1.SpnTotal

Regards,
Peter T