+ Reply to Thread
Results 1 to 2 of 2

Vba code to lock a range of cells before saving

Hybrid View

  1. #1
    Registered User
    Join Date
    07-09-2013
    Location
    Ahmedabad,India
    MS-Off Ver
    2010
    Posts
    1

    Question Vba code to lock a range of cells before saving

    I am generating a sheets wherein I want to check whether the cells are filled and if they contain some values then lock those cells on clicking save button. My code is as given below:

    Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Sheets("Sheet18").Unprotect Password:="****"
    Sheets("Sheet19").Unprotect Password:="****"
    Sheets("Sheet20").Unprotect Password:="****"
    Sheets("Sheet21").Unprotect Password:="****"
    Sheets("Sheet22").Unprotect Password:="****"
    ActiveSheet.Protect Contents:=False
    For Each Cell In Range("H5:H24,J5:J24")
    If Cell <> "" Then Cell.Locked = True
    If Cell = "" Then Cell.Locked = False
    Next
    ActiveSheet.Protect Contents:=True
    Sheets("Sheet18").Protect Password:="****"
    Sheets("Sheet18").Protect UserInterfaceOnly:=True
    Sheets("Sheet19").Protect Password:="****"
    Sheets("Sheet19").Protect UserInterfaceOnly:=True
    Sheets("Sheet20").Protect Password:="****"
    Sheets("Sheet20").Protect UserInterfaceOnly:=True
    Sheets("Sheet21").Protect Password:="****"
    Sheets("Sheet21").Protect UserInterfaceOnly:=True
    Sheets("Sheet22").Protect Password:="****"
    Sheets("Sheet22").Protect UserInterfaceOnly:=True
    End Sub
    Whenever I click on save, it asks me to unprotect sheets individually using the password. What is the problem?

  2. #2
    Valued Forum Contributor
    Join Date
    06-16-2006
    Location
    Sydney, Australia
    MS-Off Ver
    2013 64bit
    Posts
    1,394

    Re: Vba code to lock a range of cells before saving

    I think you are using userinterfaceonly incorrectly. Take a look at this.

    http://www.cpearson.com/excel/Protection.aspx
    My approach to providing help is to help you to help yourself. So my answers won't always solve your problem, but hopefully you can learn enough to solve the problem yourself and be more self sufficient for the experience.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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