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
--------------------------------------------------------------------------------------------------------------------------
|