VB to protect cells not working in Excel 2003?
 

I have a spreadsheet we send to numerous vendors that has all the formula
cells protected. We just received a file back from a vendor with incorrect
formulas in it that the vendor should not have been able to change. I'm
reasonably sure the file went to the vendor protected, and I'm trying to
figure out what happened. The file was created in Excel 2002 and works fine
in Excel 2000. The vendor has Excel 2003 which I don't have access to for
testing, and I'm wondering if the protection does not work in the newer Excel
version. I've included the code below. Thanks for any help on this.

Sheets("Period 1").Select
ActiveSheet.Unprotect Password:="vendorprotect"
Cells.Select
Selection.SpecialCells(xlCellTypeFormulas, 23).Select
Selection.Locked = True
'Unprotect Weeks Performance and Plan Units
Range("T7:T120").Select
Selection.Locked = False
Selection.FormulaHidden = False
Range("V7:V120").Select
Selection.Locked = False
Selection.FormulaHidden = False
Range("A1").Select
ActiveSheet.Protect Password:="vendorprotect", DrawingObjects:=True,
Contents:=True, Scenarios:=True

VB to protect cells not working in Excel 2003?
 

worksheet protection is easily broken.

It is supported in all current versions of excel, but as I said it is not
hard to break.

here is some free code to do it:

http://www.mcgimpsey.com/excel/removepwords.html

as an example.

Was your code locked so they couldn't go in and look at the password?

also, perhaps they did a copy and paste to a new sheet in another workbook.
then used that unprotected sheet to replaced your sheet. That worked for
me. (it wouldn't work completely if formulas are hidden).

--
Regards,
Tom Ogilvy


"cottage6" wrote in message
...
I have a spreadsheet we send to numerous vendors that has all the formula
cells protected. We just received a file back from a vendor with
incorrect
formulas in it that the vendor should not have been able to change. I'm
reasonably sure the file went to the vendor protected, and I'm trying to
figure out what happened. The file was created in Excel 2002 and works
fine
in Excel 2000. The vendor has Excel 2003 which I don't have access to for
testing, and I'm wondering if the protection does not work in the newer
Excel
version. I've included the code below. Thanks for any help on this.

Sheets("Period 1").Select
ActiveSheet.Unprotect Password:="vendorprotect"
Cells.Select
Selection.SpecialCells(xlCellTypeFormulas, 23).Select
Selection.Locked = True
'Unprotect Weeks Performance and Plan Units
Range("T7:T120").Select
Selection.Locked = False
Selection.FormulaHidden = False
Range("V7:V120").Select
Selection.Locked = False
Selection.FormulaHidden = False
Range("A1").Select
ActiveSheet.Protect Password:="vendorprotect", DrawingObjects:=True,
Contents:=True, Scenarios:=True

VB to protect cells not working in Excel 2003?
 

Hi Tom, thanks for the response. Yes, the code was password protected so the
vendor couldn't see the code unless he guessed what the password was to
unlock it. Interesting thought about copying the worksheet; maybe I'll call
the vendor and just ask him what he did. Thanks again.

"Tom Ogilvy" wrote:

worksheet protection is easily broken.

It is supported in all current versions of excel, but as I said it is not
hard to break.

here is some free code to do it:

http://www.mcgimpsey.com/excel/removepwords.html

as an example.

Was your code locked so they couldn't go in and look at the password?

also, perhaps they did a copy and paste to a new sheet in another workbook.
then used that unprotected sheet to replaced your sheet. That worked for
me. (it wouldn't work completely if formulas are hidden).

--
Regards,
Tom Ogilvy


"cottage6" wrote in message
...
I have a spreadsheet we send to numerous vendors that has all the formula
cells protected. We just received a file back from a vendor with
incorrect
formulas in it that the vendor should not have been able to change. I'm
reasonably sure the file went to the vendor protected, and I'm trying to
figure out what happened. The file was created in Excel 2002 and works
fine
in Excel 2000. The vendor has Excel 2003 which I don't have access to for
testing, and I'm wondering if the protection does not work in the newer
Excel
version. I've included the code below. Thanks for any help on this.

Sheets("Period 1").Select
ActiveSheet.Unprotect Password:="vendorprotect"
Cells.Select
Selection.SpecialCells(xlCellTypeFormulas, 23).Select
Selection.Locked = True
'Unprotect Weeks Performance and Plan Units
Range("T7:T120").Select
Selection.Locked = False
Selection.FormulaHidden = False
Range("V7:V120").Select
Selection.Locked = False
Selection.FormulaHidden = False
Range("A1").Select
ActiveSheet.Protect Password:="vendorprotect", DrawingObjects:=True,
Contents:=True, Scenarios:=True

VB to protect cells not working in Excel 2003?
 

The password to the VBE is easily broken, too. (So don't trust it, either!)

And if you open the .xls file in notepad, you may be able to find enough to
unprotect a worksheet.

But I'd put my money on something like the link to J.E. McGimpsey.

cottage6 wrote:

Hi Tom, thanks for the response. Yes, the code was password protected so the
vendor couldn't see the code unless he guessed what the password was to
unlock it. Interesting thought about copying the worksheet; maybe I'll call
the vendor and just ask him what he did. Thanks again.

"Tom Ogilvy" wrote:

worksheet protection is easily broken.

It is supported in all current versions of excel, but as I said it is not
hard to break.

here is some free code to do it:

http://www.mcgimpsey.com/excel/removepwords.html

as an example.

Was your code locked so they couldn't go in and look at the password?

also, perhaps they did a copy and paste to a new sheet in another workbook.
then used that unprotected sheet to replaced your sheet. That worked for
me. (it wouldn't work completely if formulas are hidden).

--
Regards,
Tom Ogilvy


"cottage6" wrote in message
...
I have a spreadsheet we send to numerous vendors that has all the formula
cells protected. We just received a file back from a vendor with
incorrect
formulas in it that the vendor should not have been able to change. I'm
reasonably sure the file went to the vendor protected, and I'm trying to
figure out what happened. The file was created in Excel 2002 and works
fine
in Excel 2000. The vendor has Excel 2003 which I don't have access to for
testing, and I'm wondering if the protection does not work in the newer
Excel
version. I've included the code below. Thanks for any help on this.

Sheets("Period 1").Select
ActiveSheet.Unprotect Password:="vendorprotect"
Cells.Select
Selection.SpecialCells(xlCellTypeFormulas, 23).Select
Selection.Locked = True
'Unprotect Weeks Performance and Plan Units
Range("T7:T120").Select
Selection.Locked = False
Selection.FormulaHidden = False
Range("V7:V120").Select
Selection.Locked = False
Selection.FormulaHidden = False
Range("A1").Select
ActiveSheet.Protect Password:="vendorprotect", DrawingObjects:=True,
Contents:=True, Scenarios:=True




--

Dave Peterson