Link to home
Start Free TrialLog in
Avatar of "Abys" Wallace
"Abys" WallaceFlag for United States of America

asked on

Excel VBA: Activate / Deactivate "Read-Only" on a Worksheet

Hi Experts!

Quick hopefully simple question ...  How do you turn on the "Read Only" feature in a workbook.

My Goal:
I have a data entry workbook that when the end user closes the userform (frmTracker) or when the userform deactivates I would like for the "Read-Only" feature to turn on on the "data" worksheet to prevent any modifications.  But as long as the userform (frmTracker) is "Activated" I would like for the "Read-Only" to turn Off.

Is this possible without running into Run-time Errors?  also I'm working with list objects (tables).  

Below is the code for when the userform Initializes:
Private Sub UserForm_Initialize()
    Set oSht = ThisWorkbook.Sheets("data")
    With Me
        .Width = 245
        .comboMgr.List = oSht.Range("mgrList").Value
        .comboSup.List = oSht.Range("supList").Value
        .comboSpecialist.List = oSht.Range("repList").Value
        .comboBehavior.List = oSht.Range("segmentList").Value
        ' shows today's date
        .txtDate.Value = Format(Date, "short date")
        'used a label to prevent any one changing entry
        .lblCoach.Caption = "Hi,  " & CurUser
    End With
    Set oSht = ThisWorkbook.Sheets("Data")
    Set oLo = oSht.ListObjects("CFSData")
    Me.EnableEvents = True
End Sub

Open in new window


Thank you
Avatar of Norie
Norie

The only way I know of having a file intentionally as Read Only would be to open the file as such.

Why not just protect the worksheet with data?
Avatar of "Abys" Wallace

ASKER

@imnorie thank you .. I wouldn't mind doing the alternative (protecting only the sheet with the data) .. How would I be able to accomplish this once the end user closes out of the userform?
ASKER CERTIFIED SOLUTION
Avatar of Norie
Norie

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thank you @imnorie ... and thank you for the tips also