Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Jan 2016
    Posts
    22
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Forcing A Multipage Change Event

    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 (all_dias), 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 , 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 all_dias_Click()    
        Dim llstrow As Long
        Dim lbtarget As MSForms.ListBox
        Dim rngSource As Range
        Dim oneRow As Range
        
        If all_dias.Value = True Then
        
            all_flds.Value = False
            all_crts.Value = False
            all_others.Value = False
            
            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.

    In all fairness, this has been crossposted here. I have not yet received any suggestions there, so I have turned to a different group of experienced folks that may have some advice.

  2. #2
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,640
    Thanks
    115
    Thanked 651 Times in 593 Posts
    Try this line to repopulate your listbox5 with all 54 rows

    ListBox1.RowSource = "A1:I55"

    HTH,
    Maud

  3. #3
    New Lounger
    Join Date
    Jan 2016
    Posts
    22
    Thanks
    4
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Maudibe View Post
    Try this line to repopulate your listbox5 with all 54 rows

    ListBox1.RowSource = "A1:I55"

    HTH,
    Maud
    I had to do a bit of adaptation, but this was the nudge I needed!
    Thank you Maud!!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •