Why does a ListBox require TWO clicks to select an item?

Hello skysurfer,

If this problem only occurs with this workbook then something has become corrupted in it. If it happens regardless of which workbook is open then you will have to re-install VBA. Another possibilty would be your mouse.

Hello Lawrence,

The problem is most like VBA related. This what I would do. Export all the VBA macros to the hard disk. This will store them as text files even the UserForm. Delete all the VBA macros, save the workbook, reopen the workbook, and import the saved macro files back into the workbook. Save the changes and run the workbook. Close, reopen, Run, and repeat. Hopefully this will fix the problem.

Hello Lawrence,

The "Export" function only does one file at a time. Yes, you need to transfer the Sheet macros as well as the Modules, and Forms. I think I have a macro in my library to do this.

Hello Lawrence,

I couldn't find it so I wrote a macro to export and one to import the files for you.

Please Login or Register  to view this content.

Leith,

Really appreciate your help.

The export macro generated this error: "Run time error 1004: Method 'VBE' of '_Application' failed"

on this line: Set VBProj = Application.VBE.ActiveVBProject

Also, I changed the file path to:"\\psf\Home\Documents\Projects\Schnieder\Dashboard 2012-04\VBA Recovery"

FYI, I'm running windows Excel 2010 on a Mac which explains the funky path. If that's an issue, I can whip out my Thinkpad with xl2003.

Lawrence

Hi Leith,

Made progress by ditching the Mac and using the Windows Thinkpad.

Two things: first, none of the Sheet (Microsoft Excel Objects) code was imported back to the VBA-cleansed file; and the import macro kept blowing up on on "frx" files, so I deleted that and the code completed without error.

Should I "manually" import the sheet codes? And is there something I should do with those frx files that could not be imported?

Thanks again for all your support!

Lawrence

Leith,

I rebuilt the VBA code and I still get the same problem of having to double-click a ListBox entry about half the time.

So, I recreated the file--started from scratch--with everything stripped out except the stuff related to running the userform called Countries. And I still get the same problem. Click on a country in the region 1 listbox and half the time it takes a double-click to get the item selected/deselected. Same with the Region 2 listbox.

I've attached the file. If you could take a peak it would save me from having to pop nitro pills to restart my heart. BTW, most of the code is from one of your postings on using the scripting dictionary...a VERY powerful tool you introduced me to!

Lawrence


NOTE: I had a last minute fix to the uploaded file...it should be version B.

Hello Lawrence,

The frx file type is exported automatically whenever a UserForm module is exported. The frx file contains information about the position and type of controls used on the UserForm. If I had known that this was for a Mac, I could have told you the macro would bomb. There are many tools available to PC users that the Mac doesn't support. You should update your profile to reflect which OS and version(s) of Excel you use.

After looking at the code, I see your ListBoxes use the Change event, which is the default event. This is most likely causing the problem. I changed this these to Click events since you are checking the Selected property, there is a possible processing lag between the Change and when the Click is recognized by the system. Here are the macros with a few changes made. Try this out and let me know the results.

Please Login or Register  to view this content.

Leith,

Sorry for the OS confusion...I'm using Windows 7 with xl2003 and xl2010 on a Mac courtesy of Parallels. (My big experiment away from a Windows Thinkpad.)

I made the changes and the double-click is still an issue with the second ListBox, lstRegion2. And the item selections no longer populate the spreadsheet with the results...the Select All checkboxes still work.

Attached is the updated file, version C.

Lawrence