Results 1 to 3 of 3

How to Unprotect and protect cells in a script

Threaded View

  1. #1
    Registered User
    Join Date
    11-17-2012
    Location
    Chicago
    MS-Off Ver
    Excel 2003
    Posts
    11

    How to Unprotect and protect cells in a script

    I have a script for recording passwords. It has a cell where the user enters a code (cell C3) and the script returns (displays) the password in the adjacent cell (D3). The script also allows a user to click a button to generate a new code for a new password. The new code is displayed in cell C4. To make this script work and keep me from mistakenly changing other fields, I have locked the previous 3 cells (C3, D3 and D4 as well as the password and password code cells (G2:F5000). I then Protect the sheet.

    What I would like to do is to lock JUST C3 to allow a user to enter a code. I thought I could do this with the ActiveSheet.Protect command but I can't seem to get it to work. Here is the code, with my attempt commented out:

    Private Sub Worksheet_Change(ByVal Target As Range)
        ' ActiveSheet.Unprotect Password:="password"
        ' Range("c3:g10000").Locked = True - this range encompasses the  3 specific cells and the  password  and  code storage  range
        Dim PW As Range
        If Target.Address <> "$C$3" Then Exit Sub
        Application.EnableEvents = False
        Set PW = Sheet1.Columns(6).Find(Target)
        If Not PW Is Nothing Then
            [D3] = PW.Offset(, 1)
        Else
            MsgBox "Not Found"
        End If
        '  ActiveSheet.Protect Password:="password"
        Range("C3").Select
        Application.EnableEvents = True
    End Sub
    Any help would be appreciated.
    Last edited by jeffreybrown; 10-17-2018 at 06:36 PM. Reason: Added code tags!

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] VB Code to protect unprotect certain cells
    By rizmomin in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-18-2017, 11:47 PM
  2. Protect/unprotect formula cells of entire workbook
    By shafiqe in forum Excel General
    Replies: 5
    Last Post: 07-14-2013, 01:59 AM
  3. [SOLVED] Protect/Unprotect VBA Script
    By Heat in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-19-2013, 01:03 PM
  4. New to macros - script to protect cells if a particular cell is populated
    By gsandifer in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-14-2013, 04:19 PM
  5. Replies: 2
    Last Post: 07-03-2012, 04:42 AM
  6. Macro to unprotect sheet --> unlock cells --> protect again
    By hrhittn23 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 01-02-2011, 07:00 AM
  7. Unselect locked cells when protect/unprotect worksheet
    By eight8 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 04-08-2007, 03:00 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1