Consulting

Results 1 to 7 of 7

Thread: Auto Filter Data from Multiple Worksheets Using only One Combobox

  1. #1

    Auto Filter Data from Multiple Worksheets Using only One Combobox

    Hello All,
    If someone could help out with this code, I’d be forever grateful. I have a userform with a listbox, and two comboboxes. The first combobox (combobox1) let’s the user select from one of the many worksheets in the workbook. The second combobox (combobox2) is supposed to autofilter the data in the worksheet from a particular column as well as in the listbox. The problem I have is two-fold. (1) When the filter is applied the listbox clears, and (2) I’m only able to filter data in the first worksheet. How do I get the listbox to display the filtered data and also, how can I filter the data based on the sheet selected as apposed to just the first worksheet. The codes are below and I’ve also attached the workbook. Any assistance is greatly appreciated.
    Roro

     Private Sub UserForm_Initialize()
        Dim i As Integer   
        For i = 2 To Sheets.Count
        Me.ComboBox1.AddItem Sheets(i).Name
        Next i
        Me.ListBox1.ColumnWidths = "5;70;70;80;80;80;150;55;70;150;85"
        Me.ComboBox2.List = Array("ALPHA", "BRAVO", "CHARLIE")
    End Sub
     Private Sub Combobox1_Change()
        Dim LR As Long, LC As Long   
        Sheets(ComboBox1.Value).Activate   
        Me.ListBox1.Clear
        If Me.ComboBox1.ListIndex = -1 Then Exit Sub   
        With Sheets(Me.ComboBox1.Value)
            LR = .Range("B" & Rows.Count).End(xlUp).Row
            LC = .ListObjects(1).ListColumns.Count + 1
            With .Range("A2", .Cells(LR, LC))       
                Me.ListBox1.ColumnCount = .Columns.Count
                Me.ListBox1.List = .Value
            End With
        End With   
    End Sub
     Private Sub ComboBox2_Change()
    Dim database(1 To 100, 1 To 10)
    Dim my_range As Integer
    Dim column As Byte
    On Error Resume Next
    Sheet2.Range("B4").AutoFilter field:=5, Criteria1:=Me.ComboBox2.Value
    For i = 2 To Sheet2.Range("B10000").End(x1up).Row
    If Sheet2.Cells(i, 1) = Me.ComboBox2 Then
    my_range = my_range + 1
    For column = 1 To 10
    database(my_range, column) = Sheet2.Cells(i, column)
    Next column
    End If
    Next i
    Me.ListBox1.List = database
    End Sub
    Attached Files Attached Files

  2. #2
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,635
    Structuring precedes coding.
    Attached Files Attached Files

  3. #3
    hi snb,

    thanks for the response, this is exactly what i was trying to achieve. however, now the other features in the workbook, such as the listbox click event used to populate the textboxes for editing and deleting don't work. i'd like to keep those features. thank you.

     Private Sub ListBox1_Click()
      For I = 1 To NbCol
        tmp = Me.ListBox1.Column(I - 1)
        If Not IsError(tmp) Then Me("textbox" & I) = tmp
      Next I
       Label11 = ListBox1.ListIndex + 1
    End Sub

  4. #4
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,635
    See
    Attached Files Attached Files

  5. #5

    Auto Filter Data from Multiple Worksheets Using only One Combobox

    snb,

    soo close! I added the "update" and "delete" code with suggested changes. the "delete works fine, but the "update" doesn't, rather it just create a duplicate copy of what i'm trying to update I've attached the revised workbook. thanks for your patience on this.
    Attached Files Attached Files

  6. #6
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,635
    I posted the workbook primarily to illustrate the method (the 'how') of creating a userform as UI for a 'database'. (see also: https://www.snb-vba.eu/VBA_Userform_in_database_en.html).
    I am more curious what you think of this 'how' instead of the results. I thought you might be interested in improving your VBA skills.

  7. #7
    was trying to achieve both. I do appreciate your time and I hope I haven't inconvenience you too much.

Posting Permissions

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