Running VBA script every time a linked cell is updated through a combo box selection.

[MistakesWereMade]

So I have an issue in my excel file where I have a cell that is bound with a combo box. Every time the combo box selection is chosen, the linked cell will, of course, automatically contain the contents of the combo box. I then want my VBA script to automatically open an excel file on my desktop that corresponds with each time a combo box option is selected. However, my current script is missing the ability to automatically open the excel files. The script will only register that linked cell's contents have changed when I manually click on the linked cell, go the formula box, and press enter. I want my script to allow anyone to go to the combo box, make a selection which updates the linked cell, the script to realize the change, and then correspondingly open the right excel file. I've attached my current code.

Private Sub Worksheet_Change(ByVal Target As Range)


On Error Resume Next

Dim varCellvalue As Long

If Target.Address = "$F$2" Then


Var = Range("F2").Value

Workbooks.Open "C:\Users\MyUser\Desktop" & Var & ".xlsx"

End If

End Sub

 

[MistakesWereMade]

Wow... It's flawless. I can't thank you enough! Excel GOD. Honestly.

 

[Fluff]

You're welcome & thanks for the feedback

 

[MistakesWereMade]

I decided to add one more feature, a command button, that I thought would make the program run a bit more appropriately.

However, both before and after adding the command button, there was a small bug it seemed where when the script runs and a new workbook is opened, the original excel file interface has the chosen combo box selection highlighted in blue. This is most definitely not a big problem at all, but it would run more smoothly if this bug did not occur. Any ideas on how I could get the script to run without this bug? My entire code is as follows,

Private Sub ComboBox1_Change()


ComboBox1.ListFillRange = "DropDownList"
Me.ComboBox1.DropDown


End Sub


Private Sub CommandButton1_Click()


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


    On Error Resume Next


    Set Wbk = Workbooks.Open(Pth & Me.ComboBox1.Value & ".xlsx")
   
    On Error GoTo 0
   
    If Wbk Is Nothing Then
        MsgBox "Workbook not found."
        Exit Sub
    End If


End Sub

Thanks for all the help!

 

[Fluff]

I'm afraid I don't understand what you are saying.

 

[MistakesWereMade]

For example, when I type in "Item 1" in the combo box and then press "search", the script runs and opens my file tilted "Item 1". When I close out of my file tilted "Item 1", the following window is the previous excel file with the combo box and "search" button. Without touching anything, you can see that the combo box opens up and highlights the selection, "Item 1". It is a small bug that I would like to take out of the program if possible. I would rather it not reselect the combo box and highlight the selection in blue. I have attached pictures below, the first being before pressing "search", and the second image being after pressing "search" and closing the new window to reveal the original excel file with the combo box.

picture 1

picture 2

 

[Fluff]

Ok, simply remove this

Private Sub ComboBox1_Change()


ComboBox1.ListFillRange = "DropDownList"
Me.ComboBox1.DropDown


End Sub

 

[MistakesWereMade]

Yeah, that works, but I wanted to have the feature where my combo box is continuously comparing its contents with an input library. In other words, I made it so that when, let's say, "Ite" is typed into the combo box, all selections containing "Ite" will appear below the combo box. If I type "Item 1" into the combo box, then the only selection that will appear is "Item 1" below the combo box. I was able to do this using a series of formulas in Sheet2 so that it does not cluster my sheet1 with the combo box and search button interface.

So, I was hoping I could keep this feature and get rid of this weird bug if possible. Any other ideas?

Thanks for your time and responses.

 

[Fluff]

It's not a "bug", it's doing exactly what you have told it to do.

You could try

[COLOR=#ff0000]Dim DisableCb As Boolean[/COLOR]
Private Sub CommandButton1_Click()

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

    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."
        Exit Sub
    End If

    DisableCb = False
End Sub
Private Sub ComboBox1_Change()
If DisableCb Then Exit Sub

ComboBox1.ListFillRange = "DropDownList"
Me.ComboBox1.DropDown


End Sub

The part in red must go at the very top of the module, before any code

 

[MistakesWereMade]

Yes! That worked! The code runs even more smoothly now. I no longer experience the parent excel file with the combo box highlighting its selection after running the script.

There is one new issue where after pressing "Search", the combo box for some reason quickly flashes the last input searched in the box before reverting back to the current input search selection. Despite a quick flash of the previous input in the combo box, it still searches the correct/current combo box input. It would be nice if I could get rid of this quirk, but I am so glad it runs even better than before.

One issue I did resolve was I had to delete the 'Exit Sub' portion of the code after the 'MsgBox', as there was a bug where when you put in some false input in the combo box and press the "Search" button, the combo box continuous matching feature that I created would not work until a correct input was searched once again.

Therefore, my final code is as follows,

Dim DisableCb As Boolean


Private Sub CommandButton1_Click()


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


    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


    DisableCb = False
    
End Sub


Private Sub ComboBox1_Change()


If DisableCb Then Exit Sub


ComboBox1.ListFillRange = "DropDownList"
Me.ComboBox1.DropDown


End Sub

If there is a quick fix for this quick flash that I am experiencing when pressing the "Search" button, please let me know.

Anyways, thanks for the continued help!!!!!!

 

[Fluff]

Because you are using the Change event I'm not sure it's possible to get rid of that quick flash