Excel VBA programmatic validation problem

[ZoomZoom]

Hi, newbie post here - I've trawled the archives and can't seem to fin
an answer to my problem.
I'm using Win2000/Excel 2000/VB6

Basically I'm trying to add drop down list style validation to a cell.
I believe this should be possible using the validation parameter of
range:

Range("e5").Validation.Add Type:=xlValidateList, _
AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, _
Formula1:=txt

Where text is a comma separated string ("M,F" in this case)

Every attempt to use validation in such a way gives the error
"Run-Time error '1004': Application-defined or object-defined error".
I've played with many variations on the above syntax, and the sam
error message always results.

Any ideas what I need to do to get this working? Might it be m
references or something like that

 

[Jim Rech]

You have to clear out the previous validation:

Range("E5").Validation.Delete

--
Jim Rech
Excel MVP
| Hi, newbie post here - I've trawled the archives and can't seem to find
| an answer to my problem.
| I'm using Win2000/Excel 2000/VB6
|
| Basically I'm trying to add drop down list style validation to a cell.
| I believe this should be possible using the validation parameter of a
| range:
|
| Range("e5").Validation.Add Type:=xlValidateList, _
| AlertStyle:=xlValidAlertStop, _
| Operator:=xlBetween, _
| Formula1:=txt
|
| Where text is a comma separated string ("M,F" in this case)
|
| Every attempt to use validation in such a way gives the error:
| "Run-Time error '1004': Application-defined or object-defined error".
| I've played with many variations on the above syntax, and the same
| error message always results.
|
| Any ideas what I need to do to get this working? Might it be my
| references or something like that?
|
|
| ---
| Message posted
|

 

[ZoomZoom]

Thanks very much for the reply.

I have tried deleting the previous validation as you suggest. I receiv
the same error. I've also tried modifying rather than adding validatio
- same applies again.

Cheers,

Justi

 

[Chip Pearson]

Your code works for me in Excel 97 thru 2003:


Dim Txt As String
Txt = "M,F"
On Error Resume Next
Range("E5").Validation.Delete
On Error GoTo 0
Range("e5").Validation.Add Type:=xlValidateList, _
AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, _
Formula1:=Txt

If you are using a non-USA version of Excel, try changeing "M,F"
to "M;F".

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[ZoomZoom]

I've tried directly cutting and pasting the supplied code into a fresh
VBA Project. I'm using the automatically generated Worksheet_Activate
callback in this case, I've also tried the same thing in a ComboBox
Change procedure. No luck. I always get to the line:

Range("e5").Validation.Add Type:=xlValidateList, _
AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, _
Formula1:=Txt

and the following error is thrown:
"Run-Time error '1004': Application-defined or object-defined error".

On debugging, the xl constants seem to be defined, as is the variable
Txt. Semi-colon seperated text rather than Comma separated seems to
make no difference.
I've tried the whole thing with a variety of definitions of the range
as well.

Thanks for the help - it's making the frustration easier to deal with!

Justin

 

[ZoomZoom]

I've attached a zipped up working example that uses Chip's code i

the
Worksheet activate event.

You couldn't send it by email could you?

The board code has decided to display the zip rather than linking it.
Thanks very much,

Justi

 

[Ed]

Have you tried writing your validations in a range of cells, and then
programmatically setting your validation to that range? That's what I do.

With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=
_
xlBetween, Formula1:="=$AD$" & StCol & ":$AD$" & EndCol
' StCol and EndCol are variables representing the row numbers that
' start and end my column of validations
.IgnoreBlank = False
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With

HTH
Ed

 

[ZoomZoom]

Have you tried writing your validations in a range of cells, and then

programmatically setting your validation to that range? That's what
do.

Tried it... Arrggh - exactly the same error:
"Run-Time error '1004': Application-defined or object-defined error"

I'm almost at the giving up stage.

Anybody know if there are any contraindicators for what I'm trying t
do? Any service packs, prerequisite software or references needed?
Pure and simple - adding validation does not work on any of th
computers I have here (I've tried it on a couple now).

Thanks everyone fo contributing

Justi

 

[Ed]

Can you do it manually using Data>>Validation? If that works, then step
through it with the macro recorder and go from there. If you can't set it
using the menus, then you do have other problems.

Ed

 

[ZoomZoom]

If that works, then step

through it with the macro recorder and go from there.

That's it!
I didn't realise you could do that, how bloomin' useful!

Right, the problem is entirely because I'm trying to do things on
sheet other than Sheet1. As soon as I do it all entirely on sheet1 I'
flying - it appears the validation has to be added with the cells o
Sheet1 selected, otherwise it doesn't work.

Another problem was obscuring this one though: My columns are numbere
rather than lettered, and I can't refer to cells as A1:B4, I have t
use R1C1:R4C2.
What on earth is going on in this case?

Thanks very much for your help here.

Justi

 

[Ed]

I know I've seen that before in the NG. This is what I use to do searches
there:
http://www.rondebruin.nl/Google.htm. Try it out. With "columns numbered"
as search criteria, I got this one that might be useful to you:
http://groups.google.com/groups?hl=...0&as_ugroup=*Excel*&as_usubject=&as_uauthors=
(All of that is supposed to be one line of URL - I don't know how to make
them smaller. You might have to take out some line breaks to make it work.)

HTH
Ed