Number showing row location when you scroll

  • Thread starter Thread starter Cimjet
  • Start date Start date
C

Cimjet

Hi everyone
I've got a Userform with a Spinbutton that allows me to scroll up and down and
view my list from the database.
Same thing used in XL2003 Data>Form with the buttons Prev. and Next.
On that Userform, they have a counter that shows you at what row you're at. just
above the buttons something like this 3 of 950 and would like to know
how to do that.
I've tried different thing but no success. could someone give me some codes or
direct me in the right direction.
Regards
Cimjet
 
Cimjet presented the following explanation :
Hi everyone
I've got a Userform with a Spinbutton that allows me to scroll up and down
and view my list from the database.
Same thing used in XL2003 Data>Form with the buttons Prev. and Next.
On that Userform, they have a counter that shows you at what row you're at.
just above the buttons something like this 3 of 950 and would like to
know how to do that.
I've tried different thing but no success. could someone give me some codes
or direct me in the right direction.
Regards
Cimjet
Click to expand...

The counter in DataForm is actually taking the header row into account,
and so the row number is the record number+1. IOW, '3 of 950' is row4
of 951 rows of data because the header row is row1.

So.., if you have yur spinbutton working then you can dupe display of
the 'counter' in a label by matching the position of the spinbutton to
the current row-1, and set the total records to 'Rows.Count-1'.
 
Hi Garry
Thank you for your reply.
I tried different things without success, could you show me some codes I could
work with.
Yes, my Spinbutton is working. It always start at the top, at my Heading and
thats ok.
Regards
Cimjet
 
It happens that Cimjet formulated :
Hi Garry
Thank you for your reply.
I tried different things without success, could you show me some codes I
could work with.
Yes, my Spinbutton is working. It always start at the top, at my Heading and
thats ok.
Regards
Cimjet
Click to expand...

Can you post your spinbutton code so I can see what it's doing?
 
Thanks Garry
Here it is :

Private Sub SpinButton2_Change()
Dim C As Range
Dim rSearch As Range
Dim strFind As String

Application.ScreenUpdating = False
Set rSearch = Range("A2:A985").Cells(Me.SpinButton2.Max - Me.SpinButton2.Value
+ 1, 1)
Me.TextBox11.Value = ("*")
strFind = Me.TextBox11.Value
On Error Resume Next
Set C = rSearch.Find(strFind, LookIn:=xlValues)
On Error GoTo 0

If Not C Is Nothing Then
With Me 'load entry to form
.TextBox1.Value = C.Offset(0, 0).Value
.TextBox2.Value = C.Offset(0, 1).Value
.TextBox3.Value = C.Offset(0, 2).Value
.TextBox4.Value = C.Offset(0, 3).Value
.TextBox5.Value = C.Offset(0, 4).Value
.TextBox6.Value = C.Offset(0, 5).Value
.TextBox7.Value = C.Offset(0, 6).Value
.TextBox8.Value = C.Offset(0, 7).Value
.TextBox9.Value = C.Offset(0, 8).Value
.TextBox10.Value = C.Offset(0, 9).Value
End With
End If
Application.ScreenUpdating = True
End Sub
Regards
John
 
Hi John,
Try this after adding a label named "lblRecordCount"...

Private Sub SpinButton2_Change()
Dim C As Range, rngSource As Range
Dim lPos As Long

Const strFind As String = "*"

With Me.SpinButton2: lPos = (.Max - .Value + 1): End With
Set rngSource = Range("A2:A985")
Me.lblRecordCount.Caption = CStr(lPos) & " of " _
& CStr(rngSource.Rows.Count)
Me.TextBox11.Value = strFind

Set C = rngSource.Cells(lPos, 1).Find(strFind, LookIn:=xlValues)
If Not C Is Nothing Then
Application.ScreenUpdating = False
With Me 'load entry to form
.TextBox1.Value = C.Offset(0, 0).Value
.TextBox2.Value = C.Offset(0, 1).Value
.TextBox3.Value = C.Offset(0, 2).Value
.TextBox4.Value = C.Offset(0, 3).Value
.TextBox5.Value = C.Offset(0, 4).Value
.TextBox6.Value = C.Offset(0, 5).Value
.TextBox7.Value = C.Offset(0, 6).Value
.TextBox8.Value = C.Offset(0, 7).Value
.TextBox9.Value = C.Offset(0, 8).Value
.TextBox10.Value = C.Offset(0, 9).Value
End With
Application.ScreenUpdating = True
End If
End Sub
 
Back
Top