The data/form feature of Excel facilitates viewing and entering data
when the number of fields in a row / record are too numerous to be
viewed similtaneously because the extend off the left or right side of
the screen.

With an Excel spread sheet open, when one selects Data / Form from the
tool bar menu, Excel opens a form showing the field headings as well as
all the fields in the first record. One can then scroll down to any
row / record in the sheet.

Is there a simple way to get the form to show the current record. For
example, if a cell in row ten is currently selected, I'd like to be able
to open the form and have it show the contents of the fields in row ten.
I know that I could write a macro to:

Save the number of the current row,

Open the form, and

Mimic execution of down key strokes until the number of strokes equals
the row.

IS THERE A SIMPLER WAY that is compatible with Excel97?

Michael

I believe that is it if you want to use the builtin form. I checked the
excel4 macro help file and the macro version takes no arguments, so I don't
believe there is any built in support for positioning it to a certain
record.

--
Regards,
Tom Ogilvy

buczacz wrote in message
...
The data/form feature of Excel facilitates viewing and entering data
when the number of fields in a row / record are too numerous to be
viewed similtaneously because the extend off the left or right side of
the screen.

With an Excel spread sheet open, when one selects Data / Form from the
tool bar menu, Excel opens a form showing the field headings as well as
all the fields in the first record. One can then scroll down to any
row / record in the sheet.

Is there a simple way to get the form to show the current record. For
example, if a cell in row ten is currently selected, I'd like to be able
to open the form and have it show the contents of the fields in row ten.
I know that I could write a macro to:

Save the number of the current row,

Open the form, and

Mimic execution of down key strokes until the number of strokes equals
the row.

IS THERE A SIMPLER WAY that is compatible with Excel97?

Michael

But mimicing the down key isn't too difficult:

Option Explicit
Sub testme()

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

End Sub




buczacz wrote:

The data/form feature of Excel facilitates viewing and entering data
when the number of fields in a row / record are too numerous to be
viewed similtaneously because the extend off the left or right side of
the screen.

With an Excel spread sheet open, when one selects Data / Form from the
tool bar menu, Excel opens a form showing the field headings as well as
all the fields in the first record. One can then scroll down to any
row / record in the sheet.

Is there a simple way to get the form to show the current record. For
example, if a cell in row ten is currently selected, I'd like to be able
to open the form and have it show the contents of the fields in row ten.
I know that I could write a macro to:

Save the number of the current row,

Open the form, and

Mimic execution of down key strokes until the number of strokes equals
the row.

IS THERE A SIMPLER WAY that is compatible with Excel97?

Michael

--

Dave Peterson

Thanks ... will try.. Michael

Dave Peterson wrote:
But mimicing the down key isn't too difficult:

Option Explicit
Sub testme()

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

End Sub




buczacz wrote:

The data/form feature of Excel facilitates viewing and entering data
when the number of fields in a row / record are too numerous to be
viewed similtaneously because the extend off the left or right side of
the screen.

With an Excel spread sheet open, when one selects Data / Form from the
tool bar menu, Excel opens a form showing the field headings as well as
all the fields in the first record. One can then scroll down to any
row / record in the sheet.

Is there a simple way to get the form to show the current record. For
example, if a cell in row ten is currently selected, I'd like to be able
to open the form and have it show the contents of the fields in row ten.
I know that I could write a macro to:

Save the number of the current row,

Open the form, and

Mimic execution of down key strokes until the number of strokes equals
the row.

IS THERE A SIMPLER WAY that is compatible with Excel97?

Michael

So why do you bother to say you don't want to do it that way?

--
Regards,
Tom Ogilvy

buczacz wrote in message
...
Thanks ... will try.. Michael

Dave Peterson wrote:
But mimicing the down key isn't too difficult:

Option Explicit
Sub testme()

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

End Sub




buczacz wrote:

The data/form feature of Excel facilitates viewing and entering data
when the number of fields in a row / record are too numerous to be
viewed similtaneously because the extend off the left or right side of
the screen.

With an Excel spread sheet open, when one selects Data / Form from the
tool bar menu, Excel opens a form showing the field headings as well as
all the fields in the first record. One can then scroll down to any
row / record in the sheet.

