Excel 2002 VBA macro deployment troubles

[Andy CA5]

Hi,
I have created a spreadsheet with various bits of vba code, and attached
each macro to a custom menu bar, but i have problem when i deploy it to the
network for my users.
on my machine it works fine, when its copied to the network, i get an error
when running the macros "A document with the same name ...... is already
open. you can not open two document .. etc"

the main question is what method should i use to deploy a simple
spreadsheet, for use with 30 employees, 1 person will be entering data, the
rest as read-only.
i don't want to put macro stuff in the personel.xls , and the spreadsheets
location will not be hard coded, as it may change.

i just want to include the code in the sheet, all self contained.

thanks

 

[Tom Ogilvy]

It sounds like your menus still refer to the original location of the file.
Most people use the workbook_open event to totally rebuild the menus so the
correct file is referenced in the onaction property. then in the before
close event, they include code to destroy the menus. As an added assurance,
they use similar code in the workbook_open event to destroy and residual
menus so you don't get duplicates.

On Error Resume Next
' workbook to destroy the menus
On Error goto 0

this bypasses errors if the menus don't exist.

 

[Andy CA5]

thanks,
do you have any sample code you could post.

 

[Tom Ogilvy]

Private Sub Workbook_Open()


Dim objBar As Office.CommandBar


'Delete CommandBar if it exists
On Error Resume Next
Application.CommandBars("DataEntry").Delete
On Error GoTo 0


Set objBar = Application.CommandBars.Add("DataEntry")


With objBar.Controls.Add(temporary:=True)
.Caption = "Anti Virus"
.OnAction = "PasteAntiVirus"
End With


With objBar.Controls.Add(temporary:=True)
.Caption = "Audio"
.OnAction = "PasteAudio"
End With


With objBar.Controls.Add(temporary:=True)
.Caption = "Backup"
.OnAction = "PasteBackup"
End With


End Sub