mercredi 8 avril 2020

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



Aucun commentaire:

Enregistrer un commentaire