CODEWITHSUNDEEP
excelvbacheckbox
Will89
Will89

Reputation: 3

Setting Up Mulitple CheckBoxes on Userform Intialization

New here (first post), and pretty new to vba excel also and I have run in to a stumbling block which I'm struggling to solve. If I can find help here, then I'd be very appreciative!

I have a userform which contains 90 checkboxes. On form initialization I want to set all of the checkboxes to either TRUE or FALSE depending on a value contained within a cell on a specific sheet, which I can do, I just now have to do it for a further 89 checkboxes which, in its current format is going to be unwieldy.

I'm looking for suggestions as to how this could be achieved in a more concise way.

Thanks!

Private Sub UserForm_Initialize()

    Dim chRange As Range
    Dim ws As Worksheet
    Dim PDI_No As String
    Dim Row As Integer
    Dim rfind1 As Range
    Dim Col_Ltr1 As String

    Set ws = Worksheets("Master_Log")
    Set chRange = ws.Range("A:A")
    PDI_No = UpdateRecord.ComboBox1.Value
    Row = Application.WorksheetFunction.Match(PDI_No, chRange, 0) 'finds row number
    With Worksheets("Master_Log").Range("A1:DZ1")
        Set rfind1 = .find(What:=CheckBox1.Caption, LookAt:=xlWhole, MatchCase:=False, 
                      SearchFormat:=False) 'finds column number
       Col_Ltr1 = Split((Columns(rfind1.Column).Address(, 0)), ":")(0) 'changes column number to a 
                  letter
       cellposition1 = Col_Ltr1 & Row 'range value

            If ws.Range(cellposition1) = CheckBox1.Caption Then
                CheckBox1.Value = True
                    Else
                CheckBox1.Value = False
            End If
        End With
End Sub

Upvotes: 0

Views: 115

Answers (3)

FunThomas
FunThomas

Reputation: 29592

I don't like the idea to trust that all 90 checkboxes are named with consecutive numbers. The following loop will loop over all controls of the form and check the type.

Dim ctrl As Control
For Each ctrl In Me.Controls
    if TypeName(ctrl) = "CheckBox" then
        ' rest of the logic "stolen" from Ted Willams answer:
        theCol = Application.Match(ctrl.Caption, ws.Range("A1:DZ1"), 0)
        If Not IsError(theCol) Then
            ctrl.Value = (ws.Cells(theRow, theCol).Value = ctrl.Caption)
        Else
            'no column match
        End If
    End If
Next ctrl

Upvotes: 1

jamheadart
jamheadart

Reputation: 5343

You can cycle through all controls on your userform, looking for checkboxes in particular like so:

Private Sub CycleThroughCheckboxes
Dim x As Control
Dim r As Long
Dim result as Boolean
For Each x In UserForm1.Controls
    If TypeName(x) = "CheckBox" Then
        x.Value = true ' or false - Do your find logic here using x.Caption
    End If
Next x
End Sub

Benefit of doing it this way is if you add / remove some checkboxes, you don't need to keep count and change the code later on.

Upvotes: 2

Tim Williams
Tim Williams

Reputation: 166790

You can do something like this (assuming your checkboxes all have a consistent name):

Private Sub UserForm_Initialize()

    Dim ws As Worksheet
    Dim PDI_No As String
    Dim theRow, theCol, i As Long, cb As Object

    Set ws = Worksheets("Master_Log")
    PDI_No = UpdateRecord.ComboBox1.Value
    theRow = Application.Match(PDI_No, ws.Range("A:A"), 0) 'finds row number

    If Not IsError(theRow) Then
        For i = 1 To 90
            Set cb = Me.Controls("CheckBox" & i) '<<<<<
            theCol = Application.Match(cb.Caption, ws.Range("A1:DZ1"), 0)
            If Not IsError(theCol) Then
                cb.Value = (ws.Cells(theRow, theCol).Value = cb.Caption)
            Else
                'no column match
            End If
        Next i
    Else
        'no row match...
    End If

End Sub

Upvotes: 2

Related Questions