Excel Concatenate Form Name to Parse for function

R

Rustybb

Joined
Mar 25, 2020
Messages
2
Reaction score
0
I am currently re-working my excel spreadsheet from normal data entry to utilize form entry so I can enforce more stringent validation on input

My current issue is as follows

My initial form utilizes 6 Combo-boxes for Employee name followed by 6 for start time, end time, and lunch break

the set up looks like:

Name Start Time End Time Lunch
CB_EMPName1 CB_Start1 CB_End1
CB_EMPName2 CB_Start2
CB_EMPName3
CB_EMPName4
CB_EMPName5
CB_EMPName6

........ you get the idea

i am now working on the "CLEAR" command button to clear all input and I'm looking for a way to loop the variable names opposed to inputting each individually
I have the references set utilizing :

For i = 3 To sh.Range("B" & Application.Rows.Count).End(xlUp).Row
Me.CB_EMPNAME1.AddItem sh.Range("B" & i).Value
Me.CB_EMPNAME2.AddItem sh.Range("B" & i).Value
Me.CB_EMPNAME3.AddItem sh.Range("B" & i).Value
Me.CB_EMPNAME4.AddItem sh.Range("B" & i).Value
Me.CB_EMPNAME5.AddItem sh.Range("B" & i).Value
Me.CB_EMPNAME6.AddItem sh.Range("B" & i).Value
Next i

but I'd like to figure out a way to utilize concatenation to parse each opposed to listing each individually.
 
I'm trying to avoid

Me.CB_EMPNAME1 = ""
Me.CB_EMPNAME2 = ""
Me.CB_EMPNAME3 = ""
Me.CB_EMPNAME4 = ""
Me.CB_EMPNAME5 = ""
Me.CB_EMPNAME6 = ""

for all variables
 
Back
Top