Excel 2003/7 VBA (Un) Protection not working
 

I use Excel2007 and save in 2003-file format for compatibility. I
wrote VBA code to create an in-cell drop-down list based on a certain
value in cell B6.

As I protect the spreadsheet, the drop-down validation list doesn't
work. I added code to unprotect and then protect afterwards again
which worked 100%. My company installed the latest updates on Vista
(not Office2007 as far as I know) and now the PROTECT error code gives
an error. Any ideas on how to fix this? My code below:

------------------------------------------------------------------------------------------------------------
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Target.Address = "$B$7" Then
'Set the validation box property for cell B7 to the correct range
depending on B6
ActiveSheet.Unprotect ---seems to work fine
With ActiveCell
.Validation.Delete --- blank current range name/formula

'--- test value and select appropriate range of category values

If Range("B6").Value = "Domestic" Then
.Validation.Add xlValidateList, , , "=" & "EL_DOM_Cats"
End If
End With

ActiveSheet.Protect ----- ERROR saying Protect method failed.

End If

End Sub
--------------------------------------------------------------------------------------------------------------------------

Excel 2003/7 VBA (Un) Protection not working
 

Perhaps you could use Debra's technique for dependent validation lists, and
avoid having to unprotect and reprotect:

http://contextures.com/xlDataVal02.html

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"Ferdie" wrote in message
...
I use Excel2007 and save in 2003-file format for compatibility. I
wrote VBA code to create an in-cell drop-down list based on a certain
value in cell B6.

As I protect the spreadsheet, the drop-down validation list doesn't
work. I added code to unprotect and then protect afterwards again
which worked 100%. My company installed the latest updates on Vista
(not Office2007 as far as I know) and now the PROTECT error code gives
an error. Any ideas on how to fix this? My code below:

------------------------------------------------------------------------------------------------------------
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Target.Address = "$B$7" Then
'Set the validation box property for cell B7 to the correct range
depending on B6
ActiveSheet.Unprotect ---seems to work fine
With ActiveCell
.Validation.Delete --- blank current range name/formula

'--- test value and select appropriate range of category values

If Range("B6").Value = "Domestic" Then
.Validation.Add xlValidateList, , , "=" & "EL_DOM_Cats"
End If
End With

ActiveSheet.Protect ----- ERROR saying Protect method failed.

End If

End Sub
--------------------------------------------------------------------------------------------------------------------------