Dataform - Forward/Backward Control

[Jim May]

In Excel is there a way of entering controls on a form
to flip through the records of a table of data. I now
am loading the first record in the table into my form.
I just want to be able to <<using a control>> advance
to record 2,3,4,etc.. and 4,3,2,1
Can it be done? If so how..?
TIA,
Jim

 

[Dave Peterson]

Maybe something like:

Option Explicit
Sub testme()

SendKeys "%w"
Application.DisplayAlerts = False
ActiveSheet.ShowDataForm
Application.DisplayAlerts = True

End Sub

(The shortcut for a New record is alt-w (%w in Sendkeys syntax).)

or...

You can use Sendkeys to get to the row of the activecell you want.

Option Explicit
Sub testme2()

'tabs to the third field in that row
'SendKeys "{DOWN " & ActiveCell.Row - 2 & "}{TAB 3}"

'or just to the first field
SendKeys "{DOWN " & ActiveCell.Row - 2 & "}"
Application.DisplayAlerts = False
ActiveSheet.ShowDataForm
Application.DisplayAlerts = True

End Sub

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???

 

[Guest]

If you know the location of your first cell, then you can use a button, to
cause the activecell to change by one direction or another, simply by adding
one or subtracting one. But you have to test to see if you have reached the
end of your data set. I.e. if there is nothing and there should be, you must
decide if you want to loop back to the "beginning" or stop incrementing. If
incrementing in the other direction causes the "row" to be at or near say
zero, then must again decide.

Basically choose to select a new cell that meets the row/column criteria of
the new desired direction. Possible? Yeah.

 

[Tom Ogilvy]

If you talking about a userform, there is no built in support for it. You
put buttons on the forms and write the code in the click event to update
your controls that display the data.

 

[Walt]

Hi Jim,

There's an excellent reference describing in detail how to do this.
I've used variations of it in several instances and it works
beautifully. It's not trivial and not for the timid (my opinion).
Try:

WROX Press Ltd.'s

Excel 2002 VBA Programmer's Reference
by: Steven Bullen, John Green, Rob Bovey, and Robert Rosenberg

Chapter 13 section (5) Maintaining a Data List (Pages 243-249)

Give some thought to the authors' proviso about using a database
application instead.

I think my copy is slightly out of date, but I imagine an Excel 2003
version would have this too.

Best Regards,
Walt

 

[Jim May]

Walt, thanks -- I actually HAVE the Wrox Excel 2002 VBA book! -- Had
forgotten to consult it. I'm now on page 243...
Tks again,
Jim