At the moment, I am trying to extract some information from the userform and put that in the spreadsheet.
Basically, I have 15 checkboxs in the userform. For each checkboxs(1,2,..., 15), it has a label and a combo box next to it.
What I want to achieve is, if the checkbox is true, then it will copy the name of the label to one row of my spreadsheet, running from left to right. But the number of times of that name to be appeared on the spreadsheet is also depending on the value of the combo box.
<code>For example:
Checkbox1.value = true
Label2.caption = "A"
ComboBox1.value = 2</code>
Running the macros, I should have two A's on cells(1,1) and cells(1,2) in the spreadsheet. Since I don't want any spacing bewtween the columns of the spreadsheet, I have used an array variable to store all the label names. Of course, if the checkbox1.value = false, it should jump to the next checkbox2 without storing any memory. Somehow, when I check the code with debug.print, they are all showing the same label name for all my array.I would appreciate if anyone could assist. Thanks
<code>Here is the code I wrote:
Private Sub OK_Click()
Dim ctl As Control
Dim count As Integer
Dim i, j, k As Integer
Application.DisplayAlerts = False
For Each ctl In Me.Controls
If TypeOf ctl Is MSForms.CheckBox Then If
Me.Controls(ctl.Name).Value = True
Then count = count + 1
End If
End If
Next k = count - 1
'MsgBox (count)
'----------------------------------------------------------------------
ReDim classname(k) As String
For i = 1 To 15
For j = 0 To k
CB = "CheckBox" & i
CoB = "ComboBox" & i
LB = "Label" & 1 + i
If Me.Controls(CB).object.Value = True Then
classname(j) = Me.Controls(LB).object.Caption
End If
Next j
Next i
'Debug.Print (classname(0))
'Debug.Print (classname(1))
'Debug.Print (classname(4))
End Sub</code>
Basically, I have 15 checkboxs in the userform. For each checkboxs(1,2,..., 15), it has a label and a combo box next to it.
What I want to achieve is, if the checkbox is true, then it will copy the name of the label to one row of my spreadsheet, running from left to right. But the number of times of that name to be appeared on the spreadsheet is also depending on the value of the combo box.
<code>For example:
Checkbox1.value = true
Label2.caption = "A"
ComboBox1.value = 2</code>
Running the macros, I should have two A's on cells(1,1) and cells(1,2) in the spreadsheet. Since I don't want any spacing bewtween the columns of the spreadsheet, I have used an array variable to store all the label names. Of course, if the checkbox1.value = false, it should jump to the next checkbox2 without storing any memory. Somehow, when I check the code with debug.print, they are all showing the same label name for all my array.I would appreciate if anyone could assist. Thanks
<code>Here is the code I wrote:
Private Sub OK_Click()
Dim ctl As Control
Dim count As Integer
Dim i, j, k As Integer
Application.DisplayAlerts = False
For Each ctl In Me.Controls
If TypeOf ctl Is MSForms.CheckBox Then If
Me.Controls(ctl.Name).Value = True
Then count = count + 1
End If
End If
Next k = count - 1
'MsgBox (count)
'----------------------------------------------------------------------
ReDim classname(k) As String
For i = 1 To 15
For j = 0 To k
CB = "CheckBox" & i
CoB = "ComboBox" & i
LB = "Label" & 1 + i
If Me.Controls(CB).object.Value = True Then
classname(j) = Me.Controls(LB).object.Caption
End If
Next j
Next i
'Debug.Print (classname(0))
'Debug.Print (classname(1))
'Debug.Print (classname(4))
End Sub</code>
Last edited: