VBA for Userform - Nested-loops in OK_Click()

akpc1234

New Member
Joined
Dec 6, 2013
Messages
1
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>
 
Last edited:

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

pbornemeier

Well-known Member
Joined
May 24, 2005
Messages
3,897
I think this will do as you asked. Be sure to identify the classname row (iHeaderRow)

Code:
Private Sub OK_Click()

    Dim ctl As Control
    Dim count As Integer
    Dim i, j, k As Integer
    Dim CB As String, CoB As String, LB As String
    Dim classname() As String
    Dim strLabels
    Dim intCBOCount As Integer
    Dim iColumnCount As Integer
    Dim sHeaders() As String
    
    Const iHeaderRow As Integer = 1
    
    Application.DisplayAlerts = False
    
    Rows(iHeaderRow).Cells.Clear
    
    For Each ctl In Me.Controls
    
        If TypeOf ctl Is MSForms.CheckBox Then
            If Me.Controls(ctl.Name).Value = True Then
            
                CB = "CheckBox" & Mid(ctl.Name, 9)
                
                CoB = "ComboBox" & Mid(ctl.Name, 9)
                
                LB = "Label" & 1 + CInt(Mid(ctl.Name, 9))
                
                If IsNumeric(Me.Controls(CoB).Value) Then
                    intCBOCount = CInt(Me.Controls(CoB).Value)
                    For j = 1 To intCBOCount
                        iColumnCount = iColumnCount + 1
                        ReDim Preserve sHeaders(1 To iColumnCount)
                        sHeaders(iColumnCount) = Me.Controls(LB).Caption
                    Next
                End If
            
            End If
        End If
        
    Next
    
    If iColumnCount > 0 Then
        For k = LBound(sHeaders) To UBound(sHeaders)
            Cells(iHeaderRow, k) = sHeaders(k)
        Next
    Else
        MsgBox "No data to show"
    End If
    
    Me.Hide
End Sub
 

Jon Peltier

MrExcel MVP
Joined
May 14, 2003
Messages
5,254
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
Note to both of you (PMFJI):

Dim i, j, k As Integer

is not declaring all three variables as integers. i and j are variants. You need:

Dim i As Integer, j As Integer, k As Integer

Also, you should forget there are Integers and use Longs instead. VBA converts Integers to Longs, does the math, then converts back, so in this case, Integers are slower than Longs. You wouldn't notice the speed difference, but it makes sense to use a more-broadly-defined variable if it doesn't cost anything. The same goes for Singles and Doubles.
 

pbornemeier

Well-known Member
Joined
May 24, 2005
Messages
3,897
Thanks for the info I did not realize that the types are converted. I generally use longs because of the number of rows available, but did not want to do a full makeover of OP code.
I didn't notice the Dim problem (but that has always been one of the things I thought the VB creators did wrong)

New and improved version:
Code:
Option Explicit

Private Sub OK_Click()

    Dim ctl As Control
    Dim lX As Long
    Dim sChkbox As String, sComboBox As String, sLabel As String
    Dim lCBOValue As Long
    Dim lColumnCount As Long
    Dim sClassName() As String
    
    Const lHeaderRow As Long = 1  'The row that should contain the class names
    
    Rows(lHeaderRow).Cells.Clear
    
    For Each ctl In Me.Controls
        If TypeOf ctl Is MSForms.CheckBox Then
            sChkbox = "CheckBox" & Mid(ctl.Name, 9)
            sComboBox = "ComboBox" & Mid(ctl.Name, 9)
            sLabel = "Label" & 1 + CInt(Mid(ctl.Name, 9))
            If Me.Controls(sChkbox).Value = True Then                   'Checkbox is checked
                If Me.Controls(sComboBox).Value <> vbNullString Then    'Associated ComboBox holds something
                    If IsNumeric(Me.Controls(sComboBox).Value) Then     'Associated ComboBox holds a number
                        lCBOValue = CInt(Me.Controls(sComboBox).Value)  'Make sure number is an integer
                        If lCBOValue > 0 Then                           'Make sure number is positive
                            For lX = 1 To lCBOValue
                                lColumnCount = lColumnCount + 1
                                ReDim Preserve sClassName(1 To lColumnCount)
                                sClassName(lColumnCount) = Me.Controls(sLabel).Caption
                            Next
                        End If
                    End If
                End If
            End If
        End If
    Next
    
    If lColumnCount <= Worksheets(1).Columns.count Then
        If lColumnCount > 0 Then
            For lX = LBound(sClassName) To UBound(sClassName)
                Cells(lHeaderRow, lX) = sClassName(lX)
            Next
        Else
            MsgBox "No data to show"
        End If
    Else
        MsgBox "Too many column headers created (" & lColumnCount & ")"
    End If
    
    Me.Hide
End Sub
 

Forum statistics

Threads
1,171,798
Messages
5,877,584
Members
433,265
Latest member
Umaratnam

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top