disable validation list in protected area

[micha_delle]

Hi,
i do have an area that is protected for changes (worksheet protection).
This area consists of values selected from a validation list. After
protection of this area it is still possible to select from the list
and therefore to change the content in this area.
Is there a way (VBA) to prevent from this - the validation list should
be somehow disabled.

Thanks in advance - Michael

 

[Dave Peterson]

Maybe...

What version of excel are you using?

If you're using xl97, then it depends on how the Data|Validation was set.
http://www.contextures.com/xlDataVal08.html#Change
(from Debra Dalgleish's site)

If you want to try...

Rightclick on the worksheet tab that should have this behavior. Select view
code and paste this in the codewindow that opens up.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub

On Error GoTo errHandler:

If Me.ProtectContents = True _
And Target.Locked = True Then
With Application
.EnableEvents = False
.Undo
End With
End If

errHandler:
Application.EnableEvents = True

End Sub


ps. In xl2003 (maybe xl2002, too), if the cell is locked and the worksheet
protected, then you can't change that cell--even by using the dropdown in
Data|Validation. (This was a change from previous versions.)

==========
Another option. Use a Dropdown from the Forms toolbar or a combobox from the
control toolbox toolbar. You could disable each of those when you want.

 

[Dave Peterson]

Ps. That routine is untested. I use xl2003 and I can't create a test workbook
that behaves the way you describe.

 

[micha_d]

i am using Excel 2000 - if it works in 2003 i might wait to solve the
problem by update the version ;-)

 

[Dave Peterson]

It was a problem in xl97. xl2k should work ok.

i am using Excel 2000 - if it works in 2003 i might wait to solve the
problem by update the version ;-)

 

[micha_d]

thanks a lot it works fine...