Excel VBA CommandBar buttons are not responsive when cell is in Edit Mode

In Excel, we are using VBA to create a CommandBar with several buttons.

Things are working fine, except in one case:
While the user is editing a cell, and he clicks on a CommandBar button, the click event is not triggered in VBA. In essence, the button does nothing and gives no feedback when the cell is in Edit Mode.

How does one get around this issue? The user might be confused as to why nothing is happening after entering a cell value and immediately clicking on a CommandBar button.

Thanks.
pkoivulaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Martin LissOlder than dirtCommented:
In the command button code add a Select to some other cell as the first line in the code.
0
pkoivulaAuthor Commented:
MartinLiss,
The button's event handler sub is not called while a cell is in the middle of being edited.
0
Martin LissOlder than dirtCommented:
I know but what I'm saying is that you should add something like Range("A1").Select in the macro or VBA code for the command button so that focus will move off the cell that the user changed.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

pkoivulaAuthor Commented:
MartinLiss,

That's just it -- VBA code for the Command Button is not executed while the cell is still being edited.

I have attached a sample code that demonstrates the problem. Notice the button does nothing as long as the cell still has input focus (has blinking cursor).

Could you advise where we should put "Range("A1").Select" so that it gets executed even while the cell is being edited?
EditModeEE.xlsm
EditMode.png
0
Martin LissOlder than dirtCommented:
I'm sorry but I thought that you were talking about a command button on a sheet rather than a toolbar command button, and right now I have no idea how to solve your problem but I'll see what I can find out.
0
Martin LissOlder than dirtCommented:
From what I've found out by Googling, the VBA environment is shut down when you click a toolbar button so it seems that you can't do what you want to do, at least not directly. One possible solution is to float an ActiveX textbox over the cell where the editing is being done, and while editing that textbox you will be able to select another cell or do whatever you want to do. To try it on a small scale in your posted project, see my magical floating ActiveX control article.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
pkoivulaAuthor Commented:
Thanks, MartinLiss, for the magical floating ActiveX control article.

Yes, this seems to work, as the user is no longer editing the cell directly, but is changing the value of the floating TextBox. VBA code can run when handling the KeyDown event during input.

But it seems to introduce other issues, such as the Formula Bar and the Name Box dropdown cannot be clicked while editing the TextBox.

Are you aware of any other ways to get around the original problem?

Thanks again for your help!
0
Martin LissOlder than dirtCommented:
Sorry but I'm out of ideas.
0
Martin LissOlder than dirtCommented:
Well maybe not. Are you required for some reason to use a commandbar for what you want to do? In general terms what is it that you want to do? Could it be done by adding command button to the sheet or a new item on the cell context menu?
0
pkoivulaAuthor Commented:
We opted to use a CommandBar because we wanted our custom functions to be always visible to the user at a prominent location (near the top), regardless of which sheet, or where in the sheet, the user has navigated to.

Does a Command button on the sheet help though? I just tried it, but it is also not clickable when the cell is in Edit Mode. Same for the Cell Context Menu -- the custom menu entry does not appear when right-clicking on a cell currently in Edit Mode.

We just wanted our custom function to continue to work seamlessly, without educating the user to always remember to exit Edit Mode.
0
Martin LissOlder than dirtCommented:
I've modified your demo workbook and added two buttons, one a Form button and one an ActiveX button (which I don't really recommend using on a sheet) and while editing if I click on either one I get a response. I also get a partial context menu when I right click on a cell while editing.
EditModeEE.xlsm
0
pkoivulaAuthor Commented:
Interesting, Excel 2007 and 2010 exhibit different behaviors.

Your example works in Excel 2010 -- both Form and ActiveX buttons are responding when editing. In Excel 2007, the buttons do nothing when editing.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.