Fwd: Running Macros in a Shared File
 

---------- Forwarded message ----------
From: "Dan R."
Date: Apr 30 2008, 1:32*pm
Subject: Running Macros in a Shared File
To: microsoft.public.excel.programming


Try something like this:

Sub test()
* With ThisWorkbook
* * If .MultiUserEditing Then
* * * .Application.DisplayAlerts = False
* * * .UnprotectSharing
* * * .ExclusiveAccess
* * Else
* * * MsgBox "Not shared"
* * * Exit Sub
* * End If
* * For s = 1 To .Sheets.Count
* * * .Sheets(s).Unprotect
* * Next s

* * '''''run macros'''''

* * For s = 1 To .Sheets.Count
* * * .Sheets(s).Protect
* * Next s
* * .SaveAs Filename:=ThisWorkbook.FullName, _
* * * * * * AccessMode:=xlShared
* * .Application.DisplayAlerts = True
* End With
End Sub

--
Dan

On Apr 30, 12:00*pm, wrote:

I have a file that is a "shared" file located on a server, multiple
people access the file daily to update data. There are many hidden
worksheets, protected sheets and locked cells etc... in the workbook.
The file originally was not shared and all of the macros ran no
problem. However I want to automate the execution of the macros
(through windows scheduler and a small .tcl file), but in the shared
environment I manually have to unshare the workbook, unprotect the
sheets, to run the macros. The macros wont run in the shared
environment as I cannot unprotect the sheets (and the corresponding
cells). Is there any way to:

1) Allow the macro to write to a protected locked cell without
unprotecting the worksheets/cells ?

2) a VBA procedure to unshare the workbook, unprotect sheets, run
macro, protect sheets then share workbook kagain.

Thanks
Proxy-Connection: keep-alive
Cache-Control: max-age