cmdbarmenu focus problem

[pjbur2005 via OfficeKB.com]

Dear all,

I have a complex form using Excel, the processes on the form all work well.
I have removedmenu bar items that are not required and added ones of my own
to manage how users use the form. This enables actions that are required and
disables actions that are not.

My problem is that when a user activates acell or range on the form and then
goes to use one of the menu bar items it does not work. They have to release
the cell first. Is there a way of taking focus from the cell and ensuring
that the menu bar item gets it and the on action then works?

Here is the code I use to set the items (this is one of about 5)

Set CmdBarMenu = CmdBar.Controls.Add(Type:=msoControlPopup, before:=hindex)
CmdBarMenu.Caption = "&Refresh Form"
CmdBarMenu.OnAction = "'" & ThisWorkbook.Name & "'!thisworkbook.refreshform"

Manmy thanks for any help or advice in advance.

Paul

 

[Jim Cone]

With the cursor active inside the cell it is impossible to get a
menu item to work. That is just the way Excel is.
You will have to make sure your users understand that.

An alternative is to uncheck the "Move Selection After Enter" setting
on the Edit tab in Tools | Options.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



"pjbur2005 via OfficeKB.com" <u18722@uwe>
wrote in message
Dear all,
I have a complex form using Excel, the processes on the form all work well.
I have removedmenu bar items that are not required and added ones of my own
to manage how users use the form. This enables actions that are required and
disables actions that are not.

My problem is that when a user activates acell or range on the form and then
goes to use one of the menu bar items it does not work. They have to release
the cell first. Is there a way of taking focus from the cell and ensuring
that the menu bar item gets it and the on action then works?

Here is the code I use to set the items (this is one of about 5)

Set CmdBarMenu = CmdBar.Controls.Add(Type:=msoControlPopup, before:=hindex)
CmdBarMenu.Caption = "&Refresh Form"
CmdBarMenu.OnAction = "'" & ThisWorkbook.Name & "'!thisworkbook.refreshform"

Manmy thanks for any help or advice in advance.

Paul

 

[pjbur2005 via OfficeKB.com]

Jim Thanks for you reply,

Appreciate your point but why then is not the same for "file", "View" or any
other menu bar item appart from those I have added?

Unchecking the "move Seelction........" made no differance.

I have around 400 to 600 users, some understand, some think they do and the
rest cant be bothered too!

Paul