Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Manipulate Controls added at runtime
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Manipulate Controls added at runtime
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 wrote in message oups.com... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Manipulate Controls added at runtime
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Manipulate Controls added at runtime
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 wrote in message oups.com... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Manipulate Controls added at runtime
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Manipulate Controls added at runtime
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to manipulate controls added at run-time | Excel Worksheet Functions | |||
MatchEntry for combobox added during runtime | Excel Programming | |||
add event to controls added in runtime | Excel Programming | |||
Event procedures for controls added with Controls.Add | Excel Programming | |||
creating controls at runtime | Excel Programming |