VBA Combo Box autocomplete issue

[MistakesWereMade]

I have a script for my a combobox that is in a userform (GUI). The combo box uses a formula called "DropDownList" that I made to enable a particular type of autocomplete feature to my combo box.

My program works as follows: After I start typing in the combo box and it narrows down my search, I click on the option I want and then press a command button "search" to find and open a file in a folder. When I go to the combo box again to type in a new selection, this is when my program bugs out. I also am not able to close out of the excel file that opens after I "search". I have to manually do it by force closing. I think this has something to do with it.

I've attached pictures and part of my code. Any help would be much appreciated as I just cannot figure it out.

Dim DisableCb As Boolean


Private Sub CommandButton2_Click()


    Dim Wbk As Workbook
    Dim Pth As String
   
    Pth = Environ("Userprofile") & "\Desktop\My Files\"


    DisableCb = True


    On Error Resume Next


    Set Wbk = Workbooks.Open(Pth & Me.ComboBox1.Value)
   
    On Error GoTo 0
   
    If Wbk Is Nothing Then
        MsgBox "Workbook not found."
    End If
    
    ComboBox1.Value = ""
    
    If ComboBox1.Value = "" Then
        Dim myRange As Range
        Set myRange = ThisWorkbook.Worksheets("Data").Range("E2")
        myRange.Clear
    End If
    
    DisableCb = False


End Sub


Private Sub ComboBox1_Change()


    If DisableCb Then Exit Sub
    
    ComboBox1.RowSource = "DropDownList"
    Me.ComboBox1.DropDown
    
    Sheets("Data").Range("E2") = Me.ComboBox1.Value
    
End Sub

 

[DanteAmor]

I think, the first time this variable DisableCb is True, that's why you do not have an error.
After pressing the button the variable is False, that's why you now have the error.


You can put the complete code.


And how do you want to load the combo?
What do you have in "DropDownList", is it a range name?

 

[MistakesWereMade]

Hey DanteAmor! Thanks for the response, I appreciate it!

Below is my complete code... Not sure if it is anymore helpful since I posted the major bits already. So you recommend me deleting the False portion? Mind you, my code is entirely in the UserForm VBA window. My script worked perfectly with a very similar code in a normal excel file, but it was when I transcribed it to work with a GUI that I developed the issue that I am having. The main challenges were to get the combo box working with the excel sheet where "DropDownList" is located, as the combobox has a different property window than in an excel spreadsheet. Anyways, DropDownList is a formula that allows me to have an autocomplete/comparison feature for my combo box. Its formula is:

=Data!$I$2:INDEX(Data!$I$2:$I$22, MAX(Data!$H$2:$H$22), 1)

Dim DisableCb As Boolean


Private Sub CommandButton2_Click()


    Dim Wbk As Workbook
    Dim Pth As String
   
    Pth = Environ("Userprofile") & "\Desktop\My Files\"


    DisableCb = True


    On Error Resume Next


    Set Wbk = Workbooks.Open(Pth & Me.ComboBox1.Value)
   
    On Error GoTo 0
   
    If Wbk Is Nothing Then
        MsgBox "Workbook not found."
    End If
    
    ComboBox1.Value = ""
    
    If ComboBox1.Value = "" Then
        Dim myRange As Range
        Set myRange = ThisWorkbook.Worksheets("Data").Range("E2")
        myRange.Clear
    End If
    
    DisableCb = False


End Sub


Private Sub ComboBox1_Change()


    If DisableCb Then Exit Sub
    
    ComboBox1.RowSource = "DropDownList"
    Me.ComboBox1.DropDown
    
    Sheets("Data").Range("E2") = Me.ComboBox1.Value
    
End Sub


Private Sub CommandButton1_Click()


    Application.DisplayAlerts = False
    Application.Quit


End Sub


Private Sub CommandButton3_Click()


    Application.Visible = True


End Sub

 

[DanteAmor]

How do you load the combo the first time?

 

[MistakesWereMade]

