Macro Giving Warning if Word File Opens Read Only

Hi - can anyone help me write a macro that will display a message box if a Word file is opened as Read Only? Ideally I would also like a similar macro for Excel,

MS Office 2010, WIndows 7

Thanks
Mike235Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

 
MacroShadowCommented:
Word:
Place this code in the ThisDocument module
Private Sub Document_Open()
    If ActiveDocument.ReadOnly Then
        MsgBox "Document is open in Read-Only mode!"
    End If
End Sub

Open in new window


Excel:
Place this code in the ThisWorkbook module
Private Sub Workbook_Open()
    If ActiveWorkbook.ReadOnly Then
        MsgBox "Workbook is open in Read-Only mode!"
    End If
End Sub

Open in new window

0
 
Mike235Author Commented:
Thanks. I should have said that I want these to work in all docs and workbooks. Pasting your code into Normal.dot into an  Sub AutoOpen() works fine for all docs based on Normal.dot; perfect - thanks for this. How do I achieve the same effect with Excel?  I tried creating a similar Sub AutoOpen in Personal.xlsb but that didn't work. Any ideas how I would so this?

Regards,

Mike
0
 
MacroShadowCommented:
Place this code in the Personal.xlsb:
Private WithEvents app As Application

Private Sub app_WorkbookOpen(ByVal Wb As Workbook)
    If ActiveWorkbook.ReadOnly Then
        MsgBox "Workbook is open in Read-Only mode!"
    End If
End Sub

Private Sub Workbook_Open()
    Set app = Application
End Sub

Open in new window

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Mike235Author Commented:
OK thanks, will check tomorrow - getting late in London.

One thing I noticed about the Word code is that when opening a Word attachment from an Outlook email the "Read Only" message  appears even when the document is not read-only. Any idea how I can correct that?

Rgds,

Mike
0
 
MacroShadowCommented:
I don't use Outlook so I have no way of checking.
Perhaps this should be asked in a new question.
0
 
Mike235Author Commented:
Hi - unfortunately the excel code doesn't work. Please see attached screen shot. Using Excel 2010.
Screenshot.jpg
0
 
MacroShadowCommented:
The red line has to be the first line in the module.
0
 
Mike235Author Commented:
Unfortunately this is no better, see screenshot
Screenshot.jpg
0
 
MacroShadowCommented:
Tested and working, on my machine, Windows 7 32 bit:
Option Explicit

Private WithEvents app As Application

Private Sub app_WorkbookOpen(ByVal Wb As Workbook)
    If Wb.ReadOnly Then
        MsgBox "Workbook is open in Read-Only mode!"
    Else
        MsgBox "Workbook is open in Read-Write mode!"
    End If
End Sub

Private Sub Workbook_Open()
    Set app = Application
End Sub

Open in new window

0
 
Mike235Author Commented:
I wish I could say the same. Please see attached screenshot.
Screenshot.jpg
0
 
[ fanpages ]IT Services ConsultantCommented:
^ Place the code within a "Class Module" (not a standard code "Module")
0
 
Mike235Author Commented:
If I do this the good news is no syntax error. The bad news is that it appears that the module does not run - see screenshot.
Untitled.jpg
0
 
[ fanpages ]IT Services ConsultantCommented:
Is "PERSONAL.XLSB" saved within the "XLSTART" folder (Application.StartupPath)  folder, & did you completely close MS-Excel & re-open it (before opening the workbook that is marked as "Read-only")?
0
 
Mike235Author Commented:
Yes to both questions, I'm afraid.
0
 
MacroShadowCommented:
Put this code in the  "ThisWorkbook" module (Not in a class module!) of your Personal.xlsb.

Option Explicit

Public WithEvents app As Application

Private Sub Workbook_Open()
    Set app = Application
End Sub

Private Sub app_WorkbookOpen(ByVal Wb As Workbook)

    If Wb.Name <> "Personal.xlsb" Then
        If Wb.ReadOnly Then
            MsgBox Wb.Name & " Workbook is open in Read-Only mode!"
        Else
            MsgBox Wb.Name & " Workbook is open in Read-Write mode!"
        End If
    End If
End Sub

Open in new window

0
 
Mike235Author Commented:
Ok, nearly there. I needed to change the case to PERSONAL.XLSB, which now works fine. Pls advise me how to  amend the code so it does not consider any *.xlam files. Then I think we're done.
0
 
MacroShadowCommented:
Option Explicit

Public WithEvents app As Application

Private Sub Workbook_Open()
    Set app = Application
End Sub

Private Sub app_WorkbookOpen(ByVal Wb As Workbook)
    If UCase(Wb.Name) <> "PERSONAL.XLSB" And InStr(1, UCase(Wb.Name), UCase(".xlam")) < 1 Then
        If Wb.ReadOnly Then
            MsgBox Wb.Name & " Workbook is open in Read-Only mode!"
        Else
            MsgBox Wb.Name & " Workbook is open in Read-Write mode!"
        End If
    End If
End Sub

Open in new window

0

Experts Exchange Solution brought to you by ConnectWise

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
 
Mike235Author Commented:
OK, thanks, job done!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.