+ Reply to Thread
Results 1 to 2 of 2

Using a cell text value as a password to lock sheets

Hybrid View

Mtsbck Using a cell text value as a... 01-22-2012, 05:16 PM
rscsmith Re: Using a cell text value... 01-22-2012, 06:07 PM
  1. #1
    Registered User
    Join Date
    01-22-2012
    Location
    Zgorzelec, Poland
    MS-Off Ver
    Excel 2003, Excel 2007
    Posts
    1

    Question Using a cell text value as a password to lock sheets

    <<<SOLVED>>>

    I solved my issue using : http://www.brainbell.com/tutorials/m...Fell_Swoop.htm. Hope it helps anyone else who stumbles upon the same problem

    As it is my first post on this forum i'd like to say hello to anyone kind enough to look in here and take a second to try and help me with solving this issue.

    I have found this piece of code that is protecting all of the sheets in my workbook with one button click:

    Sub Protect_All()
    For Each objSheet In Worksheets
    If objSheet.ProtectContents = False Then objSheet.Protect "Password"
    Next objSheet
    End Sub


    Yet I need to make it possible to enter the "password" value into a cell instead of just blocking it.

    Is it possible to reffer to a cell text value in VBA code in a way that would allow me to use this cell as a password input?

    as in:

    person A enters "whatever123" into cell E7 and then clicks the "lock" button which then proceeds to protect all the worksheets in the workbook using "whatever123" as a password. Person A then clears the text from field E7. Then after a while of being used by end-users person A needs to access some diffrent functions of the workbook and can input "whatever123" into E7 and click "unlock" button to unprotect all of the files.


    I will appreciate any hints!
    thanks in advance, Matt

    edit:
    The idea is to be able to change the password by simply unlocking the worksheets using old password and then locking them using the new one. At first I thought i made it kind of obvious but then i realized my terrible english could be missleading. Cheers.
    Last edited by Mtsbck; 01-22-2012 at 05:47 PM. Reason: Problem solved

  2. #2
    Valued Forum Contributor
    Join Date
    12-05-2011
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010 & 2013
    Posts
    308

    Re: Using a cell text value as a password to lock sheets

    Hi Matt

    What you propose is entirely possible.

    I would set up a named range in your worksheet for the password. This means that if you move it around (insert/delete rows, etc) you won't have to change the macro.

    In the following example, I've created a named range called "Passw" on Sheet1. Change the code in your example to:-
    If objSheet.ProtectContents = False Then 
        objSheet.Protect Password:=sheets("Sheet1").range("Passw").Value
        Else
        objSheet.Unprotect Password:=Sheets("Sheet1").Range("Passw").Value
        End If
    There are some other options you might want to implement, such as
    [code]
    objSheet.Protect Password:=Sheets("Sheet1").Range("Passw").Value, AllowFiltering:=True, AllowUsingPivotTables:=True
    [code]

    Use the record macro function to see exactly what you want to switch on and off.

    Cheers, Rob.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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