I get an error when I protect an Excel Sheet

November 3, 2010 at 12:49:50
Specs: Windows XP
Application Defined or Object Defined error occurs when I try to protect an excel sheet using the following VB code. I have tried to place ActiveSheet.Unprotect where the error occurs and ActiveSheet.Protect after however they are not password driven and I need the entire sheet to be password protected, just the place where the error occurs to be changed.

Code:

If Target.Address = "$H$7" Then
If Target = "No" Then
With Range("$I$7")
.Validation.Delete
.Value = "None"
End With
Exit Sub
End If
Range("$I$7").ClearContents
With Range("$I$7").Validation
.Delete
.Add Type:=xlValidateList, Formula1:="Ink-1, Ink-1.5, Ink-2, InLay-1,InLay-1.5,InLay-2"
.IgnoreBlank = True
.InCellDropdown = True
End With
End If


See More: I get an error when I protect an Excel Sheet

Report •

#1
November 3, 2010 at 20:38:36
re: "Application Defined or Object Defined error occurs when I try to protect an excel sheet using the following VB code."

Nothing in the code you posted is telling Excel to protect the sheet.

Are you sure you posted the correct code?

P.S. Please use the pre tags when posting code to make it easier for us to read.


Report •

#2
November 4, 2010 at 03:59:25
Sorry about not adding pre code...I will make sure I do from now on. I protect the sheet by going to Tools, Protection, Protect sheet and use a simple password right now.

I have unlocked the cells that I need to work. Everything works fine except I get a runtime error on the line:

.Add Type:=xlValidateList, Formula1:="Ink-1, Ink-1.5, Ink-2, InLay-1,InLay-1.5,InLay-2"

However, if I do add code to unprotect the code and reprotect the code before and after the line everything works fine. However, if I use a password. I am prompted for the password before the code is executed.

I don't want this as other people outside of where I am will be using the spreadsheet.

Any ideas as to what I need to do?


Report •

#3
November 4, 2010 at 04:12:20
To avoid getting the Password prompt, you have to include the Password in the code:

Sheet1.Unprotect Password:="password"
   ..other code...
Sheet1.Protect Password:="password"

Just be sure to protect the VBA code so that users can't view it.


Report •
Related Solutions


Ask Question