Reloading and Editing UserForms in Excel

Prev: Dynamic radar chart
Next: Pivot Table and Protection

From: lcoreilly on 24 Mar 2010 18:33

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.

From: JLGWhiz on 24 Mar 2010 18:50

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" <lecoughlin(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.

From: Mnzava Mnzava on 25 Mar 2010 06:30

"JLGWhiz" 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" <lecoughlin(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.
>
>
> .
>

From: Mnzava on 25 Mar 2010 06:32

From: lcoreilly on 25 Mar 2010 10:26

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.

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

Reloading and Editing UserForms in Excel - help!