Enable workbook_open macros in a shared workbook - Excel 200

[Smitty]

I have a workbook with some user forms that I'd like to share. I use a workbook_open string to disable most of Excel's functionality, so users can't screw anything up (our reps just got PC's last month), but when I share the woorkbook and then try to open it, the workbook_open string bombs out. Although the user forms can still be launched.

Sub Protect_All()

Application.ScreenUpdating = False

Application.Caption = "Customer Master Maintenance"

Application.MacroOptions Macro:="Protect_All", Description:="", _
ShortcutKey:="e"

Application.DisplayStatusBar = False
Application.DisplayFormulaBar = False
Application.MoveAfterReturn = False

Application.Goto Reference:="Data_Form"
Range("A1").Select
With ActiveWindow
.DisplayHeadings = False
.DisplayHorizontalScrollBar = False
.DisplayVerticalScrollBar = False
.DisplayGridlines = False
.DisplayZeros = False
End With
ActiveSheet.Protect UserInterfaceOnly:=True

How can I disable Excel like this and still share the workbook?

Thanks,

Smitty

 

[Mark O'Brien]

Re: Enable workbook_open macros in a shared workbook - Excel

Two questions:

1. What do you mean by: "I use a workbook_open string"? Are you referring to the Workbook_Open event? I don't see this listed in the code.
2. What are you really trying to accomplish here? What are you trying to stop the user from doing?

If you are "sharing" your workbook there are a lot of limitations imposed by MS inherently when you share. To get a list of these, open Excel help and type in a search for "share workbook limitations". To this end, you will see that certain items like protecting the macros etc are futile, because any changes will not be implemented.

To paraphrase a MS Excel MVP, what you are trying to achieve may be an exercise in futility.

 

[Smitty]

Re: Enable workbook_open macros in a shared workbook - Excel

In the Workbook_Open event, I call another macro (the portion that I posted originally) that disables all File Menus and toolbars, as well as protecting all worksheets.

Our PC users have only had their PC's for a few weeks and I've found it's best to take away all Excel control and functionality. I give them what they need to work with (Print, Save & Close, etc.) with macro assigned buttons.

I'd like to share this particular file as it's going to be accessed by 125 sales representatives. I didn't want to split the file up for them individually, but I also want to be able to protect it, so they can't alter anything they shouldn't.

Thanks for your help.