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

  • Thread starter Thread starter Bruccce
  • Start date Start date
B

Bruccce

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
 
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?
 
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?
 
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
 
Back
Top