Prev: Dynamic radar chart
Next: Pivot Table and Protection
From: JLGWhiz on 25 Mar 2010 11:05
[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.


"lcoreilly" <lecoughlin(a)gmail.com> wrote in message
news:6b876093-3df2-4aee-930a-0c36dd717f5c(a)e7g2000yqf.googlegroups.com...
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.

On Mar 24, 6:50 pm, "JLGWhiz" <JLGW...(a)cfl.rr.com> wrote:
> 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.
>
> "lcoreilly" <lecough...(a)gmail.com> wrote in message
>
> news:681042a4-9dd8-485a-acf4-0b7f10fbc7c3(a)y17g2000yqd.googlegroups.com...
>
>
>
> >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.


First  |  Prev  | 
Pages: 1 2
Prev: Dynamic radar chart
Next: Pivot Table and Protection