• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1604
  • Last Modified:

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
"Abys" Wallace
"Abys" Wallace
  • 2
  • 2
1 Solution
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?
"Abys" Wallaceself employedAuthor Commented:
@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?
It's straightforward to protect/unprotect a worksheet in code.
ThisWorkbook.Sheets("Data").Unprotect Password:="OptionalPassWord"
ThisWorkbook.Sheets("Data").Protect Password:="OptionalPassWord"

Open in new window

You'll need to decide exactly where to have the code.

I would unprotect the worksheet when data is being transferred to the worksheet and I would protect it when the form is unloaded.

That is of course assuming you aren't using the ControlSource property with any of the controls on the form.

If you are then you would probably be better unprotecting the worksheet when you initialise the form.
"Abys" Wallaceself employedAuthor Commented:
Thank you @imnorie ... and thank you for the tips also

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now