In Excel how do I show the contents of just 1 row as a form?

[Guest]

In Microsoft Works database you can change between looking at all the entries
(i.e. all the rows and columns) in list format, you can switch to Form format
and see just all the columns for one entry. As an example, say you have the
following column heads: First name, last name, address, city, state, postal
code, phone #. In MS Works database you can click From view and see just one
persons info/record. Then to switch back to seeing the whole list (i.e all
rows/entries and columns.

How do you view just one whole row all on one screen as the only record you
are viewing in Excel. Or, can this only be done in Access, since Excel is not
a database?

I am just trying to find a way to see one entry, w/o scrolling left and
right to view a long row. Is there a way to view all the columns for one
record/entry/row on screen but ONLY see just that entry?

 

[Ian]

By default there is no way to do as you wish. As you point out, Excel is not
a database and therefore lacks some built-in database type facilities.

It is, however, possible to use VBA to create a form which will do as you
wish.

 

[Paul D. Simon]

I don't know if this will give you what you're looking for, but give it
a try. Just copy and paste the following code into a module in the
VBE.

Option Explicit
Sub OpenForm()

'By Dave Peterson 10/20/2003

SendKeys "{DOWN " & ActiveCell.Row - 2 & "}{TAB 3}"
Application.DisplayAlerts = False
ActiveSheet.ShowDataForm
Application.DisplayAlerts = True

End Sub


Just click on any record in your Excel database and run this macro. A
form will come up showing you all the fields for just that one record.

A couple of caveats, though:
1.) There is a limit to the number of fields the form will hold. (I
can't remember what it is offhand, but you'll get an error message if
there are too many columns in your database.)
2.) Your field names (in other words, the column headings) must be in
row 1. Otherwise, this code won't work.

Hope this helps. Let me know.

 

[Ildhund]

In my Excel 2000, Menu Data>Form does just what I think you want.

Noel

in
news:[email protected]...

 

[Paul D. Simon]

Hi Noel,

Data>Form does indeed work well, and in fact, I use it often.

The flaw in Dataform is that no matter what record you click on in your
database, Dataform will always show you the first record instead, not
the one you clicked on - very irritating.

In other words, if I click within row 356 in my worksheet and then do
Data>Form to see that record, it will show me the record in row 1
instead - not the one I want to see in row 356. It's as if the
Microsoft programmers stopped short when writing this program. And as
many complaints about it as there have been over the years, they still
haven't done anything to correct it.

The code I recommended above (from Dave Peterson) corrects this such
that running the code will now cause DataForm to show you whatever
record you've clicked on within the database (subject to the caveats I
mentioned).

Paul

 

[Ildhund]

Hi Paul

Sure. But given that the OP had apparently only recently graduated from
Works and hadn't yet even explored all the menu options or studied the
available help, a code offering may appear a bit overwhelming. "Just
copy and paste the following code into a module in the VBE" may make
sense to you and me, but not necessarily to a novice.

Notwithstanding, I'm grateful (to both you and the author) for the code
which I'll certainly be using in some of my projects.

Noel

 

[Paul D. Simon]

Sure. But given that the OP had apparently only recently graduated from

Works and hadn't yet even explored all the menu options or studied the
available help, a code offering may appear a bit overwhelming. "Just
copy and paste the following code into a module in the VBE" may make
sense to you and me, but not necessarily to a novice.

Point well-taken, and you're absolutely correct. Sometimes I forget to
be aware of the level of knowledge of the person to whom I'm replying.
Certainly, in this case, the simple menu solution would be more easily
understood and therefore the better way to go. The VBA solution could
be incorporated down the road if and when the OP reached that level of
understanding.

(We haven't heard from Sasha since the initial question, so it's
possible the OP hasn't revisited this thread to see our solutions
anyway.)

Thanks,
Paul