Sheet protection unlocking VBA

Question

I am trying to save the sheet with password but I am not able to unlock it with same password, can someone please help me out with getting this resolved.

Sub ClearData()   
ActiveSheet.Unprotect Password = "Kiran123"
Sheets("Filter").Select
Range("A11").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Clear
Sheets("Filter").Select
Range("C3").Select
Selection.Clear

ActiveSheet.Protect Password = "Kiran123"
End Sub

Thanks for checking this, However when I have tried with above code(my code) it doesn't work. However when you try it with only below sub it works, why such ambiguity in both the codes. any specific reason why this is occurring ? would be great if I can get an code to lock the sheet with password.

Sub test()
ActiveSheet.Protect Password:="Kiran123"
ActiveSheet.Unprotect Password:="Kiran123"
End Sub

Are you using On Error Resume Next anywhere in your code? As @Eirikdaude's answer mentions your code shouldn't compile, much less run and this is the only reason I can think of that would cause this. — Aiken
– Aiken, Commented Feb 17, 2015 at 12:42

Tragamor · Accepted Answer · 2015-02-19 23:13:40Z

1

You are switching focus away from your ActiveSheet by using

Sheets("Filter").Select

Instead you can keep a reference to the original spreadsheet then use the reference for your passworded sheet deprotection and protection...

Sub ClearData()

    Dim ws As Worksheet
    Dim pw As String

    Set ws = ActiveSheet
    pw = "Kiran123"

    ws.Unprotect (pw)

    Sheets("Filter").Select
    Range("A11").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Clear
    Range("C3").Select
    Selection.Clear

    ws.Protect (pw)
End Sub

answered Feb 19, 2015 at 23:13

Tragamor

3,6893 gold badges18 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Aiken · Accepted Answer · 2015-02-17 14:21:49Z

1

This sub worked for me:

Sub test()
  ActiveSheet.Protect Password:="Kiran123"
  ActiveSheet.Unprotect Password:="Kiran123"
End Sub

The reason this works, and your sub doesn't, is that in mine I am assigning a named argument while I'd think that the lines in your sub tries to set what precedes the equal sign equal to what comes after, that is the difference between := and =. Honestly, I am a bit surprised your sub ran at all, it certainly didn't run for me.

edited Feb 17, 2015 at 14:21

Aiken

2,6682 gold badges20 silver badges25 bronze badges

answered Feb 17, 2015 at 10:46

eirikdaude

3,2596 gold badges29 silver badges58 bronze badges

2 Comments

Aiken Over a year ago

Edit that information into your answer rather than leaving it as a comment, you can do that by clicking the small 'edit' hyperlink beneath the body of your answer. If possible with an explanation of why that colon makes all the difference.

eirikdaude Over a year ago

@Aiken Done, not entirely sure if my explanation is 100% correct, but it's the best I could do. Feel free to come with further input if there's something I can do to improve the answer.

Gene Skuratovsky · Accepted Answer · 2015-02-17 15:07:21Z

0

The ActiveSheet might not be the "Filter" tab...

Worksheets("Filter").Unprotect Password = "Kiran123"

or switch the second and the third lines in you code posted above :)

answered Feb 17, 2015 at 15:07

Gene Skuratovsky

5812 silver badges6 bronze badges

Collectives™ on Stack Overflow

Sheet protection unlocking VBA

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related