User Form and Pushing Inputs to Appropriate Cells

K

keef2

Board Regular
Joined
Jun 30, 2022
Messages
185
Office Version
  1. 365
Platform
  1. Windows
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:
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:
Schedule KEM WORKING.xlsm
LMNOPQRSTUVWXYZAAAB
6Foreman InformationTrainings
7EmployeeTrainingsPhone #CompetentOSHA 30OSHA 10CPRHand SignalRiggingAsbestosCerta TorchScaffoldFork/LullManliftATV
8CP30hr10hrCPRHSRACTSTFLMLATV
9Javier Rodriguez CP, 30hr, CPR, HS, R, CTxxxxxx
10Pedro Rodriguez 30hr, HS, R, CT, STxxxxx
11Santos Toribio CP, 30hr, CPR, HS, R, CTxxxxxx
12Jose PortilloCP, HS, R, CTxxxx
13Bob FaulknerCP, 30hr, HS, R, CTxxxxx
14Marco BarajasCP, 30hr, R, CTxxxx
15James Fredericks30hr, CPRxx
16
Schedule
Cell Formulas
RangeFormula
N9:N15N9=IFERROR(TEXTJOIN(", ",,FILTER($Q$8:$AA$8,Q9:AA9="x")),"")


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)

VBA Code: 
Private Sub UserForm_Initialize()
   'force userform to center of screen
   Me.Top = Application.Top + (Application.UsableHeight / 2) - (Me.Height / 2)
   Me.Left = Application.Left + (Application.UsableWidth / 2) - (Me.Width / 2)
   
   'activate textbox
   Me.EmpName.SetFocus
   
End Sub

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
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
Sort by date Sort by votes
Ok update here. I now have the form working properly to add Employee name and phone # based on inputs. Now i just need to figure out that if a box is checked under training that "x" is populated in the appropriate columns. Any help is always appreciated!

Here is the updated code where i am stalled out on the check box question:
VBA Code: 
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.Value
    
    Unload Me

End Sub
 
Upvote 0
Update. I got this to work with the following code:

VBA Code: 
Private Sub Submit_Click()

    Dim act As Worksheet
    Set act = ThisWorkbook.ActiveSheet
    'Dim i As Long, j As Long
    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.Value
    
   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 = "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
 
Upvote 0
Solution
Ok so one more question my code above works but I currently added 2 more check box's "Chicago Resident" & "Apprentice". I am hoping to conditional format the data if 1 or both of these box's are checked. For example if Chicago Resident is checked then i want the text in column L to be Red. If Apprentice is checked I want the entire range "L & bot_row & ":AB" & bot_row to fill Yellow.

Here is my current attempts which neither worked. Any help is always appreciated!!! thanks and happy Friday!!!

1ST Attempt:
VBA Code: 
If EmployeeInformation.ChiResident.Value = True Then
    Range("L" & bot_row).FormatConditions(1).Font = vbRed
    End If
    If EmployeeInformation.Apprentice.Value = True Then
    Range("L" & bot_row & ":AB" & bot_row).FormatConditions(1).Interior = vbYellow
    End If

2ND Attempt (see Last 2 lines):
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 = "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"
            ElseIf cBox.Caption = "Chicago Resident" Then
            Range("L" & bot_row).FormatConditions(1).Font.Color = vbRed
            ElseIf cBox.Caption = "Apprentice" Then
            Range("L" & bot_row).FormatConditions(1).Interior.Color = vbYellow
            End If
         End If
      End If
    Next
 
Upvote 0
Update: I got this to work for each individual condition with the following code. However, if i select both conditions for some reason column L doesn't highlight yellow everything else works great. Any suggestions?

VBA Code: 
Dim Cond1 As FormatCondition, cond2 As FormatCondition
    Set Cond1 = Range("L" & bot_row).FormatConditions.Add(xlExpression, xlEqual, EmployeeInformation.ChiResident.Value = True)
    Set cond2 = Range("L" & bot_row & ":AB" & bot_row).FormatConditions.Add(xlExpression, x1equal, EmployeeInformation.Apprentice.Value = True)
    With Cond1
    .Font.Color = vbRed
    .Font.Bold = True
    End With
    With cond2
    .Interior.Color = vbYellow
    End With
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Solved
VBA Insert Row & copy formulas
Replies
2
Views
589
keef2
K
K
  • Solved
Join text together if condition is met
Replies
3
Views
342
Fluff
Fluff
K
  • Solved
VBA to copy range to bottom of existing table
2
Replies
10
Views
622
Joe4
Joe4
R
  • Solved
How to use variables from a user form in a macro
Replies
5
Views
855
dmt32
D
A
  • Question
Page break issues when exporting to pdf from userform (VBA)
Replies
0
Views
720
auglocqnuk
A

Forum statistics

Threads
1,215,537
Messages
6,125,393
Members
449,222
Latest member
taner zz

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top