none
vba code to lock a range RRS feed

  • Question

  • Hi,

    I wanted to lock a range with VBA . And this is the code , I used,

    Sub protectmydata()
    Dim strpassword As String
    Range("C2:D7").Select
    Selection.Locked = True
    Selection.FormulaHidden = True
    strpassword = InputBox("Please enter password.")
    ActiveSheet.Protect Password = strpassword
    End Sub

    >> It locks whole sheet , and not range selected.

    >>When password is put as 1234567 , It doesn't allow to remove the protection . Actually it says wrong password.

    Where is the mistake ? please help.

    regards 

    Friday, November 27, 2015 8:09 AM

Answers

  • By default, ALL cells are locked, but this only becomes effective when the sheet is protected. So if you want to lock only C2:D7, you should unlock all cells first.

    And there should be := between Password and strPassword instead of =

    Sub ProtectMyData()
        Dim strPassword As String
        Cells.Locked = False
        With Range("C2:D7")
            .Locked = True
            .FormulaHidden = True
        End With
        strPassword = InputBox("Please enter password.")
        ActiveSheet.Protect Password:=strPassword
    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Friday, November 27, 2015 8:54 AM

All replies

  • By default, ALL cells are locked, but this only becomes effective when the sheet is protected. So if you want to lock only C2:D7, you should unlock all cells first.

    And there should be := between Password and strPassword instead of =

    Sub ProtectMyData()
        Dim strPassword As String
        Cells.Locked = False
        With Range("C2:D7")
            .Locked = True
            .FormulaHidden = True
        End With
        strPassword = InputBox("Please enter password.")
        ActiveSheet.Protect Password:=strPassword
    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Friday, November 27, 2015 8:54 AM
  • Hi Hans Vogelaar ,

    Thank you .

    It is perfect now .

    Can you please tell me , where this password is stored ?

    regards


    Friday, November 27, 2015 1:19 PM
  • Assuming that your workbook is a .xlsx or .xlsm workbook, it is actually a .zip file (a compressed archive) that contains a series of .xml files. Among these, there is an .xml file for each worksheet:

    The .xml file for a protected worksheet has a section named sheetprotection, for example

    The password is stored in this section, but it is encrypted, so you cannot view it directly.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Friday, November 27, 2015 2:09 PM
  • Thank you Hans Vogelaar,

    Neither do I intend for.

    regards

    Friday, November 27, 2015 4:40 PM