So I type into the combo box in my userform and then selections will start to fill that contain what I am typing. Then a selection can be made early by clicking an option, or by completely typing out a selection. After this, I press a command button to "search" which actually just goes to a folder on my desktop and then opens a specific excel file which automatically opens. My goal is to be able to close this opened excel file and then go back to the combo box and search for another file. When I use the combo box the first time, it works perfectly. It is when I run the combo box a second time and any time after, that the autofill feature of my combo box is broken, aka "DropDownList" and I cannot search for a new file. I also cannot close out of the file that was opened from my desktop unless I force quit this excel file. Maybe another clue to my problem is that the first file that I successfully am able to search will remain as the only autofill feature no matter what I type in the combo box. I hope this makes sense... Let me know if I can improve my explanation in any way.

Thanks for helping!!

 

[DanteAmor]

Well, you did not answer my question.

The first time. How can you select a combo data if nothing has been loaded in the combo?
In some property of the combo did you make a load?

Or I guess the first time there's nothing in the combo and you can not select anything from the combo, so you have to start writing something.

Try this

Private Sub ComboBox1_Change()


    If DisableCb Then Exit Sub
    
[COLOR=#0000ff]    d = Evaluate("DropDownList")[/COLOR]
[COLOR=#0000ff]    ComboBox1.List = d[/COLOR]
    Me.ComboBox1.DropDown
    
    Sheets("Data").Range("E2") = Me.ComboBox1.Value
    
End Sub

 

[MistakesWereMade]

Unfortunately that didn't quite work. It said "permission denied" when I try to type into the combo box.

As per your original question, I think it must be loaded because for the property in the combo box on a normal spreadsheet rather than in my userform, I have ListFillRange set to DropDownList. However, in the property window for my userform combo box, there is no specification for ListFillRange, only RowSource, so I attempted to utilize the line of code ' ComboBox1.RowSource = "DropDownList" '

Any other suggestions? Hopefully I answered your original question more appropriately.

 

[DanteAmor]

Do you have something in the properties of the combobox?

 

[MistakesWereMade]

I've gone over the whole properties window for the combo box in the userform and I can't seem to find anything that I can change to get the desired effect.

HOWEVER, I've been working on my program and I have made some edits... Should I make a new thread or continue to post here?

I now have my program working differently, but a little more the way I want it to. There is one thing missing as I want to open a new file and then have the userform remain open so the user can revert back to the userform and type in a new file to open. Right now, I have the userform resetting so it closes itself entirely after pressing search. This way allows my combobox to not malfunction.

Nonetheless, I want to implement code that automatically opens the userform once again. Any ideas? I highlighted my changes in red. I can open a new thread if need be. I appreciate the help.

Dim DisableCb As Boolean


Private Sub CommandButton2_Click()


    Dim Wbk As Workbook
    Dim Pth As String
    Dim myRange As Range
    
[COLOR=#ff0000]    Unload UserForm1[/COLOR]
    
    Set myRange = ThisWorkbook.Worksheets("Data").Range("E2")
    
    Pth = Environ("Userprofile") & "\Desktop\My Files\"


    DisableCb = True


    On Error Resume Next


    Set Wbk = Workbooks.Open(Pth & Me.ComboBox1.Value)
   
    On Error GoTo 0
   
    If Wbk Is Nothing Then
        MsgBox "Workbook not found."
    End If
    
[COLOR=#ff0000]    myRange.Clear[/COLOR]
    
    DisableCb = False
    
End Sub


Private Sub ComboBox1_Change()


    If DisableCb Then Exit Sub
    
    Me.ComboBox1.DropDown
    Sheets("Data").Range("E2") = Me.ComboBox1.Value
    
End Sub


Private Sub CommandButton1_Click()


    Application.DisplayAlerts = False
    Application.Quit


End Sub


Private Sub CommandButton3_Click()


    Application.Visible = True


End Sub

 

[DanteAmor]

If you want the userform to remain open, then remove this line

Unload UserForm1

If you want to interact between the file and the form, change the ShowModal property of the form to False.