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