mardi 7 juillet 2020

VBA Userform Checkbox Tracking

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.enter image description here

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