Excel VBA programmatic validation problem
 

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

--
Message posted from http://www.ExcelForum.com

Excel VBA programmatic validation problem
 

You have to clear out the previous validation:

Range("E5").Validation.Delete

--
Jim Rech
Excel MVP
"ZoomZoom " wrote in message
...
| 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 from http://www.ExcelForum.com/
|

Excel VBA programmatic validation problem
 

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

--
Message posted from http://www.ExcelForum.com

Excel VBA programmatic validation problem
 

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 " wrote in
message ...
Thanks very much for the reply.

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

Cheers,

Justin


---
Message posted from http://www.ExcelForum.com/

Excel VBA programmatic validation problem
 

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


---
Message posted from http://www.ExcelForum.com/

Excel VBA programmatic validation problem
 

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

--
Message posted from http://www.ExcelForum.com

Excel VBA programmatic validation problem
 

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 " wrote in message
...
I've attached a zipped up working example that uses Chip's code in
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,

Justin


---
Message posted from http://www.ExcelForum.com/

Excel VBA programmatic validation problem
 

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

--
Message posted from http://www.ExcelForum.com

Excel VBA programmatic validation problem
 

Can you do it manually using DataValidation? 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 " wrote in message
...
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.

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 to
do? Any service packs, prerequisite software or references needed?
Pure and simple - adding validation does not work on any of the
computers I have here (I've tried it on a couple now).

Thanks everyone fo contributing

Justin


---
Message posted from http://www.ExcelForum.com/

Excel VBA programmatic validation problem
 

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

--
Message posted from http://www.ExcelForum.com

Excel VBA programmatic validation problem
 

I know I've seen that before in the NG. This is what I use to do searches
the
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=e...as_uauthors%3D
(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

"ZoomZoom " wrote in message
...
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 a
sheet other than Sheet1. As soon as I do it all entirely on sheet1 I'm
flying - it appears the validation has to be added with the cells on
Sheet1 selected, otherwise it doesn't work.

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

Thanks very much for your help here.

Justin


---
Message posted from http://www.ExcelForum.com/