User Form & Transferring Data to Worksheet-CodePudding

Disclaimer this is my first time with user forms. Hoping I can get some guidance. Thank you in advance!

I currently have the user form designed and it has an input for employee name as well as a bunch of checkbox's for trainings.

Here is the user form:

I am looking for a user to input an employee name and that value gets populated in L which is merged with M. Then for any checkbox's that are checked I would like to return "x" into the appropriate columns for those trainings.

Here is the current section that I am referring too. Please note that there is another data set 1 row below this one with same headings but for Journeymen not Foreman (this is why my code below refers to AZ2 which captures last row of the upper data set and I plan to do the same for the bottom data set).

Data Set Example:

Lastly: here is my current code which is incomplete and not working even for just the employee name... (Sorry still confused on how to properly upload code)

`Private Sub Submit_Click()
Set act = ThisWorkbook.ActiveSheet
bot_row = act.Range("AZ2")
act.Range("L" & bot_row & ":AB" & bot_row).Insert Shift:=xlShiftDown
act.Range("L" & bot_row & ":M" & bot_row).Value = EmpNameTextBox.Text
End Sub`

EDIT: WORKING CODE is here:.

Private Sub Submit_Click()
Dim act As Worksheet
Set act = ThisWorkbook.ActiveSheet
bot_row = act.Range("AZ2")

act.Range("L" & bot_row & ":AB" & bot_row).Insert Shift:=xlShiftDown
act.Range("L9:AB9").Copy
act.Range("L" & bot_row & ":AB" & bot_row).PasteSpecial xlPasteFormats
act.Range("L" & bot_row & ":AB" & bot_row).PasteSpecial xlPasteFormulas
Range("P" & bot_row & ":AB" & bot_row).ClearContents
Range("L" & bot_row) = EmpName.Value
Range("P" & bot_row) = EmpPhone.ValueDim cBox As Control
For Each cBox In Me.Controls
  If TypeOf cBox Is msforms.CheckBox Then
     'potential test msgbox
     'MsgBox "Box " & cBox.Caption & " has a click value = " & cBox.Value
        If cBox.Value Then
        If cBox.Caption = "Competent" Then
            Range("Q" & bot_row).Value = "x"
        ElseIf cBox.Caption = "OSHA 30hr" Then
            Range("R" & bot_row).Value = "x"
        ElseIf cBox.Caption = "OSHA 10hr" Then
            Range("S" & bot_row).Value = "x"
        ElseIf cBox.Caption = "CPR" Then
           Range("T" & bot_row).Value = "x"
        ElseIf cBox.Caption = "Hand Signal" Then
           Range("U" & bot_row).Value = "x"
        ElseIf cBox.Caption = "Rigging" Then
           Range("V" & bot_row).Value = "x"
        ElseIf cBox.Caption = "Asbestos" Then
           Range("W" & bot_row).Value = "x"
        ElseIf cBox.Caption = "Certa Torch" Then
           Range("X" & bot_row).Value = "x"
        ElseIf cBox.Caption = "Scaffold" Then
           Range("Y" & bot_row).Value = "x"
        ElseIf cBox.Caption = "Fork/Lull" Then
           Range("Z" & bot_row).Value = "x"
        ElseIf cBox.Caption = "Manlift" Then
           Range("AA" & bot_row).Value = "x"
        ElseIf cBox.Caption = "ATV" Then
           Range("AB" & bot_row).Value = "x"
        End If
     End If
       
  End If
Next

Unload Me
End Sub

CodePudding user response：

This would probably work. I would recommend you include this in your code at end. I would also suggest using something more dynamic with columns. Naming them would be easy and ensures you could make easy updates without too much modification of VBA code.

 Dim cBox As Control
    For Each cBox In Me.Controls
      If TypeOf cBox Is msforms.CheckBox Then
         'potential test msgbox
         'MsgBox "Box " & cBox.Caption & " has a click value = " & cBox.Value
         If cBox.Value Then
            If cBox.Caption = "CPR" Then
               Range("T" & bot_row).Value = "X"
            ElseIf cBox.Caption = "Hand Signal" Then
               Range("U" & bot_row).Value = "X"
      
            'etc.
            End If
         End If
           
      End If
    Next