Ark68
Well-known Member
- Joined
- Mar 23, 2004
- Messages
- 4,564
- Office Version
- 365
- 2016
- Platform
- Windows
In my userform, I have a multipage control with 5 pages.
I have a multipage_change event that triggers as the user moves amongst the pages.
Each page has a list box.
In the mulipage_change event, that page's listbox is populated with default data (the default state). In my example 54 rows of data.
There is a toggle button associated with each listbox.
When the user clicks the toggle button (TRUE), code is executed to refine the data (filter) in the listbox. This filter reduces the listbox contents to 19 records.
When the user clicks the toggle button a second time (false), the listbox is to return to it's respective default state, in this case to show the original 54 records.
I am struggling to figure out how to return that page's listbox back to it's default state.
Here is my code:
Listbox5 is the listbox on page 0 (1st page) of multipage control 1. My thought was that 'Me.Multipage1.Value=0 would trigger the multipage_change event but evidently I thought wrong.
I have a multipage_change event that triggers as the user moves amongst the pages.
Each page has a list box.
In the mulipage_change event, that page's listbox is populated with default data (the default state). In my example 54 rows of data.
There is a toggle button associated with each listbox.
When the user clicks the toggle button (TRUE), code is executed to refine the data (filter) in the listbox. This filter reduces the listbox contents to 19 records.
When the user clicks the toggle button a second time (false), the listbox is to return to it's respective default state, in this case to show the original 54 records.
I am struggling to figure out how to return that page's listbox back to it's default state.
Here is my code:
Code:
Private Sub ToggleButton1_Click()
Dim llstrow As Long
Dim lbtarget As MSForms.ListBox
Dim rngSource As Range
Dim oneRow As Range
If ToggleButton1.Value = True Then
With ws_core
.Range("A:W").EntireColumn.Hidden = False
.AutoFilterMode = False
.Range("A1:V1").AutoFilter Field:=5, Criteria1:="=D*"
.Range("B:B,D:D,G:G,I:J,M:M,P:V").EntireColumn.Hidden = True
Set rnglist = .Range("A1:O" & .Cells(Rows.Count, "A").End(xlUp).Row).SpecialCells(xlCellTypeVisible)
End With
With ws_th
.Cells.ClearContents
rnglist.Copy ws_th.Range("A1")
.Rows(1).Delete
End With
llstrow = ws_th.Cells(Rows.Count, 1).End(xlUp).Row
MsgBox llstrow
Set rngSource = ws_th.Range("A2:G" & llstrow)
Set lbtarget = Me.ListBox5
With lbtarget
.Clear
.ColumnCount = 9
.ColumnWidths = "50;40;20;200;125;50;50;50;50"
For Each oneRow In rngSource.Rows
.AddItem oneRow.Range("A1").Text
.List(.ListCount - 1, 1) = oneRow.Range("B1").Text
.List(.ListCount - 1, 2) = oneRow.Range("C1").Text
.List(.ListCount - 1, 3) = oneRow.Range("D1").Text
.List(.ListCount - 1, 4) = oneRow.Range("E1").Text
.List(.ListCount - 1, 5) = oneRow.Range("F1").Text
.List(.ListCount - 1, 6) = oneRow.Range("G1").Text
.List(.ListCount - 1, 7) = oneRow.Range("H1").Text
.List(.ListCount - 1, 8) = oneRow.Range("I1").Text
Next oneRow
End With
Else
Me.ListBox5.Clear
Me.MultiPage1.Value = 0
End If
End Sub
Listbox5 is the listbox on page 0 (1st page) of multipage control 1. My thought was that 'Me.Multipage1.Value=0 would trigger the multipage_change event but evidently I thought wrong.