Turn worksheet into "userform"

[coliervile]

Is it possible to take this excel worksheet and turn it into a "userform"? After an employee completes the form the worksheet is saved and the data on the worksheet is saved in a database on another worksheet to be used for other information (i.e. pivot table, individual employee leave totals etc.)? I would limit what data from the worksheet that would be placed in the database.

Please take a look and I really appreciate your help.

Best regards,

Charlie

[OBP]

Why not just put the data straight in to the database, or better still use Access for the database?
But to answer your question, yes of course you can use any Excel sheet as a data input form, but you will have the problem of controlling the "saving" of the worksheet as it will overwrite one that has already been saved unless you change the name on saving.
You can use VBA code to transfer the data from your "form" sheet in to your database sheet.
By the way I think you may have a spelling error in C10 C19, should it be accrued?

[coliervile]

Thanks for the information. I had more than one spelling error. I'm not that familiar with Access, I've done more things with Excel and feel more comfortable overall using Excel.

Would you recommend using a VBA "userform" or the SF-71 worksheet to dump the data into the database? If using a VBA "userform" how is the range set for the text or cbo boxes to fill out the SF-71 form?

Also my reason for saving the actual SF-71 worksheet after it's completed is because of the Federal Laws requiring the Original SF-71 form being saved, it is a backup if signed original is lost or misplaced.

Any ideas are greatly appreciated

Regards

Charlie

[lucas]

I put a simple userform in your file and added the first textbox for Name....maybe your just looking for a data input form like this. If so and you run into questions post back.

[OBP]

Well I think you would need to spend a lot of time creating a VBA UserForm to re-create your SF-71 form, would it be worth the extra time spent on it?
Can your SF-71 form be legally saved using the Name from cell A3?
If it can, a simple bit of VBA could save it with that name and the date of the day it was filled in when the sheet is "closed". This would create a unique Folder name.
You can then have some VBA in the database sheet to "browse to", open the sheet and then transfer the data in to your database

[OBP]

lucas, did you post an attachment with the updated sheet with the userform?

[lucas]

Hi OBP,
I don't understand the question.

[OBP]

lucas, sorry I tdid not see your attachment the first time I looked at your post.
I had better get some new glasses, it is old age you know.

[lucas]

Tell me....I keep glasses stashed everywhere I might be, next to computer, my chair, in the car, etc. wait till you get to be my age.

[coliervile]

"OBP" to answer your question on saving the SF-71 with Name in A3...absolutely. That's kind of what I was think of doing. As far as the time spent it probably would. I want to make it as user friendly and also retain all of the information in the database for other usages.

[OBP]

Well I can't help much with user forms as I use Access for databases which have really good forms built in, but lucas sounds like he can help if you need it.
If you want help with the VBA for saving the worksheet and then transferring the dta to the database give us a shout.

[coliervile]

Thanks Lucas for looking at my thread. I ran your basic userform and got an error "Range("A3").Value = TextBox1.Value"...

[coliervile]

Lucas your idea is on the right track I'm not looking to reproduce the whole Sf-71 on the userform, but for the employee to fill-out what is necessary. Once the employee fills the userform with what information is needed then the data from the userform fills the appropriate cell on the SF-71 and also the data from the userform is copied to the database.....hopefully this makes sense????

[coliervile]

OPB I'll give you a hollar when I get to that point...thanks for your help thus far.

Regards

Charlie

[lucas]

Hi Charlie,
can you tell me what the error was.....

You clicked the button....the form opened....you put text in the textbox....clicked add....where was the error and what did it say please.

[coliervile]

lucas I get 2 errors actually: 1) when I click on the "Form" button I get the following- "attachment.php?attachmentid=3492&d1152898232'!showform' cannot be found." and the form never displays 2) when I run the macro the form comes up and after entering a name and hit Add Info the following error comes up "Run-time error '1004':" "Method 'Range' of object'_Global'failed"

[lucas]

Maybe someone else would be willing to download the file from post 4 of this thread and see if they have the same problems.....

[coliervile]

I'm not sure why, but I originally ran the "Form" button without downloading it, I then downloaded it and it ran okay. Sorry for the trouble!

If I want the data from the "Form" to also go into a worksheet called "Database" how would the code look for this range???

[lucas]

I originally ran the "Form" button without downloading it, I then downloaded it and it ran okay

I'm kinda glad that happened Charlie....now I will have an idea what the problem might be if it comes up again...

I want the data from the "Form" to also go into a worksheet called "Database"

Untested but something like this:
[VBA]
Sheets("Database").Range("B2").Value = TextBox1.Value
[/VBA]

[mdmackillop]

Hi Steve,
I've posted a test and a poll on the testing area, as I've had similar problems.
Regards
Malcolm