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
MikeCEOAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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
MikeCEOAuthor 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
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

MikeCEOAuthor 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
MikeCEOAuthor 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
MikeCEOAuthor 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
MikeCEOAuthor 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
MikeCEOAuthor 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
MikeCEOAuthor 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
MikeCEOAuthor 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

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
MikeCEOAuthor Commented:
OK, thanks, job done!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Office

From novice to tech pro — start learning today.