PDA

View Full Version : [SOLVED] Clear activex listbox selection



ppermuth
05-28-2016, 09:39 PM
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
05-29-2016, 07:47 AM
Use Listbox_change() instead

ppermuth
05-29-2016, 06:49 PM
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
05-30-2016, 10:09 AM
Try

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

snb
05-30-2016, 12:44 PM
Please post your workbook.

ppermuth
05-30-2016, 06:42 PM
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 :)