Clear activex listbox selection

[ppermuth]

Hello!

I have an activeX listbox (single select) on a worksheet in excel. Listbox_click is used to run a macro vba driven report based upon the selection made. The report generates wonderfully ... however, the listbox selection remains in place. Where this becomes a problem is the next time that the workbook is opened. The previous selection remains and the report automatically generates. To solve this, I am trying to find out how to reset the listbox after the report completes running. I have tried to use this after the report macro completes running:

For x = 0 To ListBox1.ListCount - 1
If ListBox1.Selected(x) = True Then
ListBox1.Selected(x) = False
End If

however, this did not really work - the report tries to run again right away, and a custom fail message is delivered telling the user that the report is already generated.

I don't really need to fancy things up by creating a userForm. Its just a simple box to run a report on data from another spreadsheet in the workbook.

Any ideas would be welcome!
Thank you!

[snb]

Use Listbox_change() instead

[ppermuth]

Hello!

I tried switching the code to ListBox_change from ListBox_Click. Unfortunately the same thing happens when I close out of the workbook and then open it again. The last selected report type which was chosen runs again automatically. I don't know if it has anything to do with another aspect: every time I open the workbook, I must select the option to enable the macros. Do I have the wrong settings or need different permissions? Any other ideas on changing ListBox?

[mdmackillop]

Try

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.EnableEvents = False
Sheets(1).ListBox1.ListIndex = -1
Application.EnableEvents = True
End Sub

[snb]

Please post your workbook.

[ppermuth]

Try

Private Sub Workbook_BeforeClose(Cancel As Boolean)
 Application.EnableEvents = False
 Sheets(1).ListBox1.ListIndex = -1
 Application.EnableEvents = True
 End Sub

Thank you so much! This does the trick! I truly appreciate your help