Calling Subroutines/User Functions from Macros

  • Thread starter Thread starter Neil Strong
  • Start date Start date
N

Neil Strong

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

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
 
Back
Top