Multiple Scripting Libraries?

S

SensualCarrots

New Member
Joined
Mar 21, 2015
Messages
46
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 a moderator:

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
Sort by date Sort by votes
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?
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Locked
  • Question Question
copy range of cells
Replies
1
Views
91
Joe4
Joe4
O
  • Question Question
Frustrating intermittent VBA error dialog telling me that "User-define Type Not Defined"
Replies
1
Views
266
Micron
M
B
  • Question Question
Error message when copying and renaming a worksheet
2
Replies
13
Views
344
smozgur
smozgur
B
  • Solved
VBA to check if worksheet exists
Replies
8
Views
345
bdt
B
S
  • Solved
Copy hyperlinks from one table column to another. The columns are identical
Replies
2
Views
426
sspatriots
S

Forum statistics

Threads
1,224,823
Messages
6,181,183
Members
453,020
Latest member
Mohamed Magdi Tawfiq Emam

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top