Results 1 to 8 of 8
  1. #1
    Lounger
    Join Date
    Aug 2013
    Posts
    45
    Thanks
    11
    Thanked 0 Times in 0 Posts

    Protect Range with Password

    Hi,
    Is there a way to make a user enter a password when they enter in cell? Say in Range A2:A32 and E2:E32 excluding Range B,C & D.
    Password protected A2:A32 and E2:E32.

    Code:
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If ActiveCell.Address = Range("A2:A32").Address Then
    pword = InputBox("Enter Password To Edit Code:")
    If pword = "foncesa" Then ActiveCell.Select Else ActiveCell.Offset(1,0).Select
    End If
    End Sub
    Thanks.

  2. #2
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts
    Try something along the lines of:
    Code:
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim pword As String
    If Intersect(Target, Range("A2:A32")) Or Intersect(Target, Range("E2:E32")) Then
      pword = InputBox("Enter Password To Edit Code:")
      If pword = "foncesa" Then
      'do something *without* selecting
      End If
    End If
    End Sub
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  3. #3
    Lounger
    Join Date
    Aug 2013
    Posts
    45
    Thanks
    11
    Thanked 0 Times in 0 Posts
    Thanks Paul Edstein.

    Code:
    If Intersect(Target, Range("A2:A32")) Or Intersect(Target, Range("E2:E32")) Then
    In fact i am getting error '91' Object variable or With block variable not set.

    Thanks.

  4. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Foncesa,

    Use this construction:
    Code:
    Option Explicit
    
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim pword As String
    If (Intersect(Range("A2:A32"), Target) Is Nothing) And (Intersect(Range("E2:E32"), Target) Is Nothing) Then
    Else
      pword = InputBox("Enter Password To Edit Code:")
      If pword = "foncesa" Then
      'do something *without* selecting
      End If
    End If
    End Sub
    The problem is that Intersect does not return a True or False but rather a Range Object. You, therefore, have to check if the Object is Null (e.g. IsNothing) which will return TRUE or FALSE. Since you are testing two ranges you need to do an AND test to return a TRUE if both Intersect statements are NULL then you do NOTHING in the Then statement (the user did not select in the specified ranges) and rather do your processing in the Else clause.

    Of course you could add Not operators to the if statement but that just makes it harder to read IMHO.

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  5. #5
    Lounger
    Join Date
    Aug 2013
    Posts
    45
    Thanks
    11
    Thanked 0 Times in 0 Posts
    Hi,
    Thanks RetiredGeek for solution, when hovering over "A2:A32" & "E2:E32" it demands password but if Escape Key is pressed then without password we can modify the cell. Please have a look.
    Thanks in advance.

  6. #6
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Foncesa,

    Ok, here's one way to handle it.

    Code:
    Option Explicit
    
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    
       Dim pword As String
    
       If (Intersect(Range("A2:A32"), Target) Is Nothing) And (Intersect(Range("E2:E32"), Target) Is Nothing) Then
         '*** Do Nothing ***
       Else    'Prompt for Password
       
           pword = InputBox("Enter Password To Edit Code:")
           
           If pword = "foncesa" Then
             '*** Do Nothing ***
           Else 'Move cursor to Editable cell
               Application.EnableEvents = False
               [B1].Select
               Application.EnableEvents = True
           End If   'pword
           
       End If       'Intersect(Range...
       
    End Sub    'Worksheet_SelectionChange
    The other way would be to protect the cells w/password and unprotect them only if the user provided the correct password then reprotect them when the selection changed again.

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  7. #7
    Lounger
    Join Date
    Aug 2013
    Posts
    45
    Thanks
    11
    Thanked 0 Times in 0 Posts
    Hi,

    Thanks RetiredGeek, Working Perfectly, Great.

    Many Thanks.

  8. #8
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    You might also want to look at the built-in 'Allow users to edit ranges' functionality.
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •