Calling Subroutines/User Functions from Macros
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 |
Calling Subroutines/User Functions from Macros
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 |
Calling Subroutines/User Functions from Macros
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 |
All times are GMT +1. The time now is 03:41 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com