UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Can't Unprotect Worksheets Using The Correct Password, Any Version    
 
   
RustEE
post Feb 21 2020, 10:33 AM
Post#1



Posts: 20
Joined: 21-October 19



Hello,
I am having an issue with unprotecting worksheets after I have protected them with VBA. I can unprotect the worksheets using VBA but if I copy and paste the password from the code and use it to try and unprotect the sheet from the user interface it won't allow it.
Here is the code I am using to protect all worksheets.
CODE
Sub ProtectAllWorksheets()
    Dim WS As Worksheet
    On Error Resume Next
    For Each WS In ActiveWorkbook.Worksheets
        WS.Protect (Password = "Test")
    Next WS

End Sub


Here is the code I use to unprotect all worksheets.
CODE
Sub UnProtectAllWorkshets()
    Dim WS As Worksheet
    
    On Error Resume Next
    For Each WS In ActiveWorkbook.Worksheets
        WS.Unprotect (Password = "Test")
    Next WS
    
End Sub


As I have said both codes work but if I type "Test" with or without quotations into the password prompt when trying to unprotect a single sheet I get an invalid password error.
Is there some setting I am not thinking of? I have never had this issue when writing code for protection in other excel applications. I am using version 2016 if that makes a difference.
Go to the top of the page
 
theDBguy
post Feb 21 2020, 12:07 PM
Post#2


UA Moderator
Posts: 77,736
Joined: 19-June 07
From: SunnySandyEggo


Hi. I don't program in Excel, so I am just guessing here, but see what happens if you change your code like this:
CODE
WS.Protect Password:="Test"


--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
DanielPineault
post Feb 21 2020, 12:32 PM
Post#3


UtterAccess VIP
Posts: 7,262
Joined: 30-June 11



WS.Protect "Test"

WS.Unprotect "Test"

Or see https://www.devhut.net/2017/10/01/excel-pro...ect-worksheets/ for functions to do this.

--------------------
Daniel Pineault (2010-2019 Microsoft MVP, UA VIP, EE Distinguished Expert 2018)
Professional Help: https://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: https://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...(you are responsible for your choices and actions)
Go to the top of the page
 
Debaser
post Feb 24 2020, 06:37 AM
Post#4



Posts: 180
Joined: 11-October 18



You're actually protecting your sheet with the password "False". Here's why: http://excelmatters.com/2013/10/03/whats-in-a-colon/
Go to the top of the page
 
RustEE
post Feb 26 2020, 10:42 PM
Post#5



Posts: 20
Joined: 21-October 19



I had that before but it wasn't working for two sheets, they must not have been unprotected before running the protect subroutine again because it seems to be working fine now.
Go to the top of the page
 
ipisors12
post Mar 6 2020, 01:41 PM
Post#6



Posts: 20
Joined: 14-March 19



check out Debaser's reply. Your vba syntax is wrong for the protect.
This post has been edited by ipisors12: Mar 6 2020, 01:41 PM
Go to the top of the page
 
RustEE
post Mar 9 2020, 12:37 PM
Post#7



Posts: 20
Joined: 21-October 19



I did and it works now, my last reply was just to give the reason for the initial issue.
Debaser was using what I originally had as my syntax (before posting not what is in the post itself) but for some reason one page never got protected with the right password.
My best guess is I goofed and didn't have it unprotected before running the subroutine.
Thanks everyone for the replies.
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    8th April 2020 - 04:18 PM