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

 

[Fluff]

Hi & welcome to MrExcel.
As you have discovered, Change Events do not work with comboboxes. You will need to run the code from the combobox itself.

 

[MistakesWereMade]

Might you be able to tell me how I may run the code from the combobox itself? I'm a complete noob at VBA. I was able to write two different VBA codes for my excel worksheet, one for sheet1 and another for sheet2. However, I did not see a prompt to type code for my combobox.

Sheet1 Code:


Private Sub ComboBox1_Change()


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


End Sub



Sheet2 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

 

[Fluff]

Something like

Private Sub ComboBox1_Click()
   Dim Wbk As Workbook
   Dim Pth As String
   
   Pth = "C:\MrExcel\"
   Set Wbk = Workbooks.Open(Pth & Me.ComboBox1.Value)
End Sub

 

[MistakesWereMade]

Thank you so much Fluff! It worked so well! However, I do have one more final touch I would like to enact on the file. I would prefer if it would require an enter or click from the drop down menu before the script runs to open the corresponding file. Right now, the script is working so that as soon as I type in a full selection like "Item 1" into the combo box as a selection, it automatically runs the script to open that file. I guess what I am trying to do is make it so that the user typing in the combo box has to press enter before the script runs rather than before the user is finished finalizing his selection in the combo box. Hopefully my question is clear. Below is my final code, code only written in sheet1 since it was all I needed. I appreciate all the help you have given me!!

Private Sub ComboBox1_Change()


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


End Sub


Private Sub ComboBox1_Click()


On Error Resume Next


   Dim Wbk As Workbook
   Dim Pth As String
   
   Pth = "C:\Users\Kyle\Desktop\"
   Set Wbk = Workbooks.Open(Pth & Me.ComboBox1.Value & ".xlsx")
   
End Sub

 

[Fluff]

You could try

Private Sub ComboBox1_LostFocus()
   Dim Wbk As Workbook
   Dim Pth As String
   
   Pth = "C:\Users\Kyle\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

But you will need to click outside the combo to make it work

 

[MistakesWereMade]

Thanks again for such a quick response Fluff!

So I like how this slight modification change causes it to require a click outside the combo box first to initiate the open workbook command. However, with this change, it also causes the original file with the combo box to remain as the present screen instead of opening the new file and staying there. This feature to me is very important. I would like the new opened file to remain "selected" as the primary window rather than being opened and the "deselected" so that the combo box file becomes the primary window visible. Hopefully this makes sense...

Is it at all possible to make it so that after clicking outside the combo box, the new window opens and remains on screen rather than reverting to the original combo box excel file?

I appreciate all the help!

 

[Fluff]

With the last code the new workbook is the active workbook for me.
Have you removed the code from the Click event?

 

[MistakesWereMade]

This is what I have so far in my sheet1 code,

Private Sub ComboBox1_Change()


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


End Sub


Private Sub ComboBox1_LostFocus()


On Error Resume Next


   Dim Wbk As Workbook
   Dim Pth As String
   
   Pth = "C:\Users\MyUser\Desktop\"
   Set Wbk = Workbooks.Open(Pth & Me.ComboBox1.Value & ".xlsx")
   
End Sub

I got rid of the Click and replaced it with LostFocus. Should I write it another way?

 

[Fluff]

I'd recommend using

Private Sub ComboBox1_LostFocus()
   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

This should work for all users, but it won't change which workbook is active.