Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 -------------------------------------------------------------------------------------------------------------------------- |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 -------------------------------------------------------------------------------------------------------------------------- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2003: Protection | Excel Discussion (Misc queries) | |||
Excel 2003 Sheet Protection Issues | Excel Discussion (Misc queries) | |||
Excel 2003: Protection | Excel Discussion (Misc queries) | |||
protection in excel 2003 | Excel Discussion (Misc queries) | |||
Just upgraded to excel 2003 and protection is a problem | Excel Worksheet Functions |