PDA

View Full Version : Data Transfer from User Forms



pdncec
01-04-2008, 08:11 AM
I have added an Excel S/Sheet addin into a User Form via the vba Toolbox to capture data entry. I have 3 Worksheets holding separate data.

I now need to transfer this data into my excel based model for use in the model calculation.
Would appreciate some guidance on the right approach.

Many Thanks:help

lucas
01-04-2008, 09:44 AM
Not sure I understand but it sounds like you are using a spreadsheet object on a userform for data entry and then wish to transfer that data to an actual spreadsheet. Is that correct. If so it might be easier to use textboxes or comboboxes on the userform.

pdncec
01-04-2008, 09:55 AM
Yes you are correct in your assumption.

The maximum amount of data that could be entered would require 50 lines by 15 columns and this would be only one worksheet in the workbook object on the User Form. There is a max of 9 User Forms of similar structure. Is it still more effective/efficient to use text or comboboxes?

Many thanks

lucas
01-04-2008, 10:11 AM
That's a lot of data to input to a worksheet at one time. I'm not sure I would use a userform at all. Will this be for repeated use?

pdncec
01-04-2008, 11:45 AM
Well the sheets have the capacity but I expect they will only be 50% completed on average. Each time the model is run the opportunity for the user to change the data values must be given.

The alternative is to point the user to a location in an existing s/sheet where data can be entered and thenlink bck to te ue form for the next data set. Probably easier?

Regards Paul

lucas
01-04-2008, 12:18 PM
Hi Paul,
I'm still confused about how you intend to use this. Is it going to be a database where you add new data in the next row each time or is it a one time fill this out and then submit type of form?

pdncec
01-04-2008, 12:34 PM
Sorry if I seem a little vague. The data will be entered initially only once but will be modified by the user on a regular basis.

The data will be about vehicle types and the various equipment fits on each type of vehicle. There are 6 areas which have different vehicle types and will have diffferent equipment fits (up to 15 different equipments annd 50 vehicle types per area) The idea of using a User Form style with an Excel Object was to provide a degree of control for the user in inputting the data in an easy manner.

The aimof the model will be to calculate and present costed through life options for support of the equipment fits by area.

Once the data is in the model then changes to the data can be made through the same User Form input structure.

I believe I have to reference the Excel object(Name), Spreadheet(Name) , and Range(Cell) and make the destinatiion cell value equal to the orginating cell value by using a Command Button Click event.

Hope this helps Paul

lucas
01-04-2008, 01:07 PM
Paul,
I'm sorry but I really can't follow your logic here. I can show you an example of how to put data into a spreadsheet from a userform if that would help.

pdncec
01-04-2008, 03:03 PM
Steve

That would be very much appreciated

Thankyou Iwill let you know how I get on

Kind Regards

Paul

lucas
01-04-2008, 04:02 PM
Paul,
attached is a small, simple example of how to put text in a cell of the activesheet from your userform. You will have to look in the vbe to see the form and the code for the button.

lucas
01-04-2008, 04:04 PM
Attached to this post is another example that uses 3 textboxes and puts the data in the next row each time it is run...