mercredi 15 juillet 2015

How to loop through checkboxes on dynamically created userform?

I'm trying to utilize a dynamically created userform and based on what boxes are check, gray out certain cells.

As background, this is for an injection molding facility. QA sets up the cavity numbers that are running. This dynamic userform creates checkboxes based on the cavity numbers that are input on the worksheet.

Option Explicit

Private Sub UserForm_Initialize()

Dim col         As Long
Dim row         As Long
Dim lcol        As Long
Dim i           As Long
Dim j           As Long
Dim chkBox      As MsForms.CheckBox
Dim l           As MsForms.Frame
Dim t           As MsForms.Label

Set l = Me.Controls.Add("Forms.Frame.1", "cavz", True)
    l.Caption = "BLOCKED CAVITIES"
    l.Height = 195
Set t = l.Controls.Add("Forms.Label.1", "mark")
    t.Caption = "Mark all cavities that are currently blocked:"
    t.Width = 175
    t.Top = 10

col = 2 'Set your column index here
row = 8
lcol = 17
j = 1

For i = col To lcol
Set chkBox = l.Controls.Add("Forms.CheckBox.1", "CheckBox_" & i)
If Worksheets("QA").Cells(row, i).Value <> "" Then
    chkBox.Caption = Worksheets("QA").Cells(row, i).Value
ElseIf Worksheets("QA").Cells(row, i).Value = "" Then
    GoTo 10
End If
If i <= 9 Then
    'MsgBox "i = " & i
    chkBox.Left = 5
    chkBox.Top = 5 + ((i - 1) * 20)
ElseIf i > 9 Then
    j = j + 1
    'MsgBox "j = " & j
    chkBox.Left = 100
    chkBox.Top = 5 + ((j - 1) * 20)
End If
10
Next i

End Sub

Private Sub CommandButton2_Click()
Unload Me
End Sub

Private Sub CommandButton1_Click()

Dim x As Control
Dim cavz As MsForms.Frame

For Each x In cavz.Controls
If x.Value = True Then
    If x.Value = Range("B8") Then
        Range("B8:B14").Select
        Selection.Interior.ColorIndex = 16
    End If
End If
Next x

End Sub

I started out doing one thing, and somehow this is what my code turned into. It's not the neatest code, but I would still consider myself a novice at this. Plus, this is the first time I couldn't find the answer on my own and thus my first time asking for help. So, any help would be greatly appreciated!

Thanks, G




Aucun commentaire:

Enregistrer un commentaire