What is a clean way of controlling Scroll bars when using filters with Userforms?
 

What are some clean ways of handling Record xxx of yyy, and also scroll bar
functions if you are turning on and off filters and skipping hidden (the
recognition of hidden is controlled by a button) I have some issues (they
are minor compared to the functionality I have gained by controlling hidden
rows!) issues with out of ranges, and beggining of file etc with my current
scroll and record count/display functions.

Here is the code I have for controlling the filters.

Dim FiltOn As Boolean

Private Sub CommandButton38_Click()
FiltOn = True

End Sub

Private Sub CommandButton39_Click()
FiltOn = False

End Sub
Private Sub SpinButton1_SpinDown()
ActiveCell.Offset(1, 0).Select
If FiltOn Then
Do While Selection.Rows.Hidden = True
ActiveCell.Offset(1, 0).Select
Loop
End If

SetForeColor 'conditional formating based on value of offset 43
FillMyForm1
End Sub


Private Sub SpinButton1_Spinup()
ActiveCell.Offset(-1, 0).Select
If FiltOn Then

Do While Selection.Rows.Hidden = True
ActiveCell.Offset(-1, 0).Select
Loop
End If

SetForeColor 'conditional formating based on value of offset 43
FillMyForm1
End Sub



Thanks!
Bruce

What is a clean way of controlling Scroll bars when using filters with Userforms?
 

Sub AA_Tester12()
Dim rng As Range
Dim rng1 As Range
Dim rng2 As Range
Set rng = ActiveSheet.AutoFilter.Range
Set rng = rng.Offset(1, 0).Resize(rng.Rows.Count - 1, 1)
On Error Resume Next
Set rng1 = rng.SpecialCells(xlVisible)
On Error GoTo 0
If rng1 Is Nothing Then
MsgBox "0 of " & rng.Count & " rows visible"
Else
MsgBox rng1.Count & " of " & rng.Count & " rows visible"
If Not Intersect(ActiveCell.EntireRow, rng1) Is Nothing Then
Set rng2 = Intersect(ActiveCell.EntireRow, rng1)
recno = Application.Subtotal(3, Range(rng1(1), rng2))
MsgBox "rec selected is " & recno & " of " & rng1.Count & " visible"
End If
End If

End Sub


What kind of scrollbar are you using? Control toolbox toolbar or Forms
toolbar?

--
Regards,
Tom Ogilvy

"Bruccce" wrote in message
et...
What are some clean ways of handling Record xxx of yyy, and also scroll
bar
functions if you are turning on and off filters and skipping hidden (the
recognition of hidden is controlled by a button) I have some issues (they
are minor compared to the functionality I have gained by controlling
hidden
rows!) issues with out of ranges, and beggining of file etc with my
current
scroll and record count/display functions.

Here is the code I have for controlling the filters.

Dim FiltOn As Boolean

Private Sub CommandButton38_Click()
FiltOn = True

End Sub

Private Sub CommandButton39_Click()
FiltOn = False

End Sub
Private Sub SpinButton1_SpinDown()
ActiveCell.Offset(1, 0).Select
If FiltOn Then
Do While Selection.Rows.Hidden = True
ActiveCell.Offset(1, 0).Select
Loop
End If

SetForeColor 'conditional formating based on value of offset 43
FillMyForm1
End Sub


Private Sub SpinButton1_Spinup()
ActiveCell.Offset(-1, 0).Select
If FiltOn Then

Do While Selection.Rows.Hidden = True
ActiveCell.Offset(-1, 0).Select
Loop
End If

SetForeColor 'conditional formating based on value of offset 43
FillMyForm1
End Sub



Thanks!
Bruce

What is a clean way of controlling Scroll bars when using filters with Userforms?
 

If you put it in design mode using the control toolbox toolbar and right
click on it and you have a choice for properties, then it is from the
toolbox.

or right click on the sheet tab an select view code. In the left dropdown
at the top of the module, the name of the scrollbar should appear there.

So what do you want to do with the scroll bar - you want to scroll through
the visible records? It selects a record relative to its position on the
scrollbar where the scrollbar represents visible records?

--
Regards,
Tom Ogilvy

"Bruccce" wrote in message
news:4755b.336403$uu5.67964@sccrnsc04...
What kind of scrollbar are you using? Control toolbox toolbar or
Forms
toolbar?
I "believe" toolbox, how can I verify?

