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