Reloading and Editing UserForms in Excel - help!

[lcoreilly]

I have created a database in Excel for a client to do data entry.
Right now I have multiple forms set up that they can scroll through
and enter data about different programs. Then at the end they are
prompted to save the record, which runs code to copy all of the data
on the forms to a hidden worksheet.

I need to be able to build in the functionality to allow the user to
save a record half way through completing, and then be able to re-load
the data at a later time, edit it, and their changes would be saved
over the original record.

Unfortunately, creating this database in Access is not an option.
But, that's basically what I need to do - make the Excel forms behave
like Access in that you can scroll through records, edit them, and
changes are saved.

I have been scouring the Internet for examples or information on how
to do this, but haven't come up with any answers. Does anyone have
any insight? I would really appreciate it.

Thanks.

 

[JLGWhiz]

Well, there is not much to go on from the info in your post. But the
principle of moving data from UF to WS is:

Worksheet.Range = UserForm.Control.Value

So when you want to reload the UserForm it seem to me that it woud be:

UserForm.Control = Worksheet.Range.Value

Now that is in its simplest form, but that is how it is done. So it means
that you have to know where you store the data if you save it to the WS for
later recall. You could do that with dexignated object variables for the
ranges to be recalled or you could use a special worksheet with a reserved
range that stored the data so that you could just loop through it to re-load
the controls. There are probably more and better options, but maybe this
will get you pointed in the right direction.

 

[Mnzava]

Well, there is not much to go on from the info in your post. But the
principle of moving data from UF to WS is:

Worksheet.Range = UserForm.Control.Value

So when you want to reload the UserForm it seem to me that it woud be:

UserForm.Control = Worksheet.Range.Value

Now that is in its simplest form, but that is how it is done. So it means
that you have to know where you store the data if you save it to the WS for
later recall. You could do that with dexignated object variables for the
ranges to be recalled or you could use a special worksheet with a reserved
range that stored the data so that you could just loop through it to re-load
the controls. There are probably more and better options, but maybe this
will get you pointed in the right direction.






.

 

[Mnzava]

Well, there is not much to go on from the info in your post. But the
principle of moving data from UF to WS is:

Worksheet.Range = UserForm.Control.Value

So when you want to reload the UserForm it seem to me that it woud be:

UserForm.Control = Worksheet.Range.Value

Now that is in its simplest form, but that is how it is done. So it means
that you have to know where you store the data if you save it to the WS for
later recall. You could do that with dexignated object variables for the
ranges to be recalled or you could use a special worksheet with a reserved
range that stored the data so that you could just loop through it to re-load
the controls. There are probably more and better options, but maybe this
will get you pointed in the right direction.






.
please help

Can you ellaborate to me how we can access the file where I can make
changes on Worksheet.Range = UserForm.Control.Value to UserForm.Control =
Worksheet.Range.Value

Thanks

 

[lcoreilly]

Thanks for your help.

I have set up the code to "reverse" so now if I click a button, the
data reloads from the hidden worksheets into the forms.

I also created a button for the user to "save completed entry" or
"save partially completed entry." If they save a partially completed
entry, the data on the form gets transferred over to hidden worksheets
"temp_data" "temp_data2" "temp_data3" and "temp_data4"

My question is (and it may be a simple answer, my brain just doesn't
seem to be working right now) if the user loads partially completed
data, and then chooses to save it (still incomplete) how do I have it
overwrite the current entry so that there are not multiple entries
each time they go to edit. Also, when they finally say "save
completed entry" how do I write the code for it to check if the entry
exists in the temp data file, and if so delete it. I can write the
code to delete it, I'm just not sure how to identify if it already
exists.

Thanks in advance.

 

[JLGWhiz]

[if the user loads partially completed
data, and then chooses to save it (still incomplete) how do I have it
overwrite the current entry so that there are not multiple entries
each time they go to edit.]

It should overwrite, without displaying alerts, the current entry if you use
syntax like:

Sheets("Sheet1").Range("A1") = UserForm1.TextBox1.Value


[Also, when they finally say "save
completed entry" how do I write the code for it to check if the entry
exists in the temp data file, and if so delete it.]

It would be logical to put the code to clear the temporary storage area in
the same procedure that reloads the UserForm. That way it will not have any
residual data in case the user decides to make a temporary save again.

One thing to be cautious of when writing the code for these interchanges of
data is that you could inadvertantly delete data by making a destination
object equal to a source object with a value of zero. This can be handled
with an If ... Then statement to test if the source object value is greater
than zero.

Looks like you are on the right track. Good luck.


Thanks for your help.

I have set up the code to "reverse" so now if I click a button, the
data reloads from the hidden worksheets into the forms.

I also created a button for the user to "save completed entry" or
"save partially completed entry." If they save a partially completed
entry, the data on the form gets transferred over to hidden worksheets
"temp_data" "temp_data2" "temp_data3" and "temp_data4"

My question is (and it may be a simple answer, my brain just doesn't
seem to be working right now) if the user loads partially completed
data, and then chooses to save it (still incomplete) how do I have it
overwrite the current entry so that there are not multiple entries
each time they go to edit. Also, when they finally say "save
completed entry" how do I write the code for it to check if the entry
exists in the temp data file, and if so delete it. I can write the
code to delete it, I'm just not sure how to identify if it already
exists.

Thanks in advance.