Running Macros in a Shared File
 

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

Running Macros in a Shared File
 

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

GMC

Running Macros in a Shared File
 

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

On Apr 30, 1:32*pm, "Dan R." wrote:
Try something like this:

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

* * '''''runmacros'''''

* * 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 notsharedand all of themacrosran no
problem. However I want to automate the execution of themacros
(through windows scheduler and a small .tcl file), but in theshared
environment I manually have to unshare the workbook, unprotect the
sheets, to run themacros. Themacroswont run in theshared
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- Hide quoted text -

- Show quoted text -