Is there a simple way to get the form to show the current record. For
example, if a cell in row ten is currently selected, I'd like to be able
to open the form and have it show the contents of the fields in row ten.
I know that I could write a macro to:

Save the number of the current row,

Open the form, and

Mimic execution of down key strokes until the number of strokes equals
the row.

IS THERE A SIMPLER WAY that is compatible with Excel97?

Michael

Statement and two more questions.

1st - I was looking for alternative as I didn't think it was quite that
simple.. I was not familiar with format of the SENDKEYS
command...previously my solution included it in a loop. Your solution
was far more elegant.

2nd - now that I have data/form coming up, I want to step to the 4th
item on the form. The following works, but it overwrites with a blank
the item that had been written in the first column. i.e. when the
data/form comes up, it no longer shows the information that had been in
the first item (first column of active row), even though there is a
value shown in the first column of the row on the sheet shown in
background. Of course, when data/form is closed, cell corresponding to
first item becomes blank. ANY IDEAS WHY DATA IS OVERWRITTEN?

SendKeys "{DOWN " & ActiveCell.Row - 2 & "}"
SendKeys "{TAB 3 }"

3rd - why do the key commands go before the window is opened by
ActiveSheet.ShowDataForm

when you do the task manually, you open data/form, AND THEN step down
with the DOWN key and steps across with the TAB key.

CAUTION - I don't think it matters, but about check out was done using
Excel95, as I've been limited to my laptop this weekend. Normally I use
desk top machine with Excel97.

Thanks in advance. Michael





Tom Ogilvy wrote:
So why do you bother to say you don't want to do it that way?

--
Regards,
Tom Ogilvy

buczacz wrote in message
...

Thanks ... will try.. Michael

Dave Peterson wrote:

But mimicing the down key isn't too difficult:

Option Explicit
Sub testme()

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

End Sub




buczacz wrote:


The data/form feature of Excel facilitates viewing and entering data
when the number of fields in a row / record are too numerous to be
viewed similtaneously because the extend off the left or right side of
the screen.

With an Excel spread sheet open, when one selects Data / Form from the
tool bar menu, Excel opens a form showing the field headings as well as
all the fields in the first record. One can then scroll down to any
row / record in the sheet.

Is there a simple way to get the form to show the current record. For
example, if a cell in row ten is currently selected, I'd like to be able
to open the form and have it show the contents of the fields in row ten.
I know that I could write a macro to:

Save the number of the current row,

Open the form, and

Mimic execution of down key strokes until the number of strokes equals
the row.

IS THERE A SIMPLER WAY that is compatible with Excel97?

Michael

Your additional sendkeys worked fine for me--no data was overwritten (although I
had to delete that trailing space).

But so did this:

Option Explicit
Sub testme()

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

End Sub

And you're plopping some characters in a keyboard buffer so they better be there
when whatever is going to read them needs them.

(Sendkeys is not a solution of choice for most things. Lots can go wrong--maybe
you won't be in excel when the macro runs, so something else will get the down's
and tabs???)

But I used xl2002 and win98 (just so you know what I tested with).


buczacz wrote:

2nd - now that I have data/form coming up, I want to step to the 4th
item on the form. The following works, but it overwrites with a blank
the item that had been written in the first column. i.e. when the
data/form comes up, it no longer shows the information that had been in
the first item (first column of active row), even though there is a
value shown in the first column of the row on the sheet shown in
background. Of course, when data/form is closed, cell corresponding to
first item becomes blank. ANY IDEAS WHY DATA IS OVERWRITTEN?

SendKeys "{DOWN " & ActiveCell.Row - 2 & "}"
SendKeys "{TAB 3 }"

3rd - why do the key commands go before the window is opened by
ActiveSheet.ShowDataForm

when you do the task manually, you open data/form, AND THEN step down
with the DOWN key and steps across with the TAB key.

CAUTION - I don't think it matters, but about check out was done using
Excel95, as I've been limited to my laptop this weekend. Normally I use
desk top machine with Excel97.

Thanks in advance. Michael

Tom Ogilvy wrote:
So why do you bother to say you don't want to do it that way?

--
Regards,
Tom Ogilvy

