Proecting Sheet Marco Question

[fedas18]

I have a spreadsheet that I am going to lock with only a few selected cells to be able to be edited. ON the same spreadsheet I have macro bottons. However when I protect the sheet, my macros do not work.

Is there a way around this?

Thanks!

 

[Iggy]

Sub Whatever()
ActiveSheet.Unprotect Password:="Password1"

~~~~~~The rest of your Code~~~~~~

ActiveSheet.Protect Password:="Password1", DrawingObjects:=True, _
Contents:=True, Scenarios:=True

End Sub

 

[fedas18]

I have to make my password "password1" now?

 

[Iggy]

No. Sorry.
Just change "Password1" to whatever your password is that you are using. Do it in both places; the 'unprotect' and the 'protect'. (leave the quotes"" though)
Then it will unlock the sheet with your password, complete your macro, then lock it back up again.

 

[fedas18]

Thanks for the explaination!

If I wanted to have certain cells that were editable, I don't have to do that through a macro right?

 

[jeffreybrown]

By default all cells are locked.

To unlock/lock certain cells when a worksheet is protected.

Select all cells: Ctrl + A

Format cells: Ctrl + 1 >> protection tab

Check or uncheck the Locked check box.

For your situation, highlight the cells you want to unprotect and follow the steps above.

 

[fedas18]

Hi,

I'm sorry to bother you again but I am still having trouble getting this to work.

Sub Macro12()
'
' Macro12 Macro
'
ActiveSheet.Unprotect Password:="password"
Sheets("Deficit Debt Service").Select
Range("M6").Select
Range("M6").GoalSeek Goal:=0, ChangingCell:=Range("F4")
Sheets("Executive Summary").Select
Range("A1").Select
ActiveSheet.Protect Password:="password", DrawingObjects:=True, _
Contents:=True, Scenarios:=True
End Sub


the password on the locked sheets are all "password"

any idea what I am doing wrong?

 

[jeffreybrown]

Take a look here

This might help

 

[fedas18]

Thanks for the link. That is a great source.

I used the below macro which worked:

Sub Macro12()
'
' Macro12 Macro
'
ActiveSheet.Unprotect Password:="password"
Sheets("Deficit Debt Service").Select
ActiveSheet.Unprotect Password:="password"
Sheets("Deficit Debt Service").Select
Range("M6").Select
Range("M6").GoalSeek Goal:=0, ChangingCell:=Range("F4")
Sheets("Executive Summary").Select
Range("A1").Select
ActiveSheet.Protect Password:="password"
Sheets("Deficit Debt Service").Select
ActiveSheet.Unprotect Password:="password"
End Sub


THANKS FOR ALL YOUR HELP! I would not have been able to do this without you.

Thanks!

 

[Iggy]

You are selecting to different sheets in your macro.
The Command is to unprotect the active sheet.
So you will have to select the sheet then unprotect it. run your code then protect it afterwards.

So if you are not on the sheet 'Deficit Debt Service' when you start your macro, you are unprotecting the wrong sheet. Then you switch over to 'Executive Summary' and you do not unprotect that one.

Try this: (I THINK it will work)

Sub Macro12()
'
' Macro12 Macro
'
'Select the sheet first then unlock it
'
Sheets("Deficit Debt Service").Select
ActiveSheet.Unprotect Password:="password"
Range("M6").Select
Range("M6").GoalSeek Goal:=0, ChangingCell:=Range("F4")
ActiveSheet.Protect Password:="password", DrawingObjects:=True, _
Contents:=True, Scenarios:=True
'
'Protect that sheet then select the next one
'unprotect it and move on
'
Sheets("Executive Summary").Select
ActiveSheet.Unprotect Password:="password"
Range("A1").Select
ActiveSheet.Protect Password:="password", DrawingObjects:=True, _
Contents:=True, Scenarios:=True
End Sub

Let me know if it works for you.......