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)
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:
Schedule KEM WORKING.xlsm | |||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
L | M | N | O | P | Q | R | S | T | U | V | W | X | Y | Z | AA | AB | |||
6 | Foreman Information | Trainings | |||||||||||||||||
7 | Employee | Trainings | Phone # | Competent | OSHA 30 | OSHA 10 | CPR | Hand Signal | Rigging | Asbestos | Certa Torch | Scaffold | Fork/Lull | Manlift | ATV | ||||
8 | CP | 30hr | 10hr | CPR | HS | R | A | CT | ST | FL | ML | ATV | |||||||
9 | Javier Rodriguez | CP, 30hr, CPR, HS, R, CT | x | x | x | x | x | x | |||||||||||
10 | Pedro Rodriguez | 30hr, HS, R, CT, ST | x | x | x | x | x | ||||||||||||
11 | Santos Toribio | CP, 30hr, CPR, HS, R, CT | x | x | x | x | x | x | |||||||||||
12 | Jose Portillo | CP, HS, R, CT | x | x | x | x | |||||||||||||
13 | Bob Faulkner | CP, 30hr, HS, R, CT | x | x | x | x | x | ||||||||||||
14 | Marco Barajas | CP, 30hr, R, CT | x | x | x | x | |||||||||||||
15 | James Fredericks | 30hr, CPR | x | x | |||||||||||||||
16 | |||||||||||||||||||
Schedule |
Cell Formulas | ||
---|---|---|
Range | Formula | |
N9:N15 | N9 | =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