Adding validation dropdown

  • Thread starter Thread starter Joe Fawcett
  • Start date Start date
J

Joe Fawcett

I'm trying to add a dropdown list dynamically and for Excel 2000 and above
the following sub works fine:

Public Sub AddDropdown(ByRef ToCell As Range, ByRef RefCell As Range)
Dim oValidation As Validation
Set oValidation = RefCell.Validation
ToCell.Validation.Delete
ToCell.Validation.Add oValidation.Type, oValidation.AlertStyle,
oValidation.Operator, oValidation.Formula1
ToCell.Validation.ErrorTitle = oValidation.ErrorTitle
ToCell.Validation.ErrorMessage = oValidation.ErrorMessage
End Sub

I give it a reference cell that has validation set as required and a target
cell and copy the properties. However it fails in Excel 97 because the
AlertStyle always returns -1. Now I could hard code the value 1 instead but
I'd like to get to the bottom of this if possible.

Thanks

Joe (MVP)
 
Joe

I'd say this is a bug. I've never heard of it before. It appears that the
..AlertStyle property is one-off of the xlValidAlertStyle constants in 97,
but not in 2000. If I change RefCell's AlertStyle to Information, ToCells
become Warning. RefCell=Warning, ToCell=Stop. Everythings one off.

You can make it work in both versions like this

Public Sub AddDropdown(ByRef ToCell As Range, ByRef RefCell As Range)
Dim oValidation As Validation
Dim lAlertStyle As Long
Set oValidation = RefCell.Validation
If Val(Application.Version) < 9 Then
lAlertStyle = oValidation.AlertStyle + 1
Else
lAlertStyle = oValidation.AlertStyle
End If

ToCell.Validation.Delete
ToCell.Validation.Add oValidation.Type, lAlertStyle, _
oValidation.Operator, oValidation.Formula1
ToCell.Validation.ErrorTitle = oValidation.ErrorTitle
ToCell.Validation.ErrorMessage = oValidation.ErrorMessage
End Sub
 
Dick Kusleika said:
Joe

I'd say this is a bug. I've never heard of it before. It appears that the
.AlertStyle property is one-off of the xlValidAlertStyle constants in 97,
but not in 2000. If I change RefCell's AlertStyle to Information, ToCells
become Warning. RefCell=Warning, ToCell=Stop. Everythings one off.

You can make it work in both versions like this

Public Sub AddDropdown(ByRef ToCell As Range, ByRef RefCell As Range)
Dim oValidation As Validation
Dim lAlertStyle As Long
Set oValidation = RefCell.Validation
If Val(Application.Version) < 9 Then
lAlertStyle = oValidation.AlertStyle + 1
Else
lAlertStyle = oValidation.AlertStyle
End If

ToCell.Validation.Delete
ToCell.Validation.Add oValidation.Type, lAlertStyle, _
oValidation.Operator, oValidation.Formula1
ToCell.Validation.ErrorTitle = oValidation.ErrorTitle
ToCell.Validation.ErrorMessage = oValidation.ErrorMessage
End Sub

--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com
Click to expand...
Many thanks, as it's version 97 I shaln't bother asking for a fix :)

Thanks again, at least it wasn't me. Although it's odd no-one's spotted it
(or perhaps they have). Am I taking an unusual route to adding dropdon boxes
then? I though this way I just set a model one, on a hidden sheet) and then
it's easier to manage changes. When the user clicks on a 'New Job' button I
add a few dropdowns and default dates etc. The reason I don't copy existing
lines is that there may not be any.

Thanks for any views.

Joe (MVP)
 
Joe
Many thanks, as it's version 97 I shaln't bother asking for a fix :)
Click to expand...

That's wise.
Thanks again, at least it wasn't me. Although it's odd no-one's spotted it
(or perhaps they have). Am I taking an unusual route to adding dropdon boxes
then?
Click to expand...

I don't think so. I was shocked that nothing came up on google for this
issue. I've copied validation in that way here

http://www.dicks-blog.com/excel/files/Sheet1.htm

but I was firmly entrenched in XL2000 by that time, so I never noticed the
problem.
I though this way I just set a model one, on a hidden sheet) and then
it's easier to manage changes. When the user clicks on a 'New Job' button I
add a few dropdowns and default dates etc. The reason I don't copy existing
lines is that there may not be any.
Click to expand...

That seems like a pretty good way to store everything you need for
validation (except for this bug, of course). I would have hard coded
everything or made a class or something, but I think storing it all in a
real validation object is pretty slick. It would be nice if you could just
set one validation equal to another

Range("B1").Validation.Add Template:=Range("a1").Validation

Sounds like a feature request, but only you and I would use it<g>
 
Back
Top