Hi everyone,

I need to create an excel form which I will either email weekly or place on
our shared drive for weekly access, in which my co-workers can enter their
weekly sales numbers. The form should contain a button, which would
automatically send those numbers to my already created spreadsheet. Each
co-worker has their own worksheet within the workbook, so if for example, Bob
chooses his name from the dropdown list, his numbers will automatically be
sent to his specific worksheet. Is using VBA the only way to do this? I'm new
at this and having a lot of trouble with writing the code. I have the
userform created, I'm just having a lot of trouble getting beyond the basics.
Would data validation be useful for this? I haven't worked with that before
either.

Also, is there a way for a form in one workbook to send the information to a
spreadsheet set up in another workbook?

Thanks!

Is this possible to do. Yes. Is it a good way to do something like this no.
Excel works best when only one person is trying to access it at a time. It
sounds like what you are trying to do is not that intricate. Microsoft Access
is probably a better tool for what you want to accomplish. If you are not
familiar with Access then You can try the Excel route but you will have
possible problems with performance (one Excel sheet having to open and close
another sheet repeatedly has inherant overhead issues) and or concurency
problems (many users trying to access the same data all at once.)

"brackeva" wrote:

Hi everyone,

I need to create an excel form which I will either email weekly or place on
our shared drive for weekly access, in which my co-workers can enter their
weekly sales numbers. The form should contain a button, which would
automatically send those numbers to my already created spreadsheet. Each
co-worker has their own worksheet within the workbook, so if for example, Bob
chooses his name from the dropdown list, his numbers will automatically be
sent to his specific worksheet. Is using VBA the only way to do this? I'm new
at this and having a lot of trouble with writing the code. I have the
userform created, I'm just having a lot of trouble getting beyond the basics.
Would data validation be useful for this? I haven't worked with that before
either.

Also, is there a way for a form in one workbook to send the information to a
spreadsheet set up in another workbook?

Thanks!

MS claims that xl can be used as a "shared file" on a shared drive. BUT
there are problems with this approach.
If you don't have too many coworkers, each worker can have a copy of the
file, fill in the form weekly, click a control button on the form to "post"
the data to their own sheet, then click on EditMove or Copy Sheet
(selecting the Copy option).
This will create a new wbk with a single sheet, with that employees name on
the tab. Then with FileSend toas Attachment, they can email their own
sheets back to you. You then open your master wbk + the employee wbk, and
copy the desired data to your master and delete the employee wbk.

This sound like a lot of steps but it can be automated with vba (which I
have done)

You mentioned you are having difficulty getting the userform textbox data
back into the workers' sheets. You can use a command button (named
"PostButton" on the user form like the following:

The code goes in the Userform module:


Option Explicit

Sub PostButton_Click()
dim Emp as String
'ck for missing data
If Me.Textbox1.value = "" or Me.Textbox2.value = "" then
Msgbox "You must fill in the data",vbokonly,"Error"
Textbox1.SetFocus
Exit Sub
End if
'Assuming Textbox1 is populated with employee names
' which are also identical to sheet names (Tabs)
' and you want to post the data from Textbox 2 into
'range B3 on that employees wsh

Emp = Me.Textbox1
Sheets(Emp).Range("B3")=Me.Textbox2
Me.Unload
End Sub

Hope this helps get you started
Paul


"brackeva" wrote:

Hi everyone,

I need to create an excel form which I will either email weekly or place on
our shared drive for weekly access, in which my co-workers can enter their
weekly sales numbers. The form should contain a button, which would
automatically send those numbers to my already created spreadsheet. Each
co-worker has their own worksheet within the workbook, so if for example, Bob
chooses his name from the dropdown list, his numbers will automatically be
sent to his specific worksheet. Is using VBA the only way to do this? I'm new
at this and having a lot of trouble with writing the code. I have the
userform created, I'm just having a lot of trouble getting beyond the basics.
Would data validation be useful for this? I haven't worked with that before
either.

Also, is there a way for a form in one workbook to send the information to a
spreadsheet set up in another workbook?

Thanks!