VBA:programmatically invoking menu items from Excel Worksheet menu

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
The easiest way to learn those is to use
Tools | Macro | Record New Macro
and then perform an operation and examine the code generated.

Dave McRitchie has some information about getting started in that direction
here:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

In some cases you'll have to modify the macro recorded to become more
generic. A good example of that came up recently: someone had recorded a
macro to format a row of a worksheet a special way. They'd started the macro
just before selecting the row to work with. So the macro recorded that part
of it and it threw them - and all they had to do was remove the line of code
that showed where they'd selected the row, and the one toward the end which
then selected a particular cell on that specific row.
 
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)
 
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
 
JLatham said:
The easiest way to learn those is to use
Tools | Macro | Record New Macro
and then perform an operation and examine the code generated.

Dave McRitchie has some information about getting started in that direction
here:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

In some cases you'll have to modify the macro recorded to become more
generic. A good example of that came up recently: someone had recorded a
macro to format a row of a worksheet a special way. They'd started the macro
just before selecting the row to work with. So the macro recorded that part
of it and it threw them - and all they had to do was remove the line of code
that showed where they'd selected the row, and the one toward the end which
then selected a particular cell on that specific row.
Click to expand...


Hi,

many thanks. I have tried the 'recipe' and it works beautifully when I
record macros involving the WorkSheet menu bar - so I wondered if it could be
used with at least one of the 'pop up' menus that can be invoked by right
clicking.

Specifically right clicking on a sheets tab - am interested in the tab Color
Option and would like to set up a situation in which the user can invoke the
'color picker' from a subroutine. So far that trick escapes me.

Again many thanks

Morse
 
Bob Phillips said:
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)
Click to expand...

Hello,

many thanks for the quick and very helpful reply. I would be grateful if
you could point me at a source for other Control-Ids like the one you
specified.

Best wishes

Morse
 
JLatham said:
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
Click to expand...
Hi,

many thanks. I am about to 'write' a contribution to the "New and Exciting
.......".

Best wishes


Morse
 
New and Exciting ... what?

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
Get them with code

For Each ctl In Application.Commandbars("Worksheet Menu
Bar").Controls("File").Controls
Debug.Print ctl.Name, ctl.Id
Next Ctl

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
Bob Phillips said:
New and Exciting ... what?
Click to expand...

Sorry - just an abbreviated cross reference to a comment made by JLatham
which was

"> 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. "
Click to expand...

Actually it all went without a hitch.

Thanks for your advice and help

Morse
 
LOL! Thanks for telling me.



morse100 said:
Sorry - just an abbreviated cross reference to a comment made by JLatham
which was

"> This might help with the hunt for the control's ID? Gives a list,
although

Actually it all went without a hitch.

Thanks for your advice and help

Morse
Click to expand...
 
I am having trouble with the "Date", "Xirr" and Xnpv" properties of the
worksheet function class. The error message says, "unable to get.....". Is
the new macro you suggest the best approach to try to resolve this issue or
is there another way. Macro works fine with XP/Excel2003 but founders with
Vista/Excel2007. Suggestions?
 
I am having trouble using the "Date", "Xirr" and "Xnpv" properties fo the
worksheet function class
 
I am having trouble using the "Date", "Xirr" and "Xnpv" properties fo the
worksheet function class
Click to expand...

Excel worksheet functions that correspond to native VBA functions
aren't exposed by the WorksheetFunction class. Excel's DATE
functionality is provided by VBA's native DateSerial function, so DATE
isn't available. Functions that are in the Analysis Tool Pack aren't
available via WorksheetFunction. To use those functions, go to the
Tools menu in Excel, choose Add-Ins, and check "Analysis Took Pack
VBA" (note this is not the normal "Analysis Tool Pack" add-in). With
than add-in loaded, you can call ATP functions directly (rather than
via WorksheetFunctions).

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)
 
Have read this article but still need help.

I want to be able to have my user:

1. Click command button
2. Call the Edit-->Links menu
3. Allow user to select (from list) existing link to change
4. Click on change source
5. Click OK and macro ends

(There are some other instructions to hide columns I have omitted)

Please help. I have been on this all day.
 
Try the macro recorder whilst doing the steps 2 through 5

I get this code....................

ActiveWorkbook.ChangeLink Name:="Allskeds.xls", NewName:= _
"C:\Program Files\Microsoft Office\Exceldata\8_test.xls", _
Type:=xlExcelLinks

Add your hiding columns code to the above.

Assign the macro to a command button or a button from the Forms Toolbar.


Gord Dibben MS Excel MVP
 
Back
Top