PDA

View Full Version : Solved: Form Navigation Buttons



lifeson
11-22-2007, 06:15 AM
:help
Is it possible to have a user form which when launched shows a row of data in seperate text boxes ( I know hoe to do that bit :thumb ) but also has a set of next record/previous record navigation buttons that would show the next row of data - like on an access form? :dunno


Thanks in advance

Bob Phillips
11-22-2007, 07:06 AM
Yep. You would need to store the last record number and just increment it. If the incremented number is not greater than the data max then display that record.

Similarly for the previous.

lifeson
11-22-2007, 08:57 AM
XLD
Thanks for the tip
I have had a play about and come up with this
It works but probably not in the best way

I also want the option to print the form which I can do, but how would I xode the print all option?

Norie
11-22-2007, 09:48 AM
Why not just print the actual worksheet?

Userforms can of course be printed but they aren't really meant for that.:)

lifeson
11-22-2007, 09:49 AM
Why not just print the actual worksheet?

Userforms can of course be printed but they aren't really meant for that.:)

All the data is on one row for each record and there are about 50 columns
When printed one row would need 3 or 4 sheets
If I lay it out on a form I can get it on a A4 sheet.

Bob Phillips
11-22-2007, 10:39 AM
Option Explicit

Private ItemIndex As Long
Private ItemCount As Long

Private Sub cmdClose_Click()
Unload Me
End Sub

Private Sub cmdFirst_Click()
ItemIndex = 1
Call DisplayDetails
End Sub

Private Sub cmdLast_Click()
Dim dcount As Double
ItemIndex = ItemCount
Call DisplayDetails
End Sub

Private Sub cmdNext_Click()
Dim nxtrow As Long
Dim count As Long

ItemIndex = ItemIndex + 1
Call DisplayDetails
End Sub

Private Sub cmdPrev_Click()
Dim nxtrow As Long

ItemIndex = ItemIndex - 1
Call DisplayDetails
End Sub

Private Sub cmdPrintCurrent_Click()
FrmCompMaint.PrintForm
End Sub

Private Sub UserForm_Initialize()
Dim count As Double
Dim row As Double

'select the correct worksheet
Sheets("TblComponentDetail").Select

'identify which row is selected
ItemIndex = 1
'count how many records
ItemCount = Application.WorksheetFunction.CountA(Range("A:A")) - 1

Call DisplayDetails
End Sub

Private Sub DisplayDetails()

Me.Caption = "Record " & ItemIndex & " of " & ItemCount

With Worksheets("TblComponentDetail")

Me.txtDVD.Value = ItemIndex
Me.txtPrem.Value = .Cells(ItemIndex + 1, 3).Value
Me.txtGroup.Value = .Cells(ItemIndex + 1, 4).Value
Me.txtType.Value = .Cells(ItemIndex + 1, 5).Value
Me.txtDescription.Value = .Cells(ItemIndex + 1, 6).Value
Me.txtMake.Value = .Cells(ItemIndex + 1, 7).Value

Me.txtQuoteDesc.Value = .Cells(ItemIndex + 1, 32).Value
End With

With Me

.cmdPrev.Enabled = ItemIndex <> 1
.cmdNext.Enabled = ItemIndex <> ItemCount
End With
End Sub

lifeson
11-22-2007, 11:49 AM
The magnificent XLD strikes again :thumb :thumb

So much more sensible than my version :(

Do you think the "Print All" option can be done?

I have amended the print record option to prevent the controls and backcolor being printed
Private Sub cmdPrintCurrent_Click()
Dim Ctrl As Control
For Each Ctrl In Me.Controls
If TypeName(Ctrl) = "CommandButton" Then Ctrl.Visible = False
Next Ctrl
With FrmCompMaint
.BackColor = &H80000009
.PrintForm
.BackColor = &HC0FFFF
End With
For Each Ctrl In Me.Controls
If TypeName(Ctrl) = "CommandButton" Then Ctrl.Visible = True
Next Ctrl
End Sub

Bob Phillips
11-22-2007, 12:50 PM
Maybe a bit flashy, but you could so



Private Sub cmdPrintAll_Click()
Dim SaveIndex As Long
SaveIndex = ItemIndex
For ItemIndex = 1 To ItemCount
Call DisplayDetails
Next ItemIndex

ItemIndex SaveIndex
Call DisplayDetails
End Sub



__________________________________________
UK Cambridge XL Users Conference 29-30 Nov
http://www.exceluserconference.com/UKEUC.html

Norie
11-22-2007, 01:45 PM
lifeson

Have you considered a Word mail merge?

lifeson
11-23-2007, 02:03 AM
lifeson

Have you considered a Word mail merge?

Norie, Good idea!:clap: another way of skinning the cat

XLD
As usual Thanks for the help
I ended up with this for the print all option
Private Sub cmdPrintAll_Click()
Dim ctrl As Control
Dim SaveIndex As Long
SaveIndex = ItemIndex
For Each ctrl In Me.Controls
If TypeName(ctrl) = "CommandButton" Then ctrl.Visible = False
Next ctrl

Application.ScreenUpdating = False
For ItemIndex = 1 To ItemCount
MsgBox ItemIndex & " of " & ItemCount
With FrmCompMaint
.BackColor = &H80000009
.PrintForm
End With
Call DisplayDetails
Next ItemIndex

For Each ctrl In Me.Controls
If TypeName(ctrl) = "CommandButton" Then ctrl.Visible = True
Next ctrl
Application.ScreenUpdating = True
ItemIndex = SaveIndex
Call DisplayDetails
End Sub