I'm trying to create a VBA code to checkmark specific lube routes based on field data. My code works to generate a dynamic userform with the equipment number & part name as a checkbox.
My question is how do I then code the enter command button code to insert an "X" in the Done column?
My current code is below. The image of the sheet is also attached.
Option Explicit
Private Sub CommandButton1_Click()
Dim curColumn As Long
Dim LastRow As Long
Dim i As Long
Dim chkBox As MSForms.CheckBox
Dim ctrl As Control
curColumn = 1 'Set your column index here
LastRow = Worksheets("Monday").Cells(Rows.Count, curColumn).End(xlUp).Row
For Each ctrl In Me.Controls
If TypeName(ctrl) = "Checkbox" Then
If ctrl.Value = True Then
Worksheets("Monday").Cells(i, curColumn + 4).Value = "X"
End If
End If
i = i + 1
Next ctrl
Unload Me
End Sub
Private Sub CommandButton2_Click()
Unload Me
End Sub
Private Sub UserForm_Initialize()
Dim curColumn As Long
Dim LastRow As Long
Dim i As Long
Dim chkBox As MSForms.CheckBox
curColumn = 1 'Set your column index here
LastRow = Worksheets("Monday").Cells(Rows.Count, curColumn).End(xlUp).Row
For i = 2 To LastRow
Set chkBox = Me.Controls.Add("Forms.CheckBox.1", "CheckBox_" & i)
chkBox.Caption = Worksheets("Monday").Cells(i, curColumn).Value & " " &
Worksheets("Monday").Cells(i, curColumn + 2).Value
chkBox.Left = 5
chkBox.Top = 5 + ((i - 1) * 20)
Next i
End Sub
Aucun commentaire:
Enregistrer un commentaire