CODEWITHSUNDEEP
vbaexcelmacros
sady
sady

Reputation: 301

Macro enabled workbook read only when shared

I have a macro enabled workbook. It has protected worksheet and workbook. Workbook includes 3 Sheets. Sheet1 named as Main Sheet2 named as Tab2 Sheet3 named as Tab3

I have disabled cells on Tab2 and I have a macro that should throw prompt to user enable cells or not. If User selects "yes" cells gets enabled and drop down on "Main" sheet will be changed to yes.

Macro works fine for me. I have to share the workbook to the other users via e-mail.

Users saves the workbook on his machine and selects "Enable Editing" and "Enable Macros".

But the Workbook name at the top shows XXXX[Read-only] and cells are Tab2 are not getting enabled when users clicks "ok" on the prompt.

Do I have an option to open the workbook Read/Write mode? Or any other option for me so that protection works fine?

Private Sub Worksheet_Activate()

    NotifyUserGeneral

End Sub



Private Sub NotifyUserGeneral()
Dim mMessageDisplayed As Boolean
Dim message  As Integer

    If ActiveSheet.ProtectContents = True And Not mMessageDisplayed Then
        message = MsgBox("Cells are locked on current sheet, press ok to Unlock ", vbOKCancel)
        mMessageDisplayed = True
        If message = vbOK Then
        Sheets("MAIN").Range("G11") = "YES"
        Else
        Sheets("MAIN").Range("E29") = "NO"
    End If
End If
End Sub

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Cancel = True
End Sub

Please do suggest

Upvotes: 0

Views: 513

Answers (1)

Darren Bartrup-Cook
Darren Bartrup-Cook

Reputation: 19737

I think this is the code you're after.

Note: I've moved the declaration of mMessageDisplayed to the top of the module otherwise it gets reset to FALSE each time NotifyUserGeneral is called.

Also note: It may be better to move NotifyUserGeneral to a normal module if you want to call it from other sheets - remember to move the mMessageDisplayed declaration to.

Option Explicit

Private mMessageDisplayed As Boolean

Private Sub Worksheet_Activate()

    NotifyUserGeneral

End Sub

Private Sub NotifyUserGeneral()

    If ActiveSheet.ProtectContents And Not mMessageDisplayed Then
        mMessageDisplayed = True
        If MsgBox("Cells are locked on current sheet, press ok to Unlock", vbOKCancel + vbInformation) = vbOK Then
            ActiveSheet.Unprotect
            ThisWorkbook.Worksheets("MAIN").Range("G11") = "YES"
        Else
            ThisWorkbook.Worksheets("MAIN").Range("E29") = "NO"
        End If
    End If
End Sub

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Cancel = True
End Sub

Upvotes: 1

Related Questions