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
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.
Steve
"Nearly all men can stand adversity, but if you want to test a man's character, give him power."
-Abraham Lincoln
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
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?
Steve
"Nearly all men can stand adversity, but if you want to test a man's character, give him power."
-Abraham Lincoln
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
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?
Steve
"Nearly all men can stand adversity, but if you want to test a man's character, give him power."
-Abraham Lincoln
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
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.
Steve
"Nearly all men can stand adversity, but if you want to test a man's character, give him power."
-Abraham Lincoln
Steve
That would be very much appreciated
Thankyou Iwill let you know how I get on
Kind Regards
Paul
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.
Steve
"Nearly all men can stand adversity, but if you want to test a man's character, give him power."
-Abraham Lincoln
Attached to this post is another example that uses 3 textboxes and puts the data in the next row each time it is run...
Steve
"Nearly all men can stand adversity, but if you want to test a man's character, give him power."
-Abraham Lincoln
Posting Permissions
Reply With Quote
