Save data entered into UserForm to be Edited later

[RyanH]

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?

 

[Nigel]

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.

 

[RyanH]

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]

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
(e-mail address removed)

 

[Jon Peltier]

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]

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

 

[RyanH]

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.

 

[Jon Peltier]

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

 

[Nigel]

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
(e-mail address removed)