Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

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

Posted on 2014-08-12
12
Medium Priority
?
2,599 Views
Last Modified: 2014-08-20
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.
0
Comment
Question by:pkoivula
  • 7
  • 5
12 Comments
 
LVL 50

Expert Comment

by:Martin Liss
ID: 40256971
In the command button code add a Select to some other cell as the first line in the code.
0
 

Author Comment

by:pkoivula
ID: 40257182
MartinLiss,
The button's event handler sub is not called while a cell is in the middle of being edited.
0
 
LVL 50

Expert Comment

by:Martin Liss
ID: 40257190
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
Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

 

Author Comment

by:pkoivula
ID: 40257270
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
 
LVL 50

Expert Comment

by:Martin Liss
ID: 40257303
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
 
LVL 50

Accepted Solution

by:
Martin Liss earned 2000 total points
ID: 40257320
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
 

Author Comment

by:pkoivula
ID: 40257719
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
 
LVL 50

Expert Comment

by:Martin Liss
ID: 40258275
Sorry but I'm out of ideas.
0
 
LVL 50

Expert Comment

by:Martin Liss
ID: 40259549
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
 

Author Comment

by:pkoivula
ID: 40259583
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
 
LVL 50

Assisted Solution

by:Martin Liss
Martin Liss earned 2000 total points
ID: 40259657
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
 

Author Comment

by:pkoivula
ID: 40259719
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

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Quickbooks hosting can do wonders to your enterprise but considering the points elaborated in the article which will help you to better analyze the outcomes. So scan your business, its needs and then move to the new world of limitless benefits.
If Skype for Business came with your office 2016 or office 365 installation, you may find that it's almost impossible to either disable or remove it. The application will often launch with each start of Windows, even when explicitly configured not t…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
How can you see what you are working on when you want to see it while you to save a copy? Add a "Save As" icon to the Quick Access Toolbar, or QAT. That way, when you save a copy of a query, form, report, or other object you are modifying, you…

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question