Current row/record shown when opening data/form

  • Thread starter Thread starter buczacz
  • Start date Start date
B

buczacz

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.
 
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
 
Thanks ... will try.. Michael

Dave said:
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
Click to expand...
 
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
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
 
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).
 
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
 
That spacebar seems to jump around on you--sometimes leading, sometimes trailing
<vbg>. Glad you got it working.

<<snipped>>
 
Back
Top