Excel VBA programmatic validation problem

Z

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
 
J

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?
|
|
| ---
|
|
 
Z

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
 
C

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
 
Z

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
Click to expand...

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
 
Z

ZoomZoom

I've attached a zipped up working example that uses Chip's code i
the
Worksheet activate event.
Click to expand...
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
 
E

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
 
Z

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.
Click to expand...

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
 
E

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
 
Z

ZoomZoom

If that works, then step
through it with the macro recorder and go from there.
Click to expand...

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
 
Top