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


"morse100" wrote:

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

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

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

"JLatham" wrote:

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


"morse100" wrote:

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




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

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

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)



"morse100" wrote in message
...


"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




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

"Bob Phillips" wrote:

New and Exciting ... what?

--
---
HTH

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

Actually it all went without a hitch.

Thanks for your advice and help

Morse


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

LOL! Thanks for telling me.



"morse100" wrote in message
...


"Bob Phillips" wrote:

New and Exciting ... what?

--
---
HTH

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

Actually it all went without a hitch.

Thanks for your advice and help

Morse


Bob

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

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


"JLatham" wrote:

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


"morse100" wrote:

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

I am having trouble using the "Date", "Xirr" and "Xnpv" properties fo the
worksheet function class
--
ultima


"JLatham" wrote:

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


"morse100" wrote:

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

I am having trouble using the "Date", "Xirr" and "Xnpv" properties fo the
worksheet function class

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)



On Fri, 24 Oct 2008 15:04:01 -0700, ultima
wrote:

I am having trouble using the "Date", "Xirr" and "Xnpv" properties fo the
worksheet function class

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.

On Sunday, August 05, 2007 5:32 PM morse10 wrote:

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


On Sunday, August 05, 2007 5:46 PM HelpFrom wrote:

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


"morse100" wrote:


On Sunday, August 05, 2007 7:25 PM 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
...


On Sunday, August 05, 2007 10:08 PM HelpFrom 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:


On Monday, August 06, 2007 12:36 PM morse10 wrote:

"JLatham" wrote:



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


On Monday, August 06, 2007 12:38 PM morse10 wrote:

"Bob Phillips" wrote:


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


On Monday, August 06, 2007 12:42 PM morse10 wrote:

"JLatham" wrote:

Hi,

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

Best wishes


Morse


On Tuesday, August 07, 2007 7:34 AM Bob Phillips wrote:

New and Exciting ... what?

--
---
HTH

Bob

(there is no email, no snail mail, but somewhere should be gmail in my addy)


On Tuesday, August 07, 2007 7:35 AM Bob Phillips wrote:

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)



"morse100" wrote in message
...


On Thursday, August 09, 2007 6:48 AM Bob Phillips wrote:

LOL! Thanks for telling me.


On Friday, October 24, 2008 6:03 PM ultim wrote:

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


"JLatham" wrote:


On Friday, October 24, 2008 6:04 PM ultim wrote:

I am having trouble using the "Date", "Xirr" and "Xnpv" properties fo the
worksheet function class
--
ultima


"JLatham" wrote:


On Friday, October 24, 2008 6:13 PM Chip Pearson wrote:

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)



On Fri, 24 Oct 2008 15:04:01 -0700, ultima
wrote:


Submitted via EggHeadCafe - Software Developer Portal of Choice
SharePoint Workflow Custom Input Forms
http://www.eggheadcafe.com/tutorials...put-forms.aspx

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

On Fri, 29 Oct 2010 03:59:39 GMT, John Der Garabedian
wrote:

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.

On Sunday, August 05, 2007 5:32 PM morse10 wrote:

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


On Sunday, August 05, 2007 5:46 PM HelpFrom wrote:

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


"morse100" wrote:


On Sunday, August 05, 2007 7:25 PM 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
...


On Sunday, August 05, 2007 10:08 PM HelpFrom 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:


On Monday, August 06, 2007 12:36 PM morse10 wrote:

"JLatham" wrote:



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


On Monday, August 06, 2007 12:38 PM morse10 wrote:

"Bob Phillips" wrote:


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


On Monday, August 06, 2007 12:42 PM morse10 wrote:

"JLatham" wrote:

Hi,

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

Best wishes


Morse


On Tuesday, August 07, 2007 7:34 AM Bob Phillips wrote:

New and Exciting ... what?

--
---
HTH

Bob

(there is no email, no snail mail, but somewhere should be gmail in my addy)


On Tuesday, August 07, 2007 7:35 AM Bob Phillips wrote:

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)



"morse100" wrote in message
...


On Thursday, August 09, 2007 6:48 AM Bob Phillips wrote:

LOL! Thanks for telling me.


On Friday, October 24, 2008 6:03 PM ultim wrote:

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


"JLatham" wrote:


On Friday, October 24, 2008 6:04 PM ultim wrote:

I am having trouble using the "Date", "Xirr" and "Xnpv" properties fo the
worksheet function class
--
ultima


"JLatham" wrote:


On Friday, October 24, 2008 6:13 PM Chip Pearson wrote:

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)



On Fri, 24 Oct 2008 15:04:01 -0700, ultima
wrote:


Submitted via EggHeadCafe - Software Developer Portal of Choice
SharePoint Workflow Custom Input Forms
http://www.eggheadcafe.com/tutorials...put-forms.aspx