Thanks!
Bruce



"Tom Ogilvy" wrote in message
...
Sub AA_Tester12()
Dim rng As Range
Dim rng1 As Range
Dim rng2 As Range
Set rng = ActiveSheet.AutoFilter.Range
Set rng = rng.Offset(1, 0).Resize(rng.Rows.Count - 1, 1)
On Error Resume Next
Set rng1 = rng.SpecialCells(xlVisible)
On Error GoTo 0
If rng1 Is Nothing Then
MsgBox "0 of " & rng.Count & " rows visible"
Else
MsgBox rng1.Count & " of " & rng.Count & " rows visible"
If Not Intersect(ActiveCell.EntireRow, rng1) Is Nothing Then
Set rng2 = Intersect(ActiveCell.EntireRow, rng1)
recno = Application.Subtotal(3, Range(rng1(1), rng2))
MsgBox "rec selected is " & recno & " of " & rng1.Count & "
visible"
End If
End If

End Sub


What kind of scrollbar are you using? Control toolbox toolbar or Forms
toolbar?

--
Regards,
Tom Ogilvy

"Bruccce" wrote in message
et...
What are some clean ways of handling Record xxx of yyy, and also
scroll
bar
functions if you are turning on and off filters and skipping hidden
(the
recognition of hidden is controlled by a button) I have some issues
(they
are minor compared to the functionality I have gained by controlling
hidden
rows!) issues with out of ranges, and beggining of file etc with my
current
scroll and record count/display functions.

Here is the code I have for controlling the filters.

Dim FiltOn As Boolean

Private Sub CommandButton38_Click()
FiltOn = True

End Sub

Private Sub CommandButton39_Click()
FiltOn = False

End Sub
Private Sub SpinButton1_SpinDown()
ActiveCell.Offset(1, 0).Select
If FiltOn Then
Do While Selection.Rows.Hidden = True
ActiveCell.Offset(1, 0).Select
Loop
End If

SetForeColor 'conditional formating based on value of offset 43
FillMyForm1
End Sub


Private Sub SpinButton1_Spinup()
ActiveCell.Offset(-1, 0).Select
If FiltOn Then

Do While Selection.Rows.Hidden = True
ActiveCell.Offset(-1, 0).Select
Loop
End If

SetForeColor 'conditional formating based on value of offset 43
FillMyForm1
End Sub



Thanks!
Bruce

What is a clean way of controlling Scroll bars when using filters with Userforms?
 

Range(1st record row, current record row).rows.count

Assume data is in A1

Assume first data item is in A2

Range("A2",ActiveCell).Rows.count

? Range("A2",ActiveCell).Rows.count
17

--
Regards,
Tom Ogilvy


"Bruccce" wrote in message
news:YR55b.333863$o%2.153482@sccrnsc02...
Tom,
How would I change this to show the recno of the current when the filters
are disabled?


Thanks!
Bruce

"Tom Ogilvy" wrote in message
...
Sub AA_Tester12()
Dim rng As Range
Dim rng1 As Range
Dim rng2 As Range
Set rng = ActiveSheet.AutoFilter.Range
Set rng = rng.Offset(1, 0).Resize(rng.Rows.Count - 1, 1)
On Error Resume Next
Set rng1 = rng.SpecialCells(xlVisible)
On Error GoTo 0
If rng1 Is Nothing Then
MsgBox "0 of " & rng.Count & " rows visible"
Else
MsgBox rng1.Count & " of " & rng.Count & " rows visible"
If Not Intersect(ActiveCell.EntireRow, rng1) Is Nothing Then
Set rng2 = Intersect(ActiveCell.EntireRow, rng1)
recno = Application.Subtotal(3, Range(rng1(1), rng2))
MsgBox "rec selected is " & recno & " of " & rng1.Count & "
visible"
End If
End If

End Sub


What kind of scrollbar are you using? Control toolbox toolbar or Forms
toolbar?

--
Regards,
Tom Ogilvy

"Bruccce" wrote in message
et...
What are some clean ways of handling Record xxx of yyy, and also
scroll
bar
functions if you are turning on and off filters and skipping hidden
(the
recognition of hidden is controlled by a button) I have some issues
(they
are minor compared to the functionality I have gained by controlling
hidden
rows!) issues with out of ranges, and beggining of file etc with my
current
scroll and record count/display functions.

