Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
scroll bars | Excel Worksheet Functions | |||
Scroll bars | Excel Discussion (Misc queries) | |||
Controlling Userforms | New Users to Excel | |||
Scroll bars | Excel Discussion (Misc queries) | |||
Help with Userforms and filters or autofilters | Excel Programming |