OnAction Cannot find Macro ??

[Andrew Kennard]

Hi all

I'm new to VBA and am trying to add my macro to a menu

I've created the menu fine but no matter what I try the OnAction for the
menu item says it cannot find the marco ?? even when it is in the
ThisWorkbook module and not in a separate module file ??

I've tried evreything I can think of, Starting again cut 'n' paste etc and I
just cannot get it to find the macro that just has the MyForm.Show in it !!
??. I even tried putting this directly into the OnAction but no luck

TIA

Andrew Kennard

 

[Bob Phillips]

Andrew,

It would help to see the code, but you should put the macro in a standard
module, not in ThisWorkbook.

Show us the macro, and the code that creates a menu.


--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Andrew Kennard]

Thanks for your generous offer Bob. The code is as follows :-

ThisWorkBook Code
-------------------------------

Private Sub WorkBook_Open()

Dim cbWSMenuBar As CommandBar
Dim Ctrl As CommandBarControl, muCustom As CommandBarControl
Dim iHelpIndex As Integer

Set cbWSMenuBar = Application.CommandBars("Worksheet menu bar")
iHelpIndex = cbWSMenuBar.Controls("Help").Index

Set muCustom = cbWSMenuBar.Controls.Add(Type:=msoControlPopup, _
Before:=iHelpIndex, Temporary:=True)
For Each Ctrl In cbWSMenuBar.Controls
If Ctrl.Caption = "&ServeBase" Then
cbWSMenuBar.Controls("ServeBase").Delete
End If
Next Ctrl

With muCustom
.Caption = "&ServeBase"
With .Controls.Add(Type:=msoControlButton)
.Caption = "&Import and Format"
.OnAction = "ImportFormat"
End With
With .Controls.Add(Type:=msoControlButton)
.Caption = "Print Batch"
.OnAction = "PrintBatch"
End With
End With

End Sub

-------------------------------------------
Module Code

Sub ImportFormat()
Import1.Show
End Sub
----------------------------------------------

It's probably something really obvious but I can't see it !

BTW the actual error is "The Macro gg7.xls!ImportFormat cannot be found"

TIA

Andrew Kennard

 

[Bob Phillips]

Andrew,

I just plugged the code in, and it worked fine for me.

What you sometimes find is that you get that error if there is a run time
error in the code, rather than the actual error, you get a not found error.

Are you sure you have named the Userform Import1. One way to check for these
errors is to run the macro ImportFormat directly, not from the button. If
there is a code error, it will highlight the actual error.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Andrew Kennard]

Yes If I put the cursor in the Sub ImportFormat and click run .... my form
shows

 

[Bob Phillips]

Then I am at a loss for the moment Andrew.

Would you like to send me your workbook, and I will take a look?

--

HTH

RP
(remove nothere from the email address if mailing direct)