Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dataform - Forward/Backward Control
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dataform - Forward/Backward Control
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??? Jim May wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dataform - Forward/Backward Control
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. "Dave Peterson" wrote: 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??? Jim May wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dataform - Forward/Backward Control
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. -- Regards, Tom Ogilvy "Jim May" wrote in message news:Xhi0f.66$jw6.44@lakeread02... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dataform - Forward/Backward Control
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dataform - Forward/Backward Control
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 "Walt" wrote in message oups.com... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
brought forward & carried forward | Excel Worksheet Functions | |||
Backward and Forward Compatibility | Excel Discussion (Misc queries) | |||
Dataform | Excel Programming | |||
dataform | Excel Programming | |||
Control record displayed in DataForm window | Excel Programming |