Results 1 to 2 of 2

Thread: Multiple Scripting Libraries?
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Mar 2015
    Posts
    42
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Multiple Scripting Libraries?

    I don't think this is possible, but I'm sure there is a way to do what i'm trying to do. I have a user form. The form has 2 checkboxes, 2 option buttons, 2 text boxes, and 1 combo box. The text boxes are enabled/disabled by the checkboxes. The contents of the combo box change based on the two option buttons. I have a database connection through power query that imports a table, about 80,000 items. The table has 5 columns. PID, Description, Cost, Vendor #, and Vendor Name. I want to be able to search using all 3 criteria. I currently have it working being able to use either text box and either radio button, but the code is not efficient, and I have 4 different sections, one for each combination. What I have now is below, but it is using option buttons and one text box instead of 2 checkboxes and 2 textboxes. In simple terms, I want to be able to do this.

    If checkbox1 = true then
    with createobjects("scripting.dictionary")


    Code:
    Private Sub PSFormCmdButton1_Click()Dim Search As ListObject
    Dim cl As Range
    Dim i As Integer
    Dim a As Integer
    
    
    
    
    Set Search = Sheets("Price Builder").ListObjects("SearchResults")
    
    
    
    
    Sheets("Price Builder").Range("D7").Select
    ActiveSheet.Unprotect "Passwrd"
        
    If ProductSearchForm.PSFormTextBox1.Value = "" Then
        If ProductSearchForm.PSFormComboBox1.Value = "" Then
            Sheets("Price Builder").Range("N7", Sheets("Price Builder").Range("N" & Rows.Count).End(xlUp)).ClearContents
            Search.Resize Search.Range.Resize(61)
            ProductSearchForm.Hide
            GoTo LockSheet
        End If
    End If
        
    If PSFormRadio1.Value = True Then
        i = 0
        Sheets("Price Builder").Range("N7", Sheets("Price Builder").Range("N" & Rows.Count).End(xlUp)).ClearContents
        If PSFormRadio3.Value = True Then
            With CreateObject("scripting.dictionary")
            For Each cl In Sheets("Price File").Range("A2", Sheets("Price File").Range("A" & Rows.Count).End(xlUp))
            If InStr(1, cl, ProductSearchForm.PSFormTextBox1.Value, vbTextCompare) Then
                If ProductSearchForm.PSFormComboBox1.Value = "" Then
                    i = i + 1
                    ElseIf InStr(1, cl.Offset(0, 3), ProductSearchForm.PSFormComboBox1.Value, vbTextCompare) Then
                    i = i + 1
                End If
            End If
            Next cl
            For Each cl In Sheets("Price File").Range("A2", Sheets("Price File").Range("A" & Rows.Count).End(xlUp))
            If InStr(1, cl, ProductSearchForm.PSFormTextBox1.Value, vbTextCompare) Then
                If ProductSearchForm.PSFormComboBox1.Value = "" Then
                    a = 1
                    ElseIf InStr(1, cl.Offset(0, 3).Value, ProductSearchForm.PSFormComboBox1.Value, vbTextCompare) Then
                    a = 1
                End If
            End If
            If a > 0 Then .Add cl.Value, Nothing
            a = 0
            Next cl
            If i > 250 Then
                If MsgBox((i) & " Results Found. Do you want to continue?" & vbNewLine & "This could take a long time", vbYesNo + vbInformation, "Application Message") = vbYes Then
                    Sheets("Price Builder").Range("N7").Resize(i).Value = Application.Transpose(.Keys)
                    Sheets("Price Builder").Range("N7").Resize(i).Name = "Lst"
                    Search.Resize Search.Range.Resize(i + 1)
                    Else
                    ProductSearchForm.Hide
                    GoTo LockSheet
                    End If
                ElseIf i > 60 And i <= 250 Then
                Sheets("Price Builder").Range("N7").Resize(i).Value = Application.Transpose(.Keys)
                Sheets("Price Builder").Range("N7").Resize(i).Name = "Lst"
                Search.Resize Search.Range.Resize(i + 1)
                ElseIf i > 0 And i <= 60 Then
                Sheets("Price Builder").Range("N7").Resize(i).Value = Application.Transpose(.Keys)
                Sheets("Price Builder").Range("N7").Resize(i).Name = "Lst"
                Search.Resize Search.Range.Resize(60)
                Else: GoTo NotFound
            End If
            End With
            ProductSearchForm.Hide
            GoTo LockSheet
                
            
            ElseIf PSFormRadio4.Value = True Then
            With CreateObject("scripting.dictionary")
            For Each cl In Sheets("Price File").Range("A2", Sheets("Price File").Range("A" & Rows.Count).End(xlUp))
            If InStr(1, cl, ProductSearchForm.PSFormTextBox1.Value, vbTextCompare) Then
                If ProductSearchForm.PSFormComboBox1.Value = "" Then
                    i = i + 1
                    ElseIf InStr(1, cl.Offset(0, 4).Value, ProductSearchForm.PSFormComboBox1.Value, vbTextCompare) Then
                    i = i + 1
                End If
            End If
            Next cl
            For Each cl In Sheets("Price File").Range("A2", Sheets("Price File").Range("A" & Rows.Count).End(xlUp))
            If InStr(1, cl, ProductSearchForm.PSFormTextBox1.Value, vbTextCompare) Then
                If ProductSearchForm.PSFormComboBox1.Value = "" Then
                    a = 1
                    ElseIf InStr(1, cl.Offset(0, 4).Value, ProductSearchForm.PSFormComboBox1.Value, vbTextCompare) Then
                    a = 1
                End If
            End If
            If a > 0 Then .Add cl.Value, Nothing
            a = 0
            Next cl
            If i > 250 Then
                If MsgBox((i) & " Results Found. Do you want to continue?" & vbNewLine & "This could take a long time", vbYesNo + vbInformation, "Application Message") = vbYes Then
                    Sheets("Price Builder").Range("N7").Resize(i).Value = Application.Transpose(.Keys)
                    Sheets("Price Builder").Range("N7").Resize(i).Name = "Lst"
                    Search.Resize Search.Range.Resize(i + 1)
                    Else
                    ProductSearchForm.Hide
                    GoTo LockSheet
                    End If
                ElseIf i > 60 And i <= 250 Then
                Sheets("Price Builder").Range("N7").Resize(i).Value = Application.Transpose(.Keys)
                Sheets("Price Builder").Range("N7").Resize(i).Name = "Lst"
                Search.Resize Search.Range.Resize(i + 1)
                ElseIf i > 0 And i <= 60 Then
                Sheets("Price Builder").Range("N7").Resize(i).Value = Application.Transpose(.Keys)
                Sheets("Price Builder").Range("N7").Resize(i).Name = "Lst"
                Search.Resize Search.Range.Resize(60)
                Else: GoTo NotFound
            End If
            End With
            ProductSearchForm.Hide
            GoTo LockSheet
        End If
        ElseIf PSFormRadio2.Value = True Then
        i = 0
        Sheets("Price Builder").Range("N7", Sheets("Price Builder").Range("N" & Rows.Count).End(xlUp)).ClearContents
        If PSFormRadio3.Value = True Then
            With CreateObject("scripting.dictionary")
            For Each cl In Sheets("Price File").Range("B2", Sheets("Price File").Range("B" & Rows.Count).End(xlUp))
            If InStr(1, cl, ProductSearchForm.PSFormTextBox1.Value, vbTextCompare) Then
                If ProductSearchForm.PSFormComboBox1.Value = "" Then
                    i = i + 1
                    ElseIf InStr(1, cl.Offset(0, 2), ProductSearchForm.PSFormComboBox1.Value, vbTextCompare) Then
                    i = i + 1
                End If
            End If
            Next cl
            For Each cl In Sheets("Price File").Range("B2", Sheets("Price File").Range("B" & Rows.Count).End(xlUp))
            If InStr(1, cl, ProductSearchForm.PSFormTextBox1.Value, vbTextCompare) Then
                If ProductSearchForm.PSFormComboBox1.Value = "" Then
                    a = 1
                    ElseIf InStr(1, cl.Offset(0, 2).Value, ProductSearchForm.PSFormComboBox1.Value, vbTextCompare) Then
                    a = 1
                End If
            End If
            If a > 0 Then .Add cl.Offset(0, -1).Value, Nothing
            a = 0
            Next cl
            If i > 250 Then
                If MsgBox((i) & " Results Found. Do you want to continue?" & vbNewLine & "This could take a long time", vbYesNo + vbInformation, "Application Message") = vbYes Then
                    Sheets("Price Builder").Range("N7").Resize(i).Value = Application.Transpose(.Keys)
                    Sheets("Price Builder").Range("N7").Resize(i).Name = "Lst"
                    Search.Resize Search.Range.Resize(i + 1)
                    Else
                    ProductSearchForm.Hide
                    GoTo LockSheet
                    End If
                ElseIf i > 60 And i <= 250 Then
                Sheets("Price Builder").Range("N7").Resize(i).Value = Application.Transpose(.Keys)
                Sheets("Price Builder").Range("N7").Resize(i).Name = "Lst"
                Search.Resize Search.Range.Resize(i + 1)
                ElseIf i > 0 And i <= 60 Then
                Sheets("Price Builder").Range("N7").Resize(i).Value = Application.Transpose(.Keys)
                Sheets("Price Builder").Range("N7").Resize(i).Name = "Lst"
                Search.Resize Search.Range.Resize(60)
                Else: GoTo NotFound
            End If
            End With
            ProductSearchForm.Hide
            GoTo LockSheet
            
            ElseIf PSFormRadio4.Value = True Then
            With CreateObject("scripting.dictionary")
            For Each cl In Sheets("Price File").Range("B2", Sheets("Price File").Range("B" & Rows.Count).End(xlUp))
            If InStr(1, cl, ProductSearchForm.PSFormTextBox1.Value, vbTextCompare) Then
                If ProductSearchForm.PSFormComboBox1.Value = "" Then
                    i = i + 1
                    ElseIf InStr(1, cl.Offset(0, 3).Value, ProductSearchForm.PSFormComboBox1.Value, vbTextCompare) Then
                    i = i + 1
                End If
            End If
            Next cl
            For Each cl In Sheets("Price File").Range("B2", Sheets("Price File").Range("B" & Rows.Count).End(xlUp))
            If InStr(1, cl, ProductSearchForm.PSFormTextBox1.Value, vbTextCompare) Then
                If ProductSearchForm.PSFormComboBox1.Value = "" Then
                    a = 1
                    ElseIf InStr(1, cl.Offset(0, 3).Value, ProductSearchForm.PSFormComboBox1.Value, vbTextCompare) Then
                    a = 1
                End If
            End If
            If a > 0 Then .Add cl.Offset(0, -1).Value, Nothing
            a = 0
            Next cl
            If i > 250 Then
                If MsgBox((i) & " Results Found. Do you want to continue?" & vbNewLine & "This could take a long time", vbYesNo + vbInformation, "Application Message") = vbYes Then
                    Sheets("Price Builder").Range("N7").Resize(i).Value = Application.Transpose(.Keys)
                    Sheets("Price Builder").Range("N7").Resize(i).Name = "Lst"
                    Search.Resize Search.Range.Resize(i + 1)
                    Else
                    ProductSearchForm.Hide
                    GoTo LockSheet
                    End If
                ElseIf i > 60 And i <= 250 Then
                Sheets("Price Builder").Range("N7").Resize(i).Value = Application.Transpose(.Keys)
                Sheets("Price Builder").Range("N7").Resize(i).Name = "Lst"
                Search.Resize Search.Range.Resize(i + 1)
                ElseIf i > 0 And i <= 60 Then
                Sheets("Price Builder").Range("N7").Resize(i).Value = Application.Transpose(.Keys)
                Sheets("Price Builder").Range("N7").Resize(i).Name = "Lst"
                Search.Resize Search.Range.Resize(60)
                Else: GoTo NotFound
            End If
            End With
            ProductSearchForm.Hide
            GoTo LockSheet
        End If
    End If
    
    
            
    NotFound:
        ActiveSheet.Protect "Passwrd"
        If MsgBox("No items matched your search" & vbNewLine & "Would you like to try again?", vbYesNo + vbExclamation, "Application Message") = vbYes Then
            ProductSearchForm.PSFormTextBox1.SetFocus
            ProductSearchForm.PSFormTextBox1.SelStart = 0
            ProductSearchForm.PSFormTextBox1.SelLength = Len(ProductSearchForm.PSFormTextBox1.Value)
                Else
                    ProductSearchForm.Hide
                    GoTo LockSheet
        End If
        
    LockSheet:
        If ActiveSheet.Range("D28") = "Unlock" Then
            Exit Sub
                Else
                    ActiveSheet.Protect "Passwrd"
        End If
    
    
    End Sub
    Last edited by Fluff; Jul 26th, 2019 at 10:32 AM. Reason: Changed password

  2. #2
    New Member
    Join Date
    Mar 2015
    Posts
    42
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Multiple Scripting Libraries?

    Apologies, submitted before complete.

    Basically I'm trying to chain several different Inst commands to search multiple columns in a table for specific values. So if my table looked like this:

    Apple Jim $2.14 Happy Tomato
    Apricot John $13.14 Sad Lettuce
    Banana Tim $7.31 Angry Cucumber

    TextBox1 searches column 1, TextBox2 column 2, and ComboBox1 searches column 4 if OptionButton1 is selected, and column 5 is OptionButton2 is selected. If any field is blank, it omits those results.

    So in my example, if i search:
    TextBox1 = "Ap"
    TextBox2 = "J"
    OptionButton1 = True & ComboBox1 = "t"

    This would .Add "Apple" and "Apricot" as keys in my scripting library

    If I searched
    TextBox1 = "a"
    TextBox2 = "im"
    OptionButton2 = True & ComboBox1 = ""

    This would .Add "Apple" and "Banana" as keys in my scripting library. I can do it with a bunch of if statements, but it's really messy. Is there a way to use If .exists and offsets to filter the data I'm looking for?

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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