Excel

Protect cell or cells on sheet without protecting sheet

Ease of Use

Easy

Version tested with

2003 

Submitted by:

Simon Lloyd

Description:

This enables the user to protect a single cell or group of cells without protecting the whole sheet using a userform for password entry to allow access to the cell 

Discussion:

If you have difficulties in protecting the whole sheet for some reason or would like to protect something in a shared workbook but allow access on entry of a password. This could be for a single cell or cell range that only requires certain people to edit the Data in them. Using this would give you the flexibility of being able to protect or unprotect in shared workbook 

Code:

instructions for use 

			
 '''This gets pasted in to the sheet code module
Private Sub Worksheet_Activate() 
    Set OriginalCell = ActiveCell '''sets variable for use in userform module
End Sub 
 '''This also gets pasted in to the worksheet module
Private Sub Worksheet_SelectionChange(ByVal Target As Range) 
     
    If Not Intersect(Target, Range("A1")) Is Nothing Then '''sets which cell(s) to work with
         ''Use the above line to set a range of cells by changing ("A1") to your range say ("B4:D12")
        Application.ScreenUpdating = False 
        Set ProtectedCell = Target ''sets variable for use in userform module
        UserForm1.Show 
        Application.ScreenUpdating = True 
    Else 
        Set OriginalCell = Target 
    End If 
     
End Sub 
 ''''This gets pasted in to the userform module
Private Sub CmndSubmit_Click() 
    If UserForm1.TextBox1 <> "Password" Then ''''if the password does not match "Password"
        MsgBox "Incorrect Password", vbOKOnly, "Warning" 
        OriginalCell.Select ''''then select this cell, this cell is set in the worksheet module
        Unload Me '''''close the userform
    Else 
        ProtectedCell.Select '''Set in the WorkSheet module
        Unload Me '''''close the userform
        Application.EnableEvents = True 
    End If 
End Sub 
 '''This one gets pasted in to the userform module
Private Sub CmndCancel_Click() 
    MsgBox "Cell Access Denied", vbExclamation, "Access Status" 
    OriginalCell.Select '''''Back to the cell that was selected at worksheet activation
    Unload Me '''''close the userform
End Sub 
 '''This one gets pasted in to the userform module
Private Sub UserForm_initialize() 
    UserForm1.Caption = UserForm1.Caption & ProtectedCell.Address ''Adds the protected cell name to the title of the userform
     ''''ProtectedCell is determined in the WorkSheet module
End Sub 
 '''This also gets pasted in to the userform code module
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer) 
    If CloseMode = 0 Then '''''prevents the X from being used to close the userform
        Cancel = True 
        Call CmndCancel_Click '''go to the cancel button sub
    End If 
End Sub 
 '''This gets pasted in to the ThisWorkBook code module
Private Sub Workbook_BeforeClose(Cancel As Boolean) 
    Dim Sh As Worksheet 
    Dim Ctr As Integer 
    Ctr = 0 ''''give the counter a start point
    For Each Sh In Sheets '''for every sheet in the workbook
        If Sh.Visible Then 
            Ctr = Ctr + 1 ''''if the sheet is visible at 1 to the counter
        End If 
    Next Sh 
    If Ctr = 1 Then '''if we only have one visible sheet in the workbook then show the message
         
        MsgBox "You can not hide the only sheet in the workbook, you must have at least 2 sheets visible before closing", vbOKOnly, "Insufficient Sheets!" 
        Cancel = True 
        Exit Sub '''quits before closing and saving
         
    End If 
    Sheets("Sheet1").Visible = xlVeryHidden '''prevents the sheet from being unhidden via the toolbar
    ThisWorkbook.Save 
End Sub 
 '''This gets pasted in to the ThisWorkBook code module
Private Sub Workbook_Open() 
    Sheets("Sheet1").Visible = True '''If macro's are allowed to run then the sheet is visible again
End Sub 
 '''This also gets pasted in to the thisworkbook code module
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) 
    If SaveAsUI = True Then Cancel = True '''Prevents user saving the workbook as different name
End Sub 
 '''This gets pasted in to a standard module
Option Explicit 
Public OriginalCell As Range 
Public ProtectedCell As Range

How to use:

  1. Firstly we need to open the VBE (visual basic editor) window, this is done by pressing Alt+F11 together, once the window opens you will notice a gray window pane to the left and a VBA Project window to your top left displaying any worksheets and modules, below this you will see the properties window for the current object you are viewing. We now need to create a userform, this is done by right clicking in the VBA project window and choose INSERT | USERFORM, once you have done this the userform object will appear as a gray box with a blue border, to the left of this a small panel will appear with the available controls for the userform this is called the CONTROL TOOLBOX.
  2. Select the Command Button icon by clicking on it (a single gray oblong button), move your mouse pointer to the userform you will see a "+" with a small gray box attached to the bottom left, simply hold down your left mouse button and "Draw" the button to whatever size you want, now repeat the process for another command button and for a Textbox!
  3. Once you have created these click any one of them once, notice the window to the bottom left of your screen change, this is the property window for that item, click the command button once and in the properties window find the title that says Caption and change the word CommandButton1 to whatever you would like to be displayed on the button,repeat for the second button but call it "cancel", also in the properties window just above "Caption" you will see the title "Cancel" change this to True to prevent the user from using the Escape button to get out of the userform and ending the macro prematurely, now click the textbox once find the title in the properties window that says PasswordChar next to this type a single character that you would like to be displayed when text is typed in to this box later (i chose the standard "*")
  4. Now we will get to work pasting the items of code above in to their respective places, in the VBA Project window to the left you will see a userform icon right click it once and choose "View Code", now copy and paste the items from the code above that are labeled for pasting into the userform.
  5. Having finished with the userform for now lets paste the other items of code, right click on the ThisWorkbook icon choose "View Code" and paste the items of code labeled as such from the code above in to this module, next perform the same action on Sheet 1 code module and paste the items of code labeled for pasting to it from the above in to that, your final task is to right click in the VBA Project window and choose INSERT | MODULE and paste the last item of code in to this as this sets Global Variables for use in our userform and worksheet module
  7. You are now ready to test the code!
 

Test the code:

  1. In your workbook select sheet 1 and try to access cell A1, you should get a userform appear waiting for your password, type any thing you like, the characters should appear as * click your command button, assuming you typed the password incorrect you will be presented with a message box telling you its incorrect and then you will be transported back to the last cell you were at, now click A1 again and type "Password" (it is case sensitive!) hit the command button and you should find that you now stay at cell A1 and can type in it!
  3. Now hide all the worksheets in your workbook except for sheet1, now try to close the workbook by either clicking the "X" or choosing File and then Close, you should be presented with a message box telling you you cannot do that, unhide another sheet or sheets try the same, you should find that the workbook will close and save no problem. Now open the workbook and try to save the workbook as a different name, you should find that the Save As dialog box does not appear. Close the workbook for one last test, open another workbook set your macro security to high and close the workbook, try opening the workbook we have been working on but when resented with the Macros security warning and being asked to enable macro's choose No you should find that sheet 1 is not visible nor available from the toolbar under FORMAT | SHEET | UNHIDE. With this workbook open reset you macro security to the desired level and close, you may then reopen the workbook and continue using it normally provided you have agreed to run macros.
  5. If you want to change the cell or cells that are protected then access the worksheet module and change the named cell in the (Target,Range("Xn") where Xn is A1.
 

Sample File:

Cell Password No Save As.zip 13.08KB 

Approved by mdmackillop


This entry has been viewed 699 times.

Please read our Legal Information and Privacy Policy
Copyright @2004 - 2020 VBA Express