VBA Code to Protect/Un-Protect Sheets asking for a password when there isn't one

[bakom002]

Hello Everyone,

I wrote some code to protect/unprotect all of the worksheets in my workbook, and when I run the code in the vba editor it works perfectly fine.

However, I added some shapes to my excel file and then assign the macros to those shapes, so the macro could be run at the click of a button, and now its asking me for a password when I run the macro from the button.

Also, entering the password used to lock the cells doesnt work. Here is the code:

Sub UnProtect()
'Dim ps As String


Application.ScreenUpdating = False
Application.Calculation = xlManual


'ps = "modeldevrocks"
For i = 1 To Sheets.count
    Sheets(i).UnProtect Password:="modeldevrocks"
Next i


Application.ScreenUpdating = True
Application.Calculation = xlAutomatic


End Sub

////////////////////////////////////////////////

Sub Protect()
'Dim ps As String


Application.ScreenUpdating = False
Application.Calculation = xlManual


'ps = "modeldevrocks"
For i = 1 To Sheets.count
    Sheets(i).Protect Password:="modeldevrocks"
Next i


Application.ScreenUpdating = True
Application.Calculation = xlAutomatic


End Sub

Thanks in advanced gals and guys!

 

[salim hasan]

You must use the method UserInterfaceOnly:=True
with can run The macro in protected sheet
like this example

Option Explicit


Sub Protect_sh()
Dim i
For i = 1 To Sheets.Count
    Sheets(i).Protect Password:="Pass", UserInterfaceOnly:=True
Next i
End Sub
'=====================
Sub test()
'test the Macro in protected sheet
Range("f4") = ""
Range("A1:A5") = "I'm Runing in protected sheet"
Columns(1).AutoFit
End Sub

 

[bakom002]

Hey thanks for the response, so I added the code, and it still works when I run the code form the editor, but I still get the "Password Required" pop-up box when I try to run it from the excel button, and the password used to lock/unlock the sheets won't work.

Here is the code now:

Option Explicit
Sub UnProtect()
Dim i


Application.ScreenUpdating = False
Application.Calculation = xlManual


For i = 1 To Sheets.count
    Sheets(i).UnProtect Password:="modeldevrocks"
Next i


Application.ScreenUpdating = False
Application.Calculation = xlManual


End Sub

//////////////////////////////////////////////////////////

Sub Protect()
Dim i


Application.ScreenUpdating = False
Application.Calculation = xlManual


For i = 1 To Sheets.count
    Sheets(i).Protect Password:="modeldevrocks", UserInterfaceOnly:=True
Next i


Application.ScreenUpdating = False
Application.Calculation = xlManual


End Sub

 

[Fluff]

That looks like it's an inputbox that is appearing, not the normal dialogue box.
Are you sure the code you've posted, is the code being run by the button?

 

[bakom002]

Yes I am fairly sure, I right clicked on the the button in excel and assigned the macro that I want to run.

 

[Fluff]

If you add the part in red as shown & the click the button, does the code window pop-up with the line highlighted in yellow?

Sub UnProtect()
Dim i
[COLOR=#ff0000]Stop[/COLOR]

Application.ScreenUpdating = False
Application.Calculation = xlManual


For i = 1 To Sheets.count
    Sheets(i).UnProtect Password:="modeldevrocks"
Next i


Application.ScreenUpdating = False
Application.Calculation = xlManual


End Sub

 

[bakom002]

No it does not actually, it just gives me the pop-up asking for a password still.

 

[bakom002]

Oh my goodness, you were right.... I didn't realize I had similarly named code in there, thank you so much lol

 

[salim hasan]

try Do this
1- insert a CommandButton with name My_Button for example (ActiveX controls)
2 -try to run this macro by pressing the button

Sub test()
Sheets("sheet1").Protect Password:="pass", UserInterfaceOnly:=True
Range("A1:A5") = "Hallo"


End Sub
'============================


Private Sub My_Button_Click()
test
End Sub

 

[Fluff]

Oh my goodness, you were right.... I didn't realize I had similarly named code in there, thank you so much lol

Glad to help & thanks for the feedback