+ Reply to Thread
Results 1 to 2 of 2

query/filter form

  1. #1
    Registered User
    Join Date
    07-09-2010
    Location
    Denver, Colorado
    MS-Off Ver
    Excel 2007
    Posts
    32

    query/filter form

    I would like the user to be able to search a worksheet via Userform2 by selecting criteria that include both input text and comboboxes. The data would then populate the listbox. In addition, when the user clicks an item on the list box, the Explanation text (column 6) will show in the texbox below so the written comments can be read. I am new to both programming and this forum but feel I have learned a lot since joining. I have researched some examples and have the existing date show in the listbox, but think the next steps are out of my league. Should filters be used or a dynamic array? Any code examples that could get me started would be most appreciated.
    Attached Files Attached Files

  2. #2
    Registered User
    Join Date
    07-09-2010
    Location
    Denver, Colorado
    MS-Off Ver
    Excel 2007
    Posts
    32

    Re: query/filter form

    Well I worked out some code but can't make it work. Is there any suggestions for improvement?
    Sub FilterCopyIssue()

    Dim strIssue As String
    Dim strDocType As String
    Dim strUsername As String
    Dim strLoanNum As String
    Dim strDate As String
    Dim ColCnt As Integer
    Dim Rng As Range
    Dim Rng2 As Range
    Dim cw As String
    Dim c As Integer

    strUsername = UserForm2.ComboBox1
    strDocType = UserForm2.ComboBox2
    strIssue = UserForm2.ComboBox3
    strLoanNum = UserForm2.TextBox1
    strDate = UserForm2.TextBox2
    Set Rng2 = Workbooks("Owner").UsedRange


    If UserForm2.ComboBox1.Value = True Then
    Rng2.Select
    Selection.AutoFilter
    ActiveSheet.Rng2AutoFilter Field:=3, Criteria1:=strUsername

    Else

    End If

    If UserForm2.ComboBox2.Value = True Then
    Rng2.Select
    Selection.AutoFilter
    ActiveSheet.Rng2.AutoFilter Field:=4, Criteria1:=strDocType
    Else

    End If

    If UserForm2.ComboBox3.Value = True Then
    Rng2.Select
    Selection.AutoFilter
    ActiveSheet.Rng2.AutoFilter Field:=5, Criteria1:=strIssue
    Else

    End If

    If UserForm2.TextBox1.Value = True Then
    Rng2.Select
    Selection.AutoFilter
    Worksheets("Owner").UsedRamge.AutoFilter Field:=1, Criteria1:=strLoanNum
    Else

    End If

    If UserForm2.TextBox2.Value = True Then
    Rng2.Select
    Selection.AutoFilter
    ActiveSheet.Rng2.AutoFilter Field:=2, Criteria1:=strDate
    Else

    End If

    Selection.Copy
    Sheets("Sheet4").Select
    Range("A1").Select
    ActiveSheet.Paste


    ColCnt = Worksheets("Sheet4").UsedRange.Columns.Count
    Set Rng = Worksheets("Sheet4").UsedRange

    With UserForm2.ListBox1
    .ColumnCount = ColCnt
    .RowSource = Rng.Address
    cw = ""
    For c = 1 To .ColumnCount
    cw = cw & Rng.Columns(c).Width & ";"
    Next c
    .ColumnWidths = cw
    .ListIndex = 0
    End With



    End Sub

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1