Sharing a workbook on close

[Bairn90]

Hi Guys,

I am completely stumped by this one and in desperate need of help. I have a macro that on close "should" share the workbook if not currently shared. When I run through the code not closing the book, it shares the book. When I close the book I can see it having been shared in the name but when I re-open the spreadsheet low and behold it's not shared.

Anybody know why this is happening?

Sub Auto_Close()
Dim myname, mypath As String


Application.DisplayAlerts = False

ThisWorkbook.Activate
If ThisWorkbook.MultiUserEditing Then
End
Else
    Sheets("Invoice Run Requests").Shapes.Range(Array("Button 4")).Visible = False
    Sheets("Invoice Run Requests").Protect "*********", 0, 1, 1, 1, 0, 0, 0, 0, 0, 0, 0, 0, 1, 1, 0
    ThisWorkbook.Save


    mypath = ThisWorkbook.path
    myname = ThisWorkbook.name
    ChDir mypath
    ActiveWorkbook.SaveAs Filename:=myname, FileFormat:=52, Accessmode:=xlShared
    
    ThisWorkbook.Save
End If


Application.DisplayAlerts = True


End Sub

 

[chuckchuckit]

I was playing around with this some earlier today. This may not be correct - but in my system with a wkbk shared by 2, if the one who opened the shared wkbk 1st closes it, if it is reopened before the 2nd user closes theirs, my original user is no longer able to share it again with the 2nd user.

 

[Bairn90]

Thanks for you're reply Chuck. Not sure we're having exactly the same problem but in any case I've not managed to solve mine by effectively pausing the close of the book. For some reason it worked.

Here's the code I used if anyone is interested

Private Sub Workbook_BeforeClose(Cancel As Boolean)


Cancel = True
Call closing
Cancel = False


End Sub


Sub closing()
Dim MyPath, MyName, auth As String


Application.DisplayAlerts = False


Sheets("Invoice Run Requests").Shapes.Range(Array("Button 1")).Visible = False
ThisWorkbook.Save


If Not ThisWorkbook.MultiUserEditing Then
MyPath = ActiveWorkbook.Path
MyName = ActiveWorkbook.Name
        
Sheets("Invoice Run Requests").Protect "********", 0, 1, 1, 1, 0, 0, 0, 0, 0, 0, 0, 0, 1, 1, 0
ChDir MyPath
ActiveWorkbook.SaveAs Filename:=MyName, FileFormat:=52, Accessmode:=xlShared
End If


Application.DisplayAlerts = True
End Sub

 

[chuckchuckit]

Thanks Bairn for your reply also, with your results. We often wonder how things turned out for others on the threads.

Likely we don't have the same problem as I tossed out my situation that was similar. Glad you got yours fixed.

This coding is a lot of just trial and error till we test all the variables we think may occur using our common sense and experience. It's fun when it works well.

Oh and BTW - welcome to the board.

Chuck