How to enable / disable ControlButton added to Cell CommandBar
 

Hi,

I have an addin that adds a ControlButton to the Cell CommandBar as follows:

'/// Read Cell Commandbar and add "View Data"
Set oCellCommandBar = Application.CommandBars("Cell")

If oCellCommandBar.FindControl(, , "ViewData") Is Nothing Then
Set oButton = oCellCommandBar.Controls.Add(msoControlButton)
oButton.Caption = "View Data..."
oButton.OnAction = "ViewData"
oButton.Tag = "ViewData"
End If

Which works fine. However, I want to control enabling / disabling it
dependent upon when the cell menu is shown (e.g. only if the selected cell is
valid for the action).

Is this possible in VBA for Excel 2002? If so, I cannot see where to add
the code to be run when this menu is displayed?


Thank you

Rob

How to enable / disable ControlButton added to Cell CommandBar
 

I should be able to catch a cell right-click with the
Worksheet_BeforeRightClick event and enable your control.

But users can also pop up the cell menu with Shift-F10 and the context menu
key that some keyboards have. You can use OnKey to trap Shift-F10 but I
don't know any way to trap the context key.

--
Jim
"Rob Blackmore" <Rob wrote in message
...
| Hi,
|
| I have an addin that adds a ControlButton to the Cell CommandBar as
follows:
|
| '/// Read Cell Commandbar and add "View Data"
| Set oCellCommandBar = Application.CommandBars("Cell")
|
| If oCellCommandBar.FindControl(, , "ViewData") Is Nothing Then
| Set oButton = oCellCommandBar.Controls.Add(msoControlButton)
| oButton.Caption = "View Data..."
| oButton.OnAction = "ViewData"
| oButton.Tag = "ViewData"
| End If
|
| Which works fine. However, I want to control enabling / disabling it
| dependent upon when the cell menu is shown (e.g. only if the selected cell
is
| valid for the action).
|
| Is this possible in VBA for Excel 2002? If so, I cannot see where to add
| the code to be run when this menu is displayed?
|
|
| Thank you
|
| Rob
|

How to enable / disable ControlButton added to Cell CommandBar
 

I should

"You should" <g

--
Jim
"Jim Rech" wrote in message
...
|I should be able to catch a cell right-click with the
| Worksheet_BeforeRightClick event and enable your control.
|
| But users can also pop up the cell menu with Shift-F10 and the context
menu
| key that some keyboards have. You can use OnKey to trap Shift-F10 but I
| don't know any way to trap the context key.
|
| --
| Jim
| "Rob Blackmore" <Rob wrote in message
| ...
|| Hi,
||
|| I have an addin that adds a ControlButton to the Cell CommandBar as
| follows:
||
|| '/// Read Cell Commandbar and add "View Data"
|| Set oCellCommandBar = Application.CommandBars("Cell")
||
|| If oCellCommandBar.FindControl(, , "ViewData") Is Nothing Then
|| Set oButton = oCellCommandBar.Controls.Add(msoControlButton)
|| oButton.Caption = "View Data..."
|| oButton.OnAction = "ViewData"
|| oButton.Tag = "ViewData"
|| End If
||
|| Which works fine. However, I want to control enabling / disabling it
|| dependent upon when the cell menu is shown (e.g. only if the selected
cell
| is
|| valid for the action).
||
|| Is this possible in VBA for Excel 2002? If so, I cannot see where to add
|| the code to be run when this menu is displayed?
||
||
|| Thank you
||
|| Rob
||
|

How to enable / disable ControlButton added to Cell CommandBar
 

Thank you for your help.

This works ok in a worksheet (e.g. sheet 1) but then the code is not
available in sheet 2 and sheet 3.

Ideally I want the code to be part of my add-in so that it applies to any
workbook opened in Excel (sorry for not clarifying that before).

Is it possible to put it in the add-in so it applies to all worksheets?


"Jim Rech" wrote:

I should

"You should" <g

--
Jim
"Jim Rech" wrote in message
...
|I should be able to catch a cell right-click with the
| Worksheet_BeforeRightClick event and enable your control.
|
| But users can also pop up the cell menu with Shift-F10 and the context
menu
| key that some keyboards have. You can use OnKey to trap Shift-F10 but I
| don't know any way to trap the context key.
|
| --
| Jim
| "Rob Blackmore" <Rob wrote in message
| ...
|| Hi,
||
|| I have an addin that adds a ControlButton to the Cell CommandBar as
| follows:
||
|| '/// Read Cell Commandbar and add "View Data"
|| Set oCellCommandBar = Application.CommandBars("Cell")
||
|| If oCellCommandBar.FindControl(, , "ViewData") Is Nothing Then
|| Set oButton = oCellCommandBar.Controls.Add(msoControlButton)
|| oButton.Caption = "View Data..."
|| oButton.OnAction = "ViewData"
|| oButton.Tag = "ViewData"
|| End If
||
|| Which works fine. However, I want to control enabling / disabling it
|| dependent upon when the cell menu is shown (e.g. only if the selected
cell
| is
|| valid for the action).
||
|| Is this possible in VBA for Excel 2002? If so, I cannot see where to add
|| the code to be run when this menu is displayed?
||
||
|| Thank you
||
|| Rob
||
|