PDA

View Full Version : Turn worksheet into "userform"



coliervile
07-14-2006, 08:25 AM
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
07-14-2006, 09:47 AM
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
07-14-2006, 10:13 AM
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
07-14-2006, 10:30 AM
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
07-14-2006, 10:30 AM
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
07-14-2006, 10:34 AM
lucas, did you post an attachment with the updated sheet with the userform?

lucas
07-14-2006, 10:36 AM
Hi OBP,
I don't understand the question.

OBP
07-14-2006, 10:46 AM
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
07-14-2006, 10:48 AM
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.:devil2:

coliervile
07-14-2006, 10:56 AM
"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
07-14-2006, 11:12 AM
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
07-14-2006, 11:14 AM
Thanks Lucas for looking at my thread. I ran your basic userform and got an error "Range("A3").Value = TextBox1.Value"...

coliervile
07-14-2006, 11:28 AM
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
07-14-2006, 11:31 AM
OPB I'll give you a hollar when I get to that point...thanks for your help thus far.

Regards

Charlie

lucas
07-14-2006, 12:40 PM
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
07-14-2006, 01:51 PM
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
07-14-2006, 02:34 PM
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
07-14-2006, 03:17 PM
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
07-14-2006, 06:28 PM
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:

Sheets("Database").Range("B2").Value = TextBox1.Value

mdmackillop
07-15-2006, 03:44 AM
Hi Steve,
I've posted a test and a poll on the testing area, as I've had similar problems.
Regards
Malcolm

mdmackillop
07-15-2006, 04:28 AM
Regarding the problem.
As I see it,
1. You can add a series of userforms as Steve's suggestion. If I was follwing this route, I think this would be best done with 3 or 4 maximum, each grouping together similar data. Latter forms should also display Name etc. information previously entered in labels so no mix up occurs if a disruption in data input takes place (tea breaks etc.)

2. You can work more on the form. It should be possible to code for a predetermined Tab order to make data entry sensible.

3. Make use of ActiveX (Control Toolbox) items on your form, checkboxes, textboxes and the like.

All options require a good deal of work. It all depends upon your users, frequency of use etc. Changes to your form layout can also be difficult and require changes to background code etc.

Personally, I would recommend Access. Its a bit of a learing process, but definitely the best solution in the long run. It will be more secure, easier to manage and multiple user access is simpler.

Regards
MD

OBP
07-15-2006, 04:38 AM
MD, I am glad that you agree about "Access" being the better solution.

mdmackillop
07-15-2006, 04:50 AM
Hi Charlie,
I would even suggest that you hire a professional (or even someone here) to create your initial database. With this at your disposal, you can start to learn how it's put together and see the code that makes it work. That's how I started in Access, and athough no expert, I've now created half a dozen or so different applications being used by my employer.
Regards
MD

coliervile
07-15-2006, 05:18 AM
Here's my newly designed "userform" with a database worksheet for the items that's needed to be tracked. Can you give me a head start with the command button controls and how the userform dumps the data into the OPM-71 worksheet and the Database worksheet???

Regards


Charlie

coliervile
07-15-2006, 05:26 AM
Thanks MD I will definitely look into that. One of my questions from way back, not necessarily in this thread, was what is the pros and cons of Access over Excel? I haven't heard why one over the other in the application that's being used?

Learner extraordinaire

Charlie

Norie
07-15-2006, 05:37 AM
Charlie

There are many reasons for using Access for a database instead of Excel.

The main one is probably that Access is actually a database application and Excel isn't.

lucas
07-15-2006, 08:57 AM
Charlie, if you decide to go with Excel and use the form from post 24 then you have a little work ahead of you. Only you know which items you wish to go to which sheet and the location. If you look at the file I uploaded it will show you how to get data to a worksheet from a textbox and how to make things happen on button click. Look carefully at the modules and the code for the form and when you run into a specif problem like how to populate your comboboxes, etc. post your question here. HTH

coliervile
07-15-2006, 10:00 AM
Thanks Lucas for the info. and I'll keep you in mind when the time comes.

Regards

Charlie