VBA clear selection of dropdown list upon closing file

[tidepooler]

Hello,

I am attempting to set up my Excel workbook/worksheet (not a user form) to clear a dropdown box when the file is closed. Right now, every time I close the file and reopen it, the last selection in the dropdown is shown. I want this to be clear.

I may also need to change this in the dropdown box's properties, but I have been going down the rabbit hole with VBA code. The problem is that I cannot seem to figure out which event I can apply code to that allows me to use the object name and set its value equal to "".

Thanks in advance!

 

[Jeffrey Mahoney]

Are you talking about a Data Validation dropdown list, an ActiveX ComboBox or a Form ComboBox?

 

[tidepooler]

It is an ActiveX ComboBox.

 

[Jeffrey Mahoney]

Please try this. It needs to be placed in the THISWORKBOOK module. Replace "ComboBox1" the name of your ComboBox

Private Sub Workbook_BeforeClose(Cancel As Boolean)
  Sheet1.ComboBox1.ListIndex = -1
End Sub

 

[tidepooler]

Thank you so much, Jeffrey! Sorry for the delay - I have several projects going and I got distracted by another one before I could get back to this.

This worked perfectly.

Now I'm wondering if there is a way to save the file upon closing as well and avoid the "Save File" dialog box...?

 

[tidepooler]

My research turned up the code for the saving issue, so I'm good! Thanks again.