| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Save data entered into UserForm to be Edited later
I have a UserForm that the Sales people will be using to calculate the cost
of a product. The UserForm has many Controls such as CheckBoxes, ComboBoxes, TextBoxes, Option Buttons, etc. for the Sales person to navigate through. When the sales person is done entering a products description there is a Command Button on the UserForm that they will Click to add the product description, product quantity,cost and an Edit Button onto Sheets1. I want the sales person to be able to click the Edit Button and all the info. they entered for that item will automatically be loaded into the UserForm for editing. At any time there could be 5-6 items on Sheets1. How could this be done? |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Save data entered into UserForm to be Edited later
Your assumption is correct. I think storing the control values and then
reversing the code isn't a problem for 1 item on Sheet1. But the sales people will have to add 5-10 items on Sheet1. Is there an easy way to differeniate the different items on the same Userform? For example, if I click the Edit Button next to Item #1, Item #1's info will fill the Userform, or if I click the Edit Button next to Item #7, Item #7's info will fill the Userform. I want to beable to store the data on One worksheet. Is this possible? "Nigel" wrote: I assume you want the UserForm controls to take all the values set at the time the user added the item? Reverse the code that put the data on the worksheet, you may need to store other control values in the sheet and restore these at the same time. -- Regards, Nigel "RyanH" wrote in message ... I have a UserForm that the Sales people will be using to calculate the cost of a product. The UserForm has many Controls such as CheckBoxes, ComboBoxes, TextBoxes, Option Buttons, etc. for the Sales person to navigate through. When the sales person is done entering a products description there is a Command Button on the UserForm that they will Click to add the product description, product quantity,cost and an Edit Button onto Sheets1. I want the sales person to be able to click the Edit Button and all the info. they entered for that item will automatically be loaded into the UserForm for editing. At any time there could be 5-6 items on Sheets1. How could this be done? |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Save data entered into UserForm to be Edited later
I am not entirely clear what you mean by "store all the data on One
worksheet". You indicate that the items are stored in sheet 1 is that not one worksheet? If you wish for the user to see ALL items and CHOOSE one to edit on the UserForm, why not add a ListBox of the item identifier on the UserForm, such that as each item is added the list is updated, then the user can choose the item to edit, thus avoiding the Edit button next to each item on sheet1? Hope I interpreted your needs correctly? -- Regards, Nigel "RyanH" wrote in message ... Your assumption is correct. I think storing the control values and then reversing the code isn't a problem for 1 item on Sheet1. But the sales people will have to add 5-10 items on Sheet1. Is there an easy way to differeniate the different items on the same Userform? For example, if I click the Edit Button next to Item #1, Item #1's info will fill the Userform, or if I click the Edit Button next to Item #7, Item #7's info will fill the Userform. I want to beable to store the data on One worksheet. Is this possible? "Nigel" wrote: I assume you want the UserForm controls to take all the values set at the time the user added the item? Reverse the code that put the data on the worksheet, you may need to store other control values in the sheet and restore these at the same time. -- Regards, Nigel "RyanH" wrote in message ... I have a UserForm that the Sales people will be using to calculate the cost of a product. The UserForm has many Controls such as CheckBoxes, ComboBoxes, TextBoxes, Option Buttons, etc. for the Sales person to navigate through. When the sales person is done entering a products description there is a Command Button on the UserForm that they will Click to add the product description, product quantity,cost and an Edit Button onto Sheets1. I want the sales person to be able to click the Edit Button and all the info. they entered for that item will automatically be loaded into the UserForm for editing. At any time there could be 5-6 items on Sheets1. How could this be done? |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Save data entered into UserForm to be Edited later
Each item is in its own row in the sheet, right? In hidden cells in the same
row as the visible items, save all the settings for each control on the form. When the user selects a row and hits "update", load all of this information from the active row onto the form. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "RyanH" wrote in message ... Your assumption is correct. I think storing the control values and then reversing the code isn't a problem for 1 item on Sheet1. But the sales people will have to add 5-10 items on Sheet1. Is there an easy way to differeniate the different items on the same Userform? For example, if I click the Edit Button next to Item #1, Item #1's info will fill the Userform, or if I click the Edit Button next to Item #7, Item #7's info will fill the Userform. I want to beable to store the data on One worksheet. Is this possible? "Nigel" wrote: I assume you want the UserForm controls to take all the values set at the time the user added the item? Reverse the code that put the data on the worksheet, you may need to store other control values in the sheet and restore these at the same time. -- Regards, Nigel "RyanH" wrote in message ... I have a UserForm that the Sales people will be using to calculate the cost of a product. The UserForm has many Controls such as CheckBoxes, ComboBoxes, TextBoxes, Option Buttons, etc. for the Sales person to navigate through. When the sales person is done entering a products description there is a Command Button on the UserForm that they will Click to add the product description, product quantity,cost and an Edit Button onto Sheets1. I want the sales person to be able to click the Edit Button and all the info. they entered for that item will automatically be loaded into the UserForm for editing. At any time there could be 5-6 items on Sheets1. How could this be done? |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Save data entered into UserForm to be Edited later
Following up on my post... You can get fancy and put a listbox on the form
that lists each item on the sheet. When the listbox selection is changed, all the existing data on the form is saved to the appropriate row and all the data from the new row (selected in the listbox) is loaded to the form. It may seem complicated, but just needs some consistent bookkeeping. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Nigel" wrote in message ... I am not entirely clear what you mean by "store all the data on One worksheet". You indicate that the items are stored in sheet 1 is that not one worksheet? If you wish for the user to see ALL items and CHOOSE one to edit on the UserForm, why not add a ListBox of the item identifier on the UserForm, such that as each item is added the list is updated, then the user can choose the item to edit, thus avoiding the Edit button next to each item on sheet1? Hope I interpreted your needs correctly? -- Regards, Nigel "RyanH" wrote in message ... Your assumption is correct. I think storing the control values and then reversing the code isn't a problem for 1 item on Sheet1. But the sales people will have to add 5-10 items on Sheet1. Is there an easy way to differeniate the different items on the same Userform? For example, if I click the Edit Button next to Item #1, Item #1's info will fill the Userform, or if I click the Edit Button next to Item #7, Item #7's info will fill the Userform. I want to beable to store the data on One worksheet. Is this possible? "Nigel" wrote: I assume you want the UserForm controls to take all the values set at the time the user added the item? Reverse the code that put the data on the worksheet, you may need to store other control values in the sheet and restore these at the same time. -- Regards, Nigel "RyanH" wrote in message ... I have a UserForm that the Sales people will be using to calculate the cost of a product. The UserForm has many Controls such as CheckBoxes, ComboBoxes, TextBoxes, Option Buttons, etc. for the Sales person to navigate through. When the sales person is done entering a products description there is a Command Button on the UserForm that they will Click to add the product description, product quantity,cost and an Edit Button onto Sheets1. I want the sales person to be able to click the Edit Button and all the info. they entered for that item will automatically be loaded into the UserForm for editing. At any time there could be 5-6 items on Sheets1. How could this be done? |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Save data entered into UserForm to be Edited later
Thanks for sticking through this Nigel. I realize I haven't explained my
situation throughly and I'm sorry for that. But let me try to explain now. We have 10 different products we sell. Each product has its own UserForm and Worksheet to store the data entered into the Userform. When the sales person fills out a particular products UserForm, the data will be stored on its associated Worksheet, AND a product description along with a Edit button will be added to the Order Worksheet (Sheet11). The sales person may add 4 different/same products onto the Order Worksheet. The reason for the Edit button next to each product on the Order Worksheet is to call that particular products UserForm (that will be autofilled) for editing. The issue I do not know how to do is this: One of our Products we sell are Plastic Sign Faces. Lets say I use UserForm1(used for plastic faces) to enter data for Item A and Worksheet1 stores that data. Order Worksheet (Sheet11) now has Item A's description along with a Edit button next to it. What if the sales person wants to add another plastic face (Item B) using UserForm1? How do I link the UserForm1 Controls to cells in Worksheet1 so that Item B's (using UserForm1) data will not be stored (in Worksheet1) on top of Item A's data? If this happens I will not beable to edit Item A when the edit button is hit. At this point I probably confused you even more, but I wanted to try to keep this somewhat short. "Nigel" wrote: I am not entirely clear what you mean by "store all the data on One worksheet". You indicate that the items are stored in sheet 1 is that not one worksheet? If you wish for the user to see ALL items and CHOOSE one to edit on the UserForm, why not add a ListBox of the item identifier on the UserForm, such that as each item is added the list is updated, then the user can choose the item to edit, thus avoiding the Edit button next to each item on sheet1? Hope I interpreted your needs correctly? -- Regards, Nigel "RyanH" wrote in message ... Your assumption is correct. I think storing the control values and then reversing the code isn't a problem for 1 item on Sheet1. But the sales people will have to add 5-10 items on Sheet1. Is there an easy way to differeniate the different items on the same Userform? For example, if I click the Edit Button next to Item #1, Item #1's info will fill the Userform, or if I click the Edit Button next to Item #7, Item #7's info will fill the Userform. I want to beable to store the data on One worksheet. Is this possible? "Nigel" wrote: I assume you want the UserForm controls to take all the values set at the time the user added the item? Reverse the code that put the data on the worksheet, you may need to store other control values in the sheet and restore these at the same time. -- Regards, Nigel "RyanH" wrote in message ... I have a UserForm that the Sales people will be using to calculate the cost of a product. The UserForm has many Controls such as CheckBoxes, ComboBoxes, TextBoxes, Option Buttons, etc. for the Sales person to navigate through. When the sales person is done entering a products description there is a Command Button on the UserForm that they will Click to add the product description, product quantity,cost and an Edit Button onto Sheets1. I want the sales person to be able to click the Edit Button and all the info. they entered for that item will automatically be loaded into the UserForm for editing. At any time there could be 5-6 items on Sheets1. How could this be done? |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Save data entered into UserForm to be Edited later
Using the method I suggested, you could have the form pop up blank if it is
activated when the active cell is in an empty row. The data in the form are always stored in the active cell's row when the form is closed. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "RyanH" wrote in message ... Thanks for sticking through this Nigel. I realize I haven't explained my situation throughly and I'm sorry for that. But let me try to explain now. We have 10 different products we sell. Each product has its own UserForm and Worksheet to store the data entered into the Userform. When the sales person fills out a particular products UserForm, the data will be stored on its associated Worksheet, AND a product description along with a Edit button will be added to the Order Worksheet (Sheet11). The sales person may add 4 different/same products onto the Order Worksheet. The reason for the Edit button next to each product on the Order Worksheet is to call that particular products UserForm (that will be autofilled) for editing. The issue I do not know how to do is this: One of our Products we sell are Plastic Sign Faces. Lets say I use UserForm1(used for plastic faces) to enter data for Item A and Worksheet1 stores that data. Order Worksheet (Sheet11) now has Item A's description along with a Edit button next to it. What if the sales person wants to add another plastic face (Item B) using UserForm1? How do I link the UserForm1 Controls to cells in Worksheet1 so that Item B's (using UserForm1) data will not be stored (in Worksheet1) on top of Item A's data? If this happens I will not beable to edit Item A when the edit button is hit. At this point I probably confused you even more, but I wanted to try to keep this somewhat short. "Nigel" wrote: I am not entirely clear what you mean by "store all the data on One worksheet". You indicate that the items are stored in sheet 1 is that not one worksheet? If you wish for the user to see ALL items and CHOOSE one to edit on the UserForm, why not add a ListBox of the item identifier on the UserForm, such that as each item is added the list is updated, then the user can choose the item to edit, thus avoiding the Edit button next to each item on sheet1? Hope I interpreted your needs correctly? -- Regards, Nigel "RyanH" wrote in message ... Your assumption is correct. I think storing the control values and then reversing the code isn't a problem for 1 item on Sheet1. But the sales people will have to add 5-10 items on Sheet1. Is there an easy way to differeniate the different items on the same Userform? For example, if I click the Edit Button next to Item #1, Item #1's info will fill the Userform, or if I click the Edit Button next to Item #7, Item #7's info will fill the Userform. I want to beable to store the data on One worksheet. Is this possible? "Nigel" wrote: I assume you want the UserForm controls to take all the values set at the time the user added the item? Reverse the code that put the data on the worksheet, you may need to store other control values in the sheet and restore these at the same time. -- Regards, Nigel "RyanH" wrote in message ... I have a UserForm that the Sales people will be using to calculate the cost of a product. The UserForm has many Controls such as CheckBoxes, ComboBoxes, TextBoxes, Option Buttons, etc. for the Sales person to navigate through. When the sales person is done entering a products description there is a Command Button on the UserForm that they will Click to add the product description, product quantity,cost and an Edit Button onto Sheets1. I want the sales person to be able to click the Edit Button and all the info. they entered for that item will automatically be loaded into the UserForm for editing. At any time there could be 5-6 items on Sheets1. How could this be done? |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Save data entered into UserForm to be Edited later
I think your problem reduces to simply ensuring that when a new item is
added to the relevant product sheet, it is added to the next blank row. I suspect that you might be linking your controls on the relevant UserForm to that sheet, hence changes on the form would overwrite the data, since the links are not being change? I would create some code to take the control values and store these on the relevant worksheet, first establishing the next blank row on that sheet. In doing this you are going to need to establish the relationship between your item sheet and your order log/edit sheet (sheet11). Simply saving the product sheet row number in sheet11 would work but is in danger of losing sync if any row is subsequently deleted. My approach would be to create a unique key and store that on both product and order sheets; bringing the data back together is then a simple matter of searching / scanning for the key value. One final suggestion, for complex forms I always use an array to act as the 'link' between a worksheet and the UserForm controls. This gives more control over the read/write process and simplifies some actions, such as cancel changes. It is also faster as you can add 'layers' in the array to pre-load the next item(s) for editing. -- Regards, Nigel "RyanH" wrote in message ... Thanks for sticking through this Nigel. I realize I haven't explained my situation throughly and I'm sorry for that. But let me try to explain now. We have 10 different products we sell. Each product has its own UserForm and Worksheet to store the data entered into the Userform. When the sales person fills out a particular products UserForm, the data will be stored on its associated Worksheet, AND a product description along with a Edit button will be added to the Order Worksheet (Sheet11). The sales person may add 4 different/same products onto the Order Worksheet. The reason for the Edit button next to each product on the Order Worksheet is to call that particular products UserForm (that will be autofilled) for editing. The issue I do not know how to do is this: One of our Products we sell are Plastic Sign Faces. Lets say I use UserForm1(used for plastic faces) to enter data for Item A and Worksheet1 stores that data. Order Worksheet (Sheet11) now has Item A's description along with a Edit button next to it. What if the sales person wants to add another plastic face (Item B) using UserForm1? How do I link the UserForm1 Controls to cells in Worksheet1 so that Item B's (using UserForm1) data will not be stored (in Worksheet1) on top of Item A's data? If this happens I will not beable to edit Item A when the edit button is hit. At this point I probably confused you even more, but I wanted to try to keep this somewhat short. "Nigel" wrote: I am not entirely clear what you mean by "store all the data on One worksheet". You indicate that the items are stored in sheet 1 is that not one worksheet? If you wish for the user to see ALL items and CHOOSE one to edit on the UserForm, why not add a ListBox of the item identifier on the UserForm, such that as each item is added the list is updated, then the user can choose the item to edit, thus avoiding the Edit button next to each item on sheet1? Hope I interpreted your needs correctly? -- Regards, Nigel "RyanH" wrote in message ... Your assumption is correct. I think storing the control values and then reversing the code isn't a problem for 1 item on Sheet1. But the sales people will have to add 5-10 items on Sheet1. Is there an easy way to differeniate the different items on the same Userform? For example, if I click the Edit Button next to Item #1, Item #1's info will fill the Userform, or if I click the Edit Button next to Item #7, Item #7's info will fill the Userform. I want to beable to store the data on One worksheet. Is this possible? "Nigel" wrote: I assume you want the UserForm controls to take all the values set at the time the user added the item? Reverse the code that put the data on the worksheet, you may need to store other control values in the sheet and restore these at the same time. -- Regards, Nigel "RyanH" wrote in message ... I have a UserForm that the Sales people will be using to calculate the cost of a product. The UserForm has many Controls such as CheckBoxes, ComboBoxes, TextBoxes, Option Buttons, etc. for the Sales person to navigate through. When the sales person is done entering a products description there is a Command Button on the UserForm that they will Click to add the product description, product quantity,cost and an Edit Button onto Sheets1. I want the sales person to be able to click the Edit Button and all the info. they entered for that item will automatically be loaded into the UserForm for editing. At any time there could be 5-6 items on Sheets1. How could this be done? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How can I save an image I edited in Excel? | Excel Discussion (Misc queries) | |||
| how can i re-populate a userform with data already entered? | New Users to Excel | |||
| Can I save excel data as it is entered? | Excel Discussion (Misc queries) | |||
| How do I get excel to save after data is entered in a certain cell | Excel Discussion (Misc queries) | |||
| Retrieving previously entered userform data from saved spreadsheet | Excel Programming | |||
Linear Mode
