Running Macros in a Shared File

[grantmcewan]

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


GMC

 

[Dan R.]

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

 

[grantmcewan]

Dan, thank you very much for the code, I will try it out as soon as
possible. Thanks Grant