Can I add a Macro to right-click Menu?
 

Hello, I would like to know if it is possible to add after "Hyperlink" a
command to run a certain Macro. It would be better if that extra option in
the rightclick menu is visible through a Macro runned at the opening of that
specific file or something not to make a mayor change to the configurations
in Excel because the computer is from the company, not mine...

thanks!

Can I add a Macro to right-click Menu?
 

Sub Workbook_Open()
With Application.CommandBars("Cell").Controls.Add(tempo rary:=True)
.BeginGroup = True
.Caption = "Clear Formats"
.OnAction = "MyMacros.xla" & "!ClearFormatting"
End With
End Sub

Also you should delete it when the workbook closes.

Sub Workbook_BeforeClose(Cancel As Boolean)
Application.CommandBars("Cell").Controls("Clear Formats").Delete
End Sub


Gord Dibben MS Excel MVP


On Tue, 10 Oct 2006 09:38:02 -0700, Ed wrote:

Hello, I would like to know if it is possible to add after "Hyperlink" a
command to run a certain Macro. It would be better if that extra option in
the rightclick menu is visible through a Macro runned at the opening of that
specific file or something not to make a mayor change to the configurations
in Excel because the computer is from the company, not mine...

thanks!

Can I add a Macro to right-click Menu?
 

Hi Ed

You can try this

Use the activate and deactivate events of the workbook in the thisworkbook module to run the macro

Sub Test()
Dim Num As Long

Call Delete_Test

Num = Application.CommandBars("Cell"). _
FindControl(ID:=1576).Index

With Application.CommandBars("Cell").Controls.Add(Type: =msoControlButton, befo=Num + 1)
.OnAction = ThisWorkbook.Name & "!Yourmacroname"
.Caption = "Run_MyMacro"
End With
End Sub


Sub Delete_Test()
On Error Resume Next
Application.CommandBars("cell").Controls("Run_MyMa cro").Delete
On Error GoTo 0
End Sub


Sub Yourmacroname()
MsgBox "Hi"
End Sub


See also
http://www.rondebruin.nl/menuid.htm#Add


--
Regards Ron de Bruin
http://www.rondebruin.nl



"Ed" wrote in message ...
Hello, I would like to know if it is possible to add after "Hyperlink" a
command to run a certain Macro. It would be better if that extra option in
the rightclick menu is visible through a Macro runned at the opening of that
specific file or something not to make a mayor change to the configurations
in Excel because the computer is from the company, not mine...

thanks!

Can I add a Macro to right-click Menu?
 

Hello Gorb and Ron, thanks both but I haven't managed to use your codes, Im
not sure if I am doing it correctly...

-----------

Gorb:
In your code, on the 5th line I just need to change the file name and macro
name right? (Im using Excel 2007 Beta) so in this case would be:

..OnAction = "File Name.xlsm" & "!NotePad"

right? Notepad is a macro I have in another module. Well if yes, that is
what I have done, then I run the macro but nothing seems to happen...

-----------

Ron:
I didn't quite understood what you meant by the activate and deactivate
events of the workbook in the thisworkbook module to run the macro. I just
added a module with this code, change on line 7 the name for File Name.xlsm
(I'm using the Beta version 2007, and I think it needs quotation marks on the
file name, anyways I tried with and without) and then when I run your macro I
get:

Run-time error '91':
Object Variable or With block variable not set

then on debug:

Num = Application.CommandBars("Cell"). _
FindControl(ID:=1576).Index

highlighted in yellow

------------

What do I need to correct in order to get the codes working?
thanks again!

Can I add a Macro to right-click Menu?
 

hi Ed

Tested in Beta2 TR asnd working OK there

Copy the code I posted in a module of the file and only change

.OnAction = ThisWorkbook.Name & "!Yourmacroname"

To
.OnAction = ThisWorkbook.Name & "!Notepad"


Then in the Thisworkbook module add this

Private Sub Workbook_Activate()
Call Test
End Sub

Private Sub Workbook_Deactivate()
Delete_Test
End Sub


Now when you go to another workbook it will delete the menu item and when you go back it add it.



--
Regards Ron de Bruin
http://www.rondebruin.nl



"Ed" wrote in message ...
Hello Gorb and Ron, thanks both but I haven't managed to use your codes, Im
not sure if I am doing it correctly...

-----------

Gorb:
In your code, on the 5th line I just need to change the file name and macro
name right? (Im using Excel 2007 Beta) so in this case would be:

.OnAction = "File Name.xlsm" & "!NotePad"

right? Notepad is a macro I have in another module. Well if yes, that is
what I have done, then I run the macro but nothing seems to happen...

