PDA

View Full Version : macros cannot be found



maryam
05-19-2007, 05:45 AM
I have made a toolbar and assign macros to the controls of the toolbar, when I open a file that I made with the controls of this toolbar elswhere to edit, it gives me error that Macro.... cannot be found and before macri is the location of the first file. How can I make macros available?

Simon Lloyd
05-19-2007, 06:17 AM
You need to assign the macro's like this:

Sub Workbook_Open()

With Application
.CommandBars.ActiveMenuBar.Enabled = False
.CommandBars("Formatting").Visible = False
.CommandBars("Standard").Visible = False
.CommandBars("your menubar name").Visible = True
.CommandBars("your menubar name").Controls("your button name").OnAction = "your sub name"
.CommandBars("your menubar name").Controls("your button name").OnAction = "your sub name"
.DisplayFullScreen = False
.DisplayFormulaBar = False
.DisplayStatusBar = False
End With
End Sub
the first 3 lines turn standard menubars off, the 4th you need to insert your menu bar name and the next 2 lines (which you can repeat) are to assign subs to buttons when the workbook is opened (modify with your names). don't forget the things you turn off (False) in workbook_open need to be turned back on (True) in your workbook_close!

maryam
05-19-2007, 07:41 PM
I have this coding, but still get the error. I need to open another workbook which is Data1 when the first workbook opens.

Private Sub Workbook_Open()

Dim PathName As String

'All irrelevant toolbars are hidden
Run HideAllToolBars

Application.ScreenUpdating = False
PathName = Application.ThisWorkbook.Path & "\Data1.xls"
Workbooks.Open (PathName)
ActiveWindow.Visible = True
Application.ScreenUpdating = True

'Sub AssignMacros()
With Application.CommandBars("Object Palette1")
With .Controls("&Cancel Choice")
.OnAction = "BreakChoice"
End With
With .Controls("&Connector")
.OnAction = "AddConnector"
End With
With .Controls("&Material Stream")
.OnAction = "AddStreams"
End With
.
.
.
End Sub

lucas
05-19-2007, 08:47 PM
post the workbook maryam. We need to see it. There is no reason for the code to be in a different workbook.

Bob Phillips
05-20-2007, 02:22 AM
You should add the commandbat and controls, not just referencing them.

maryam
05-20-2007, 11:04 PM
what do u mean by adding not just referancing, I made a toolbar and controls are there. where should I add?

geekgirlau
05-21-2007, 12:32 AM
Can you please post the workbook?

maryam
05-21-2007, 01:33 AM
I can send my workbook but it may not work becasue I am using HYSYS library and other referances.
I will explain here. I have a toolbar with which I make a diagram of my interest then I have a menubar which has new/open/save to save my worksheet( this is seprate from Excel save) and later when I use open and open the worksheet that I saved previousely, I cannot run the macros which are assigned to the controls of the toolbar any more and it give me error.

lucas
05-21-2007, 08:20 AM
I have this coding, but still get the error. I need to open another workbook which is Data1 when the first workbook opens.

Private Sub Workbook_Open()

Dim PathName As String

'All irrelevant toolbars are hidden
Run HideAllToolBars

Application.ScreenUpdating = False
PathName = Application.ThisWorkbook.Path & "\Data1.xls"
Workbooks.Open (PathName)
ActiveWindow.Visible = True
Application.ScreenUpdating = True

'Sub AssignMacros()
With Application.CommandBars("Object Palette1")
With .Controls("&Cancel Choice")
.OnAction = "BreakChoice"
End With
With .Controls("&Connector")
.OnAction = "AddConnector"
End With
With .Controls("&Material Stream")
.OnAction = "AddStreams"
End With
.
.
.
End Sub
Are the macro's BreakChoice, AddConnector and AddStreams in a standard module in the same workbook as the toolbar above?

You open a different workbook:

PathName = Application.ThisWorkbook.Path & "\Data1.xls"

but you don't do anything with it...you appear to just open it....???
Are you trying to run macro's listed above from the Data1.xls?
If so...you are going about this all wrong. You shoud save your macro's as an addin to make them available to any open workbook and if you create a menu in a new workbook you will still probably have to reference the macro's in the data1.xla directly.

Ideally, all macros called by a toolbar in a workbook where the toolbar is created by code...should be in the same workbook as the code that creates the toolbar. It should all be in the same workbook.