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

Posted on 2012-09-08
Last Modified: 2012-09-08
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
Question by:"Abys" Wallace
    LVL 33

    Expert Comment

    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?

    Author Comment

    by:"Abys" Wallace
    @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?
    LVL 33

    Accepted Solution

    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.

    Author Closing Comment

    by:"Abys" Wallace
    Thank you @imnorie ... and thank you for the tips also

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Why You Should Analyze Threat Actor TTPs

    After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

    Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
    Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
    This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
    This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    9 Experts available now in Live!

    Get 1:1 Help Now