-----------

Ron:
I didn't quite understood what you meant by the activate and deactivate
events of the workbook in the thisworkbook module to run the macro. I just
added a module with this code, change on line 7 the name for File Name.xlsm
(I'm using the Beta version 2007, and I think it needs quotation marks on the
file name, anyways I tried with and without) and then when I run your macro I
get:

Run-time error '91':
Object Variable or With block variable not set

then on debug:

Num = Application.CommandBars("Cell"). _
FindControl(ID:=1576).Index

highlighted in yellow

------------

What do I need to correct in order to get the codes working?
thanks again!

Can I add a Macro to right-click Menu?
 

The filename is the name of the file where you have the macro "notepad" if
separate from the activeworkbook.

If "notepad" is in a module in the activeworkbook, you should not need that.

OnAction = "notepad"

I have not yet bothered with the 2007 beta so not sure if any changes were made
in that version but works fine in 2003


Gord


On Tue, 10 Oct 2006 11:51:02 -0700, Ed wrote:

Hello Gorb and Ron, thanks both but I haven't managed to use your codes, Im
not sure if I am doing it correctly...

-----------

Gorb:
In your code, on the 5th line I just need to change the file name and macro
name right? (Im using Excel 2007 Beta) so in this case would be:

.OnAction = "File Name.xlsm" & "!NotePad"

right? Notepad is a macro I have in another module. Well if yes, that is
what I have done, then I run the macro but nothing seems to happen...

-----------

Ron:
I didn't quite understood what you meant by the activate and deactivate
events of the workbook in the thisworkbook module to run the macro. I just
added a module with this code, change on line 7 the name for File Name.xlsm
(I'm using the Beta version 2007, and I think it needs quotation marks on the
file name, anyways I tried with and without) and then when I run your macro I
get:

Run-time error '91':
Object Variable or With block variable not set

then on debug:

Num = Application.CommandBars("Cell"). _
FindControl(ID:=1576).Index

highlighted in yellow

------------

What do I need to correct in order to get the codes working?
thanks again!

Can I add a Macro to right-click Menu?
 

Ed

And also change the macro name in both subs

Run_MyMacro to Notepad

Better not us a macro name like notepad because it is a program

Good night


--
Regards Ron de Bruin
http://www.rondebruin.nl



"Ron de Bruin" wrote in message ...
hi Ed

Tested in Beta2 TR asnd working OK there

Copy the code I posted in a module of the file and only change

.OnAction = ThisWorkbook.Name & "!Yourmacroname"

To
.OnAction = ThisWorkbook.Name & "!Notepad"


Then in the Thisworkbook module add this

Private Sub Workbook_Activate()
Call Test
End Sub

Private Sub Workbook_Deactivate()
Delete_Test
End Sub


Now when you go to another workbook it will delete the menu item and when you go back it add it.



--
Regards Ron de Bruin
http://www.rondebruin.nl



"Ed" wrote in message ...
Hello Gorb and Ron, thanks both but I haven't managed to use your codes, Im
not sure if I am doing it correctly...

-----------

Gorb:
In your code, on the 5th line I just need to change the file name and macro
name right? (Im using Excel 2007 Beta) so in this case would be:

.OnAction = "File Name.xlsm" & "!NotePad"

right? Notepad is a macro I have in another module. Well if yes, that is
what I have done, then I run the macro but nothing seems to happen...

-----------

Ron:
I didn't quite understood what you meant by the activate and deactivate
events of the workbook in the thisworkbook module to run the macro. I just
added a module with this code, change on line 7 the name for File Name.xlsm
(I'm using the Beta version 2007, and I think it needs quotation marks on the
file name, anyways I tried with and without) and then when I run your macro I
get:

Run-time error '91':
Object Variable or With block variable not set

then on debug:

Num = Application.CommandBars("Cell"). _
FindControl(ID:=1576).Index

highlighted in yellow

------------

What do I need to correct in order to get the codes working?
thanks again!

Can I add a Macro to right-click Menu?
 

Hello again, I really tried both codes and checked several times that
everything was as instructed, but I don't get it to work... I really don't
know what is happening, but thanks anyways for your time and patience...

,Ed

Can I add a Macro to right-click Menu?
 

Send me your test file private Ed

I look at it then for you

--
Regards Ron de Bruin
http://www.rondebruin.nl



"Ed" wrote in message ...
Hello again, I really tried both codes and checked several times that
everything was as instructed, but I don't get it to work... I really don't
know what is happening, but thanks anyways for your time and patience...

,Ed