Number showing row location when you scroll
 

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

Number showing row location when you scroll
 

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

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'.

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

Number showing row location when you scroll
 

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
"GS" wrote in message ...
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 DataForm 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

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'.

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

Number showing row location when you scroll
 

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

Can you post your spinbutton code so I can see what it's doing?

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

Number showing row location when you scroll
 

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
"GS" wrote in message ...
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

Can you post your spinbutton code so I can see what it's doing?

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

Number showing row location when you scroll
 

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

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

Number showing row location when you scroll
 

Hi Garry
You're terrific, it work great.
Thanks a million.
Cimjet
"GS" wrote in message ...
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

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

Number showing row location when you scroll
 

You are very welcome! Always glad to be of help...

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc