Solved: Adding Rows in Userform

[coliervile]

I have a Userform thats used when calling in over-time. I want to expand the user form so that a total of 8 employees can be on the form. When the form is submitted it goes into an excel database and then is used in a pivot table. When the userform is used there may only be 4 employees on the form. When the data is moved to the database it will resemble this:

A B C D
EMPLOYEE OFFER DATE HOURS row1
NAME1 Refused 6/26/2006 8 row2
NAME2 No Answer 6/26/2006 8 row3
NAME3 Med Disq 6/26/2006 8 row4
NAME4 Accepted 6/26/2006 8 row5

What I can't figure out is how to program the vba to tranfer the userform data into the rows as I described them above. Here's what I currently have and I know this doesn't work:

"copy the data to the database
With ws
.Cells(lRow, 1).Value = Me.cboEmployee.Value
.Cells(lRow, 2).Value = Me.cboOffer.Value
.Cells(lRow, 3).Value = Me.txtDate.Value
.Cells(lRow, 4).Value = Me.txtHours.Value
End With

Any help would be greatly appreciated

[acw]

Hi

Can you update your file to include the userform and any code you have?


Tony

[coliervile]

Thanks for your help. Here's what I've got so far.

[mdmackillop]

I'm not seeing NameList as a named range, and I can't see where cLoc or cPart are having values assigned. There may be other items, but that's as far as I've looked.

[coliervile]

Here's the correct data file. What I want is a userform the will allow me to insert upto 8 employees overtime:

employee1- date1- hours1- offer1
employee2- date2- hours2- offer2
employee3- date3- hours3- offer3
employee4- date4- hours4- offer4
employee5- date5- hours5- offer5
and so on....

I also need to add all of the above information to the data base at one time when submitted. I'm not certain on how to write the VBA to add rows in this manner??????

I appreciate your help.

[acw]

Hi

So you want up to 8 entries available on the one form at the same time rather than using the existing form and doing 8 separate entries?


Tony

[coliervile]

"acw" my thoughts were to make it easier for the person making the calls for overtime to make all of the entries at one-time rather than going back upto maybe 8 times. Your thoughts would be appreciated.

Charlie

[acw]

Charlie

Don't have a problem with that approach. Can you build the form with the required entries. If you make your names for each of the options with the same form, then you can build a loop to go through them all easily. SO all the name boxes would be cboName1, cboName2....

Loading the entries is pretty much the same as the approach you have used. Just put them into a loop, with the output row initialized for each iteration. Only paste if there is an entry in the name box.

The testing can be performed for the entries that should relate to the name. So if cboName3 has an entry, then txtDate3, txtHours3 and cboOffer3 all require entries before you proceed.


Tony

[coliervile]

Tony thanks for your input and suggestions. I'll be changing the names on the user form and a couple of other small chages, but this is what I have come up with (password: QED). Any ideas would be appreciated.

Regards

Charlie

[lucas]

What if you need to edit something?
Also, why the pivot table sheet. Why not just compile all data and totals on one sheet "OvertimeDatabase"?

[coliervile]

I guess I could do that. The pivot table would just be used for quick reference to view the totals. Changes or edit can be done using the Data-Form control. Only certain people would have the password to make changes.

I'm open to any ideas. If you can show me your thoughts?

Regards
Charlie