How to enable / disable ControlButton added to Cell CommandBar

[Rob Blackmore]

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

 

[Jim Rech]

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
| 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
|

 

[Jim Rech]

I should

"You should" <g>

--
Jim
|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
| || 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
||
|

 

[Rob Blackmore]

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?