PDA

View Full Version : Solved: Excel 2003 and 2007 - Cannot make selection in Listbox2 if click Button twice



frank_m
01-24-2011, 05:50 AM
Excel 2003 and 2007 (.xls file)

If I click the Import Shipping Address button once on the Invoice/cert sheet,
I then can make a selection in Listbox2 just fine

however if I click the Import Shipping Address button a second time without changing anything, I'm locked out from be able to make a selection in listbox2

Please try the sample attached workbook to see if it works multiple times for you,
as I'm wondering if it may be my mouse drivers or other hardware specific to my pc.

Thanks,

frank_m
01-24-2011, 06:25 AM
It seems the issue might be relative to one of two facts, or both.
(1) I changed the Command button 3 click event from Private to Public so that I could call it from a regular macro module.

(2) If I remove the on error resume next at the beginning of the Command button 3 click event, I usually get a invalid list property error when the command button 3 is called from the import invoice info button

Very strange to me that it works the first time I click the import button, but not the second.

I have attached a Rev 2 Sample workbook where I removed the On Error Resume next command every where except for command button 3.
And I cleaned out some come confusing code commenting.

Thanks

frank_m
01-24-2011, 07:06 AM
I change the Command button3 back to private and put a duplicate of it's code within the import invoice info button, but the results are the same.
* I get an invalid list index error if I do not use on error resume next, and it works as expected if I do use on error resume next.

See Rev 3 attachment

Still I am able to make a selection in listbox2 after running the import address info button once, but if I click it a second time I'm locked out of Listbox2 :banghead:

The main thing I am seeking here is to have others try what I described, to see if they are also locked out of listbox2 after running the code a 2nd time

If it works multiple times for others, then that will help me determine if it might be a hardware issue such as the mouse.

Thanks :friends:

frank_m
01-24-2011, 10:13 AM
Hope you'll forgive me as I just now crossposted over at Ozgrid as I waited about three hours for a response here, but no one has consented to trying out my workbook to report if the listbox behavior is the same when clicking the button a 2nd time.
http://www.ozgrid.com/forum/showthread.php?t=150039&p=539754#post539754

Thanks guys, I really appreciate all the help I get here and sure hope that this very first crossposting of mine, will not make anyone frown at me.

frank_m
01-25-2011, 08:02 AM
[ Solved now ] :yes

After many hours of experimenting with about a dozen different ideas that did not work, and googling a lot, only to come across a couple impracticable, and/or clunky work around's, I finally came up with a hunch, that did solve the problem. - What I discovered was that by using an active x command button, with take Focus on click set to True.(problem GONE) - (I had been using a forms style sheet button shape with macro assigned)

Later, I noticed that an even better solution was presented to me by member name rory, at ozgrid. - That is to add ActiveWindow.ActiveCell.Activate before turning screen updating off. -- Works great..

Thanks for your time and all that you guys do here. - Several of you have helped me enormously over the last few months since I joined. - I promise to make cross posting a rarity, (this really was my 1st),,,,, it's just that I had spent so many hours googling and experimenting with ideas that took me no where...