MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Dumb Macro Question


Posted by Scott on April 03, 2001 7:49 AM

I have two macros attached to a spreadsheet. I also have a custom menu that executes the macro.

When I distribute the spreadsheet to another machine, I get a message stating that there is already a spreadsheet by that name open, close the current spreadsheet or rename it.

How do I get the menu to recognize that the macro is associated with the open sheet and avoid the error message?


Posted by Dave Hawley on April 03, 2001 8:20 AM


Hi Scott

Not a dumb question at all. What you need to do is attach the custom menu bar to the Workbook. You do this via View>Toolbars>Customize, select your menubar then click "Attach" and copy it to the Workbook.


Now you will need some code to delete the custom menubar when you close the Workbook. Don't worry this isn't as drastic as it sounds, it will automatically appear again when you re-open.

Place this code in the Workbook module. Right click on the sheet picture, top left next to "File"

Private Sub Workbook_Activate()
'Show the Custom toolbar
Application.CommandBars("mymenubar").Visible = True
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
IsClosed = True 'Closing so set to True

If Cancel = True Then IsClosed = False 'Changed their mind
End Sub

Private Sub Workbook_Deactivate()

On Error Resume Next 'In Case it's already gone.
If IsClosed = True Then 'Workbook is closing
Application.CommandBars("mymenubar").Delete
Else 'They have activated another Workbook
Application.CommandBars("mymenubar").Visible = True
End If

End Sub


Change "mymenubar" to the name you have.

This code will delete your menubar when you close the workbook, so it is not available to any other Workbook. If the user activates another Workbook when yours is open, it will hide your menubar.

This also prevents any changes that a user may try to make to your menubar from sticking, as soon as they close and re-open it will revert back to normal. This of course means that if you wish to make any changes, you will need to unattach the menubar first, make the changes, then attach again and save.

I also have some code on my website that will hide all other Excel menubars upon opening (except your custom one) and restore them on closing or deactivation. To see this code follow my link to my website and click "VBA Tips and Tricks"

Good luck

Dave

OzGrid Business Applications