buczacz wrote in message
...

Thanks ... will try.. Michael

Dave Peterson wrote:

But mimicing the down key isn't too difficult:

Option Explicit
Sub testme()

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

End Sub




buczacz wrote:


The data/form feature of Excel facilitates viewing and entering data
when the number of fields in a row / record are too numerous to be
viewed similtaneously because the extend off the left or right side of
the screen.

With an Excel spread sheet open, when one selects Data / Form from the
tool bar menu, Excel opens a form showing the field headings as well as
all the fields in the first record. One can then scroll down to any
row / record in the sheet.

Is there a simple way to get the form to show the current record. For
example, if a cell in row ten is currently selected, I'd like to be able
to open the form and have it show the contents of the fields in row ten.
I know that I could write a macro to:

Save the number of the current row,

Open the form, and

Mimic execution of down key strokes until the number of strokes equals
the row.

IS THERE A SIMPLER WAY that is compatible with Excel97?

Michael





--

Dave Peterson

Dave.. your formulation worked perfect
SendKeys "{DOWN " & ActiveCell.Row - 2 & "}{TAB 3}"

The formulation that I used which did not work was:
SendKeys "{DOWN " & ActiveCell.Row - 2 & "}"
SendKeys " {TAB 3}"

But building on your comment, when I eliminated the extra space in
front of the "{" making it
SendKeys "{TAB 3}"
the two line version worked as well.

My mistake was that I did not realize that the blank space would be sent
as a space key.

Thanks again. Michael


Dave Peterson wrote:
Your additional sendkeys worked fine for me--no data was overwritten (although I
had to delete that trailing space).

But so did this:

Option Explicit
Sub testme()

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

End Sub

And you're plopping some characters in a keyboard buffer so they better be there
when whatever is going to read them needs them.

(Sendkeys is not a solution of choice for most things. Lots can go wrong--maybe
you won't be in excel when the macro runs, so something else will get the down's
and tabs???)

But I used xl2002 and win98 (just so you know what I tested with).


buczacz wrote:

2nd - now that I have data/form coming up, I want to step to the 4th
item on the form. The following works, but it overwrites with a blank
the item that had been written in the first column. i.e. when the
data/form comes up, it no longer shows the information that had been in
the first item (first column of active row), even though there is a
value shown in the first column of the row on the sheet shown in
background. Of course, when data/form is closed, cell corresponding to
first item becomes blank. ANY IDEAS WHY DATA IS OVERWRITTEN?

SendKeys "{DOWN " & ActiveCell.Row - 2 & "}"
SendKeys "{TAB 3 }"

3rd - why do the key commands go before the window is opened by
ActiveSheet.ShowDataForm

when you do the task manually, you open data/form, AND THEN step down
with the DOWN key and steps across with the TAB key.

CAUTION - I don't think it matters, but about check out was done using
Excel95, as I've been limited to my laptop this weekend. Normally I use
desk top machine with Excel97.

Thanks in advance. Michael

Tom Ogilvy wrote:

So why do you bother to say you don't want to do it that way?

--
Regards,
Tom Ogilvy

buczacz wrote in message
...


Thanks ... will try.. Michael

Dave Peterson wrote:


But mimicing the down key isn't too difficult:

Option Explicit
Sub testme()

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

End Sub




buczacz wrote:



The data/form feature of Excel facilitates viewing and entering data
when the number of fields in a row / record are too numerous to be
viewed similtaneously because the extend off the left or right side of
the screen.

With an Excel spread sheet open, when one selects Data / Form from the
tool bar menu, Excel opens a form showing the field headings as well as
all the fields in the first record. One can then scroll down to any
row / record in the sheet.

Is there a simple way to get the form to show the current record. For
example, if a cell in row ten is currently selected, I'd like to be able
to open the form and have it show the contents of the fields in row ten.
I know that I could write a macro to:

Save the number of the current row,

Open the form, and

Mimic execution of down key strokes until the number of strokes equals
the row.

IS THERE A SIMPLER WAY that is compatible with Excel97?

Michael

That spacebar seems to jump around on you--sometimes leading, sometimes trailing
<vbg. Glad you got it working.

<<snipped

--

Dave Peterson