+ Reply to Thread
Results 1 to 6 of 6

Protecting a sheet with a password in VBA

Hybrid View

dagindi Protecting a sheet with a... 11-18-2020, 05:15 PM
TMS Re: Protecting a sheet with a... 11-18-2020, 06:03 PM
dagindi Re: Protecting a sheet with a... 11-19-2020, 12:35 PM
TMS Re: Protecting a sheet with a... 11-19-2020, 12:52 PM
dagindi Re: Protecting a sheet with a... 11-19-2020, 12:54 PM
TMS Re: Protecting a sheet with a... 11-19-2020, 01:00 PM
  1. #1
    Forum Contributor dagindi's Avatar
    Join Date
    06-02-2008
    Location
    New York, NY
    MS-Off Ver
    Excel 2007 & 2010
    Posts
    295

    Protecting a sheet with a password in VBA

    The code below represents change actions for a spreadsheet I am using. The issue I am having is that I originally locked the sheet with a password however once these worksheet changes happen the sheet will lock and unlock but its not password protected.

    I always want the sheet to be password protected after the IF ELSE changes happen.

    I am not sure of the proper code or where to embed the code and sheet password.

    Private Sub Worksheet_Change(ByVal Target As Range)
     ActiveSheet.Unprotect
     
     If Range("B22") = "Select From Drop Down" Then
     Range("B24:B29").Locked = True
     Range("B30:B32").Locked = True
     Range("B34").Locked = True
     ElseIf Range("B22") = "Yes" Then
     Range("B24:B29").Locked = True
     Range("B30:B32").Locked = False
     Range("B34").Locked = False
     Else
     Range("B24:B32").Locked = False
     Range("B34").Locked = False
     End If
      
     If Range("B44") = "Select From Drop Down" Then
     Range("B46:B54").Locked = True
     ElseIf Range("B44") = "Yes" Then
     Range("B46:B51").Locked = True
     Range("B52:B54").Locked = False
     Else
     Range("B46:B54").Locked = False
     End If
      
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
     
    End Sub
    Last edited by dagindi; 11-19-2020 at 12:54 PM.

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    48,501

    Re: Protecting a sheet with a password in VBA

    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, Password:="whatever"
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Forum Contributor dagindi's Avatar
    Join Date
    06-02-2008
    Location
    New York, NY
    MS-Off Ver
    Excel 2007 & 2010
    Posts
    295

    Re: Protecting a sheet with a password in VBA

    I added in the code but now when you access the sheet and start using it, it keeps prompting for the password.

  4. #4
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    48,501

    Re: Protecting a sheet with a password in VBA

    Did you use
    ActiveSheet.Unprotect Password:="whatever"
    at the beginning?

  5. #5
    Forum Contributor dagindi's Avatar
    Join Date
    06-02-2008
    Location
    New York, NY
    MS-Off Ver
    Excel 2007 & 2010
    Posts
    295

    Re: Protecting a sheet with a password in VBA

    As soon as I responded to your post and realized I did not do that. I added it and it works perfectly!

    Many thanks!!

  6. #6
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    48,501

    Re: Protecting a sheet with a password in VBA

    You're welcome. Thanks for the rep.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Forgot password when protecting sheet
    By ACrossley1 in forum Excel General
    Replies: 1
    Last Post: 01-02-2020, 06:38 AM
  2. Password Protecting Hidden Sheet
    By koochandkai in forum Excel General
    Replies: 1
    Last Post: 07-02-2016, 09:00 AM
  3. [SOLVED] Password Protecting Sheet
    By nickhunt in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 10-15-2014, 10:00 AM
  4. Protecting sheet password
    By John Cruz in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-08-2014, 01:29 AM
  5. [SOLVED] Password Protecting a Hidden Sheet
    By JRose0303 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 04-25-2014, 12:30 PM
  6. Protecting sheet with password on close
    By a94andwi in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 10-24-2007, 08:11 PM
  7. Sheet Protecting password
    By Nick in forum Excel General
    Replies: 1
    Last Post: 06-29-2005, 08:05 PM

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