Macro not working with Excel 2003


F

Fernando Gomez

I just upgraded excel 97 to 2003 and I was using some macros saved in
Personal.xls, but now I lost all the custom toolbar that I had before, and
this Macro does not work it says "Can not find prject or library" (macro is
to have 1 custom menu) and stop in Type:=msoControlPopup.

Sub Auto_Open()

Dim NewMenu As Object
Dim SubMenu As Object
Dim ToolBarActivate As Object

Set NewMenu = CommandBars.FindControl(Type:=msoControlPopup, _
Tag:="AuditPack")
If NewMenu Is Nothing Then
Set ToolBarActivate = CommandBars.ActiveMenuBar
Set NewMenu = ToolBarActivate.Controls.Add(Type:=msoControlPopup,
Temporary:=True)
NewMenu.Caption = "CompX Menu"
NewMenu.Tag = "CompX Menu"
Set SubMenu = NewMenu.Controls.Add(Type:=msoControlButton, Id:=1)
With SubMenu
.Caption = "ID File - Sheet"
.OnAction = "UpdateFooter"
End With
Set SubMenu = NewMenu.Controls.Add(Type:=msoControlButton, Id:=1)
With SubMenu
.Caption = "ID File - Workbook"
.OnAction = "UpdateFooters"
End With
Set SubMenu = NewMenu.Controls.Add(Type:=msoControlButton, Id:=1)
With SubMenu
.Caption = "Page break - View"
.OnAction = "ToggleViews"
End With
Set SubMenu = NewMenu.Controls.Add(Type:=msoControlButton, Id:=1)
Set CommandBarActiva = Nothing
Set NewMenu = Nothing
Set SubMenu = Nothing
End If

End Sub

Could somebody help me to fix this and understand how Excel 2003 keeps the
toolbar (
Before it was in Excel8.xlb)

Thanks
 
Ad

Advertisements

D

Dave Peterson

First, your code almost worked for me.

This line:
Set CommandBarActiva = Nothing
should be replaced with:
Set ToolBarActivate = Nothing

It could mean that you're missing a reference in your personal.xls project.

Inside the VBE with personal.xls the active project, click on Tools|References.
Look to see if "microsoft office 11.0 object library" is checked (11.0 is for
xl2003, IIRC).

If it's checked can you try your workbook on a different pc. If it works there,
check the references and come back and match those in the troublesome pc.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top