VBA and sharepoint

[laurawr]

Hi
I have an excel document stored on sharepoint that will be used by all of my colleagues.

However even though people have had the sufficient training on sharepoint people still manage to open an excel document on sharepoint as read only and do a few hours of work then cannot save the data due to it being in read only mode.

How can i bring message box up if the document opened via sharepoint is read only and close the application for them to re open it as the correct format... currently i am using this code and it does not work.

Private Sub Workbook_Open()
If Me.ReadOnly = True Then
       MsgBox "This is a read only document. Please open this document as "Checkout and Edit'"
    End If
End Sub

Thanks in advance

L

 

[ogo]

Try this,



Private Sub Workbook_Open()
If ActiveWorkbook.ReadOnly Then
MsgBox "This is a read only document. Please open this document as "Checkout and Edit'"
End If
End Sub


Thanks,
Ogo

 

[laurawr]

Hi,
thanks for your reply,

but i have already tried your suggestion aswell and it still wont work, i really dont know why

:/

Thanks

 

[Andrew Poulsom]

Your code didn't compile, but this worked for me with macros enabled:

Private Sub Workbook_Open()
    If Me.ReadOnly = True Then
       MsgBox "This is a read only document. Please open this document as ""Checkout and Edit""."
    End If
End Sub

Make sure that the code is in the ThisWorkbook module.

 

[laurawr]

Hi
I have tried that but however it still doesnt work.

It is already in the ThisWorkbook module aswell.. when I open the document on sharepoint it just runs as usual with no messagebox.

?

Thanks

L

 

[MikeDBMan]

I am convinced this is more than just a read-only flag that Excel normally sees. The SharePoint site has a different flag on the file and I don't know how to remove it either. Please post here if you learn how to solve this problem as I have the same one.

 

[Andrew Poulsom]

Maybe you need to use the CanCheckIn and/or CanCheckOut properties, although I can't test it.

 

[MikeDBMan]

I can check it but I don't know the syntax to use those. Can you provide some code, I'll test it and hopefully we'll solve this problem for me and for the other person on this post. Thanks!

 

[MikeDBMan]

Andrew, the checkinwithversion worked. All I did was issue this command:

ActiveWorkbook.CheckInWithVersion

And my file was checked back in on SharePoint. However, I do NOT see a checkout option on activeworkbook. Can you help with that?

We're getting very close!!!!!!!!!!!!!!!!!!!!!

 

[Andrew Poulsom]

A couple of links:

http://msdn.microsoft.com/en-us/library/aa223808(v=office.11).aspx

http://msdn.microsoft.com/en-us/library/aa223809(v=office.11).aspx