excel 2000 vs 2003: drop-down box locking issues

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

I've been using drop down list boxes in an excel 2003 worksheet. In 2003, I
am
able to lock the cell with the drop down box and protect the worksheet, which
then prevents anyone from selecting a new value from the drop down boxes and
changing the contents of that cell. However, when I try the same procedure
in excel 2000, the drop down box never locks (though it's property says it's
locked), and can still allow new selections from the box. Does anyone know
of a workaround for this, or is this just a flaw in 2000? Thanks!
 
In Excel 2000 and earlier versions, you can change the selection in a
data validation dropdown, if the list is from a range on the worksheet.

If the list is typed in the data validation dialog box, the selection
can't be changed.

In Excel 2002 and later versions, neither type of dropdown list can be
changed if the cell is locked and the sheet is protected.

This MSKB article has information on the previous behaviour:

XL97: Error When Using Validation Drop-Down List Box
http://support.microsoft.com/default.aspx?id=157484
 
Is there any way I can (in XL2000) make those lists locked from changes when
the sheet is protected? It's essential I have the validation for the lists
referring to a range on the worksheet (and not with the list items in the
validation directly). Thanks!
 
I don't know of any other way to prevent users from selecting an item
from the dropdown list in Excel 2000.

Could you remove the dropdown from the cell when you protect the sheet?
(Data>Validation, Settings tab, In-cell dropdown)
 
That does indeed work. Only trouble is, I'm trying to change the cell's data
validation settings through VBA code, and Excel is a bit quirky when it comes
to validation. If a cell doesn't have validation, excel throws an error when
I use VBA to check it's validation settings.

I'm trying to work around this. Thanks for your previous help!
 
You could test for data validation using SpecialCells, e.g.:

'=====================
Dim c As Range
Dim ws As Worksheet
Set ws = ActiveSheet

For Each c In ws.UsedRange
If Not Intersect(c, ws.Cells.SpecialCells(xlCellTypeAllValidation)) _
Is Nothing Then
With c.Validation
If .Type = xlValidateList Then
.InCellDropdown = False
End If
End With
End If
Next c
'====================
 
Depending on what you're doing, it may be easier to just delete it first and add
it back the way you want.

dim myCell as range
mycell.validation.delete
'add it back here...

And another way to test to see if the cell has validation:

Option Explicit
Function HasValidation(rng As Range) As Boolean

Dim myValidationType As Long

Set rng = rng(1)

myValidationType = -1
On Error Resume Next
myValidationType = rng.Validation.Type
On Error GoTo 0

HasValidation = CBool(myValidationType > -1)

End Function

Sub testme()

With ActiveSheet
MsgBox HasValidation(.Range("a1")) & vbLf & HasValidation(.Range("b1"))
End With

End Sub
 
Back
Top