Help with Protecting/Unprotecting worksheets in VBA

JakeVBA2009

New Member
Joined
Nov 30, 2009
Messages
16
Hi guys, I've got this code that i'm using to try and protect worksheets but i've got a small problem...I cant seem to make the code recognise that only 1 password will work in order to allow access to the spreadsheet.

Instead, any word thats duplicated twice on both password screens seems to let the user in so i need to make sure that only 1 certain password will allow access to the spreadsheets and anything else is barred from access. (The password used is Password)

Code is below, thanks in advance.

Sub ProtectAll()
Dim sh As Worksheet
Dim myPassword1 As String
myPassword1 = ("password")
Dim sInput1 As String
Dim sinput2 As String
sInput1 = InputBox("Enter password")
sinput2 = InputBox("Confirm password")
If sInput1 = sinput2 Then
MsgBox "User Can Type"
Else
MsgBox "User Cannot type"
For Each sh In ActiveWorkbook.Worksheets
sh.Protect Password:=myPassword1
Next sh
End If
End Sub
 

Some videos you may like

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

taigovinda

Well-known Member
Joined
Mar 28, 2007
Messages
2,639
This should solve the immediate problem:

Rich (BB code):
If sInput1 = sinput2 and sinput2=myPassword1 Then
 

gauntletxg

Well-known Member
Joined
Jul 15, 2008
Messages
636
You're only checking to see if the user entered the same thing in both prompts - you aren't checking to see if it's equal to the actual password. Try something like this

Code:
Dim strInput1 As String, strInput2 As String
Dim pwd As String, sht As Worksheet
pwd = "test"

strInput1 = InputBox("Enter password", "Unprotect")
strInput2 = InputBox("Confirm password", "Unprotect")

If strInput1 = strInput2 And strInput1 = pwd Then
    ' Correct password
    On Error Resume Next
    For Each sht In ActiveWorkbook.Worksheets
        sht.Unprotect pwd
    Next
    On Error GoTo 0
Else
    ' Incorrect password
End If
 

Smitty

Legend
Joined
May 15, 2003
Messages
29,536
Welcome to the Board!

Noting that you'd really chap my hide for making me type the same thing twice.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,959
Messages
5,599,056
Members
414,281
Latest member
Engjamal2021

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top