Getting unexpected error message

[Bob]

Near the end of my procedure (which is located in PERSONAL.XLS) , I have the
following lines of code:

Application.DisplayAlerts = False
ActiveWorkbook.ProtectSharing SharingPassword:="password"
ActiveWorkbooks.Close SaveChanges:=False
Application.DisplayAlerts = True

(Please note that my procedure starts out by opening a workbook located on a
network shared drive.)

When Excel executes the 3rd line of code above, my procedure stops and I get
the following error message:

Run-time error.
This workbook contains macros recorded or written in Visual Basic.
Macros cannot be viewed or edited in shared workbooks.

Given that Application.DisplayAlerts has been set to False, I don't
understand why the error message is appearing.

Does anyone have any ideas on how I can modify the lines above to prevent
the error message from appearing? Thanks.

 

[Tom Ogilvy]

If you don't save changes, what is the point of making a change in the status
of the workbook.

All messages are not suppressed by Application.DisplayAlerts

 

[JLGWhiz]

If the workbook was password protected when it was opened, and you did
nothing to remove that protection, why would you need to add the protection a
second time, especially when you don't want to save the modifications at that
point anyhow? Or am I reading this code wrong.

 

[Bob]

Tom / JLGWhiz,

Prior to the lines of code I showed below, my procedure opens the workbook
and unshares it. I then modify some data within the workbook and then
re-save it.

The lines of code show below are the lines that are excuted after I have
re-saved the workbook. I need to re-share the workbook (similar to if I
clicked on Tools, Protection, Protect and Share Workbook...). When the 3rd
line of code below is executed, Excel does indeed re-share the workbook.
Afterwards, however, execution stops when I then try to Close the workbook.

Does this additional info help?

Thanks again for any assistance.
Bob

 

[Bob]

Sorry, I meant to say, "When the 2nd line of code below is executed..."

 

[Tom Ogilvy]

So you are saying the workbook is sitting opened shared.

I don't know how you will retain that setting unless you save the workbook.
Maybe Excel does something sneaky and updates the file to show it is save
without saving the workbook, but I wouldn't think so.

Nonetheless, I can't say why you are getting an error.

 

[Bob]

Yes, I first save the workbook, then re-share it (it was in share mode when I
first opened it and then I unshared it), and finally close it. When I
perform these steps manually, everything works fine. But when I try to do it
in VBA, that's when I get the error message. Go figure.

Thanks all the same.
Bob