Here is the code I have for controlling the filters.

Dim FiltOn As Boolean

Private Sub CommandButton38_Click()
FiltOn = True

End Sub

Private Sub CommandButton39_Click()
FiltOn = False

End Sub
Private Sub SpinButton1_SpinDown()
ActiveCell.Offset(1, 0).Select
If FiltOn Then
Do While Selection.Rows.Hidden = True
ActiveCell.Offset(1, 0).Select
Loop
End If

SetForeColor 'conditional formating based on value of offset 43
FillMyForm1
End Sub


Private Sub SpinButton1_Spinup()
ActiveCell.Offset(-1, 0).Select
If FiltOn Then

Do While Selection.Rows.Hidden = True
ActiveCell.Offset(-1, 0).Select
Loop
End If

SetForeColor 'conditional formating based on value of offset 43
FillMyForm1
End Sub



Thanks!
Bruce

What is a clean way of controlling Scroll bars when using filters with Userforms?
 

It is from the Toolbox!
I would like it to select the visable records.

"Tom Ogilvy" wrote in message
...
If you put it in design mode using the control toolbox toolbar and right
click on it and you have a choice for properties, then it is from the
toolbox.

or right click on the sheet tab an select view code. In the left dropdown
at the top of the module, the name of the scrollbar should appear there.

So what do you want to do with the scroll bar - you want to scroll through
the visible records? It selects a record relative to its position on the
scrollbar where the scrollbar represents visible records?

--
Regards,
Tom Ogilvy

"Bruccce" wrote in message
news:4755b.336403$uu5.67964@sccrnsc04...
What kind of scrollbar are you using? Control toolbox toolbar or
Forms
toolbar?
I "believe" toolbox, how can I verify?

Thanks!
Bruce



"Tom Ogilvy" wrote in message
...
Sub AA_Tester12()
Dim rng As Range
Dim rng1 As Range
Dim rng2 As Range
Set rng = ActiveSheet.AutoFilter.Range
Set rng = rng.Offset(1, 0).Resize(rng.Rows.Count - 1, 1)
On Error Resume Next
Set rng1 = rng.SpecialCells(xlVisible)
On Error GoTo 0
If rng1 Is Nothing Then
MsgBox "0 of " & rng.Count & " rows visible"
Else
MsgBox rng1.Count & " of " & rng.Count & " rows visible"
If Not Intersect(ActiveCell.EntireRow, rng1) Is Nothing Then
Set rng2 = Intersect(ActiveCell.EntireRow, rng1)
recno = Application.Subtotal(3, Range(rng1(1), rng2))
MsgBox "rec selected is " & recno & " of " & rng1.Count & "
visible"
End If
End If

End Sub


What kind of scrollbar are you using? Control toolbox toolbar or
Forms
toolbar?

--
Regards,
Tom Ogilvy

"Bruccce" wrote in message
et...
What are some clean ways of handling Record xxx of yyy, and also
scroll
bar
functions if you are turning on and off filters and skipping hidden
(the
recognition of hidden is controlled by a button) I have some issues
(they
are minor compared to the functionality I have gained by controlling
hidden
rows!) issues with out of ranges, and beggining of file etc with my
current
scroll and record count/display functions.

Here is the code I have for controlling the filters.

Dim FiltOn As Boolean

Private Sub CommandButton38_Click()
FiltOn = True

End Sub

Private Sub CommandButton39_Click()
FiltOn = False

End Sub
Private Sub SpinButton1_SpinDown()
ActiveCell.Offset(1, 0).Select
If FiltOn Then
Do While Selection.Rows.Hidden = True
ActiveCell.Offset(1, 0).Select
Loop
End If

SetForeColor 'conditional formating based on value of offset 43
FillMyForm1
End Sub


Private Sub SpinButton1_Spinup()
ActiveCell.Offset(-1, 0).Select
If FiltOn Then

Do While Selection.Rows.Hidden = True
ActiveCell.Offset(-1, 0).Select
Loop
End If

SetForeColor 'conditional formating based on value of offset 43
FillMyForm1
End Sub



Thanks!
Bruce