VBA:programmatically invoking menu items from Excel Worksheet
New and Exciting ... what?
--
---
HTH
Bob
(there's no email, no snail mail, but somewhere should be gmail in my addy)
"morse100" wrote in message
...
"JLatham" wrote:
This might help with the hunt for the control's ID? Gives a list,
although
interpreting the output list may be an interesting experiment in the
chapter
on New and Exciting Ways to Lock Up Excel.
Sub ControlBarsInfo()
'potential properties of CommandBar objects:
' .AdaptiveMenu
' .Application
' .BuiltIn
' .Controls
' .Creator
' .Height
' .Index
' .Left
' .Name
' .NameLocal
' .Parent
' .Position
' .RowIndex
' .Top
' .Type
' .Visible
' .Width
'
'potential methods of them
' .Delete
' .FindControl
' .Reset
' .ShowPopUp
'
Dim anyCB As CommandBar
Dim anyControl As Object
Worksheets("Sheet1").Activate
Range("A1").Select
For Each anyCB In CommandBars
ActiveCell = anyCB.Name
ActiveCell.Offset(0, 1) = anyCB.ID
If anyCB.Controls.Count 0 Then
ActiveCell.Offset(0, 3) = "Has Controls"
End If
On Error Resume Next ' not all report a parent
ActiveCell.Offset(0, 2) = anyCB.Parent
ActiveCell.Offset(1, 0).Activate
Next
End Sub
"Bob Phillips" wrote:
You need to get the controls id, and then execute it like so
Application.VBE.CommandBars.FindControl(ID:=ctl_id ).Execute
Sheet delete is 847.
--
---
HTH
Bob
(there's no email, no snail mail, but somewhere should be gmail in my
addy)
"morse100" wrote in message
...
I would like to to be able invoke specified menu items from the
standard
Excel WorkSheet menu bar from within VBA subroutines and functions
(do
***not*** need to change the resulting behaviour in any way
whatsoever).
For example
Edit | Delete Sheet
But I have absolutely no idea how to access the equivalent OnAction
macro,
or indeed to discover its name.
Any and all help will be much appreciated
many thanks
morse
Hi,
many thanks. I am about to 'write' a contribution to the "New and Exciting
......".
Best wishes
Morse
|