I've a number of sheets in a workbook

One sheet has a button which calls

Private Sub DoPreStock_Click()

I'd like to be able to call this sub from a macro ( E.g.
Sheet3.DoPrestockClick()), invoked from a menu, but don't
know the syntax ( Or even if it's possible ).

Can someone help please.

TIA
Neil

Sub auto_open()
Dim cmbWMB As CommandBar
Dim cmbCtl As CommandBarControl
Set cmbWMB = CommandBars("Worksheet Menu Bar")
Set cmbCtl = cmbWMB.Controls.Add(msoControlPopup)
With cmbCtl
.Caption = "User Macros"
.Visible = True
With .Controls.Add(Type:=msoControlButton)
.Caption = "Pre Stock Routine"
.OnAction = "DoPreStock"
End With
End With
End Sub

Move the routine into a module and name:

Sub DoPreStock
...code
End Sub

Menu will only appear on the opening of the file.

To remove the menu item
Sub auto_close()
With CommandBars("Worksheet Menu Bar")
.Controls("User Macros").Delete
End With
End Su

-----------------------------------------------
~~ Message posted from http://www.ExcelTip.com
~~View and post usenet messages directly from http://www.ExcelForum.com

~~Now Available: Financial Statements.xls, a step by step guide to creating financial statements

Hi Neil,

I'd like to be able to call this sub from a macro ( E.g.
Sheet3.DoPrestockClick()), invoked from a menu, but don't
know the syntax ( Or even if it's possible ).


The calling syntax is right, but you have to change the Private keyword
to Public:

Public Sub DoPreStock_Click()

Regards,

Jan Karel Pieterse
Excel MVP
www.jkp-ads.com