Unshare and Reshare a workbook

  • Thread starter Thread starter Matt
  • Start date Start date
M

Matt

I have the following to unshare and reshare a workbook. It also adds
and removes worksheet protection based on which command button is
pushed (using two control toolbox - command buttons on worksheet). The
code works the first time I click on one of the buttons but fails on
subsequent clicks. Below is the code. Advice on how to fix this
problem would be appreciated.
Thanks. Matt

Private Sub CommandButton1_Click()

If ThisWorkbook.MultiUserEditing Then
ThisWorkbook.UnprotectSharing
ThisWorkbook.ExclusiveAccess
End If

For Each sh In ThisWorkbook.Worksheets
sh.Unprotect
Next

If Not ThisWorkbook.MultiUserEditing Then
ThisWorkbook.SaveAs Filename:=ThisWorkbook.FullName, _
accessMode:=xlShared
End If

End Sub

Private Sub CommandButton2_Click()

If ThisWorkbook.MultiUserEditing Then
ThisWorkbook.UnprotectSharing
ThisWorkbook.ExclusiveAccess
End If

For Each sh In ThisWorkbook.Worksheets

sh.Protect
Next

If Not ThisWorkbook.MultiUserEditing Then
ThisWorkbook.SaveAs Filename:=ThisWorkbook.FullName, _
accessMode:=xlShared
End If
End Sub
 
I have the following to unshare and reshare a workbook. It also adds
and removes worksheet protection based on which command button is
pushed (using two control toolbox - command buttons on worksheet). The
code works the first time I click on one of the buttons but fails on
subsequent clicks. Below is the code. Advice on how to fix this
problem would be appreciated.
Thanks. Matt

Private Sub CommandButton1_Click()

If ThisWorkbook.MultiUserEditing Then
  ThisWorkbook.UnprotectSharing
    ThisWorkbook.ExclusiveAccess
   End If

For Each sh In ThisWorkbook.Worksheets
sh.Unprotect
Next

If Not ThisWorkbook.MultiUserEditing Then
    ThisWorkbook.SaveAs Filename:=ThisWorkbook.FullName, _
        accessMode:=xlShared
End If

End Sub

Private Sub CommandButton2_Click()

If ThisWorkbook.MultiUserEditing Then
  ThisWorkbook.UnprotectSharing
    ThisWorkbook.ExclusiveAccess
   End If

For Each sh In ThisWorkbook.Worksheets

sh.Protect
Next

If Not ThisWorkbook.MultiUserEditing Then
    ThisWorkbook.SaveAs Filename:=ThisWorkbook.FullName, _
        accessMode:=xlShared
End If
End Sub
Click to expand...

Come on this is a pretty good post...and it would really make my day
if somebody took a stab at it.
 
Back
Top