using list box to edit or select data

Is it possible to have a user form or a list box on a form to select data and
if the data is correct then provide a command button that says data is
correct (which in turn populates the appropriate fields), and if not allow
the user to manually correct the fields that need to be updated or entered by
allowing the user to edit the user form or list box?

Example: I have a form that draws its data from a shared access database
'import external data-new database query'. Currently I placed a list box so
that the user can select a ssn. Once selected several fields are populated
(last name, first, etc...) The problem is that sometimes the ssn might not be
there or the data in the sharable access database hasn't been updated
properly. If it isn't updated, then the user has to manuallly correct the
data in the form. Since I don't want to overwrite the formulae in those
fields, is there a way to edit one or all of the fields using list or combo
boxes and or user forms?

Please help!

Yep.

You may want to look at Data|form for the most basic stuff.

But if you want validation:

You may want to look at John Walkenbach's enhanced dataform:
http://j-walk.com/ss/dataform/index.htm

And if you want to create your own, then Debra Dalgleish has a get started with
userforms:
http://contextures.com/xlUserForm01.html

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

BigPig wrote:
>
> Is it possible to have a user form or a list box on a form to select data and
> if the data is correct then provide a command button that says data is
> correct (which in turn populates the appropriate fields), and if not allow
> the user to manually correct the fields that need to be updated or entered by
> allowing the user to edit the user form or list box?
>
> Example: I have a form that draws its data from a shared access database
> 'import external data-new database query'. Currently I placed a list box so
> that the user can select a ssn. Once selected several fields are populated
> (last name, first, etc...) The problem is that sometimes the ssn might not be
> there or the data in the sharable access database hasn't been updated
> properly. If it isn't updated, then the user has to manuallly correct the
> data in the form. Since I don't want to overwrite the formulae in those
> fields, is there a way to edit one or all of the fields using list or combo
> boxes and or user forms?
>
> Please help!

--

Dave Peterson

Hi Dave,

Thankyou very much for your advice.

What I ended up doing was:

I had two spreadsheets:
1. Just the data that I pulled from a shared access database using data-get
external data etc... In that I created named cell ranges for ssn, other key
fields, and a named range for all of the data that I was using.

2. The form. In that I named cell ranges that referred to the 'data' sheet,
ssn, other key fields and a named range for all of the data that I was using.
I also named them the same as the 'data' sheet's named ranges. Also, I
unchecked the checkbox labeled 'Show error alert after invalid data is
entered'. Then I used data validation using the named ranges which referred
to the other named ranges using index and match.

There was some trial and error, but thanks to your advice I was able to work
it out. Thanks again!

Questions:
1. Is it possible to create a macro that will not only save the worksheet,
but all of the cell formatting (borders, row heights, column widths, merged
cells, and data)?
I know I probably should have created the form using the design feature.

2. Is it possible to have a cell that has data validation in it to show a
result in lieu of having to select the value? I have looked at some examples
that show how to do something like this, but the formatting doesn't remain.

3. Would it be easier to save the raw data input into the form via macro
into a worksheet/notepad/text file. And then be able to pull from that if
necessary into the form? Like formflow.

Thankyou again for you help.

#1. You can save a workbook in code. If you record a macro when you do it
manually, you'll have the code.

Make sure you save the workbook as a normal workbook--not a .csv and not a
..prn. It'll work just like saving any workbook.

#2. Data|validation will help stop users. But your macros can put anything
they want into those cells. But I'd be hesitant to do that. I don't understand
the question about the formatting, though.

If you put a formula in that cell, it has to evaluate to something that's
considered valid by your data|validation rules. And formulas don't return
colors/fonts/or that kind of formatting. They return values.

#3. My personal opinion is that it's usually easier to load things into a form
from an excel worksheet--but you could do anything you want. I don't know what
flowform is.

#4. Would it have been easier to use Access? Just create an Access userform
and keep that data directly in Access? (From someone who doesn't speak the
Access.)



BigPig wrote:
>
> Hi Dave,
>
> Thankyou very much for your advice.
>
> What I ended up doing was:
>
> I had two spreadsheets:
> 1. Just the data that I pulled from a shared access database using data-get
> external data etc... In that I created named cell ranges for ssn, other key
> fields, and a named range for all of the data that I was using.
>
> 2. The form. In that I named cell ranges that referred to the 'data' sheet,
> ssn, other key fields and a named range for all of the data that I was using.
> I also named them the same as the 'data' sheet's named ranges. Also, I
> unchecked the checkbox labeled 'Show error alert after invalid data is
> entered'. Then I used data validation using the named ranges which referred
> to the other named ranges using index and match.
>
> There was some trial and error, but thanks to your advice I was able to work
> it out. Thanks again!
>
> Questions:
> 1. Is it possible to create a macro that will not only save the worksheet,
> but all of the cell formatting (borders, row heights, column widths, merged
> cells, and data)?
> I know I probably should have created the form using the design feature.
>
> 2. Is it possible to have a cell that has data validation in it to show a
> result in lieu of having to select the value? I have looked at some examples
> that show how to do something like this, but the formatting doesn't remain.
>
> 3. Would it be easier to save the raw data input into the form via macro
> into a worksheet/notepad/text file. And then be able to pull from that if
> necessary into the form? Like formflow.
>
> Thankyou again for you help.

--

Dave Peterson

Thanks for your input.

1. I'll do some more research on a macro that'll just save my worksheet etc...

2. That's what I figured, but I had to ask. The worksheet I was referring to
had a macro that allowed the user via properties to change the font,
etc...Unfortunately it didn't stay that way after exiting the list/combo box.
What it did was after double clicking on a list box (data validation) a list
box from control forms would appear in the cell tha you selected, and the
data you typed in would be as you formatted it in properties. But afterward
clicking on another cell it would revert to the original formatting found in
data validation. It's not a biggy, just something that I'll have to play with
some more.

3. The reason for the quesiton is that the form is about 300kb, which would
be saved many times (once for each applicant). Since that kind of space would
add up after awhile, I was trying to think of a better way of saving the
file. But I think if I get rid of some of the control box list boxes, that
will save some space.

Form flow is a databased application that has thousands of forms, and allows
the user to save the data not in the form but in its database files. All a
user would have to do is to save the data, or if they wanted to review the
form from information saved to the database they could do that too.

4. You are right about access. I should do that, it's just that I find excel
much more user friendly, and easier to set up. I have made a couple of
databases in access, and it takes forever and a day to set up. Plus I am a
fan of numbers, and excel's ease of use (most of the time), and I want to
learn more about excel before I move on to access. Nonetheless you are right
about access.

Thanks very much for your advice!

---Mike---