creating validation list in VBA
 

Hi All,

When I create a validation list manually, I get a message saying "Evaluating
source cause an error! Do you want to continue?". If I reply Yes, XL2003
creates the data validation list, and I can choose new values from the
validation source.
If I want to create the same validation list in VBA, I get a run-time error
message. On Error Resume Next doesn't help, it skips the .Add
Type:=xlValidateList, ... etc. line, but doesn't create the validation.
How can I tell VBA to behave itself like XL user interface?

Thanks,
Stefi

creating validation list in VBA
 

I can't be sure as I don't have 2003 but try adding

Application.DisplayAlerts = False

before the code and reset to True after.

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"Stefi" wrote in message
...
Hi All,

When I create a validation list manually, I get a message saying
"Evaluating
source cause an error! Do you want to continue?". If I reply Yes, XL2003
creates the data validation list, and I can choose new values from the
validation source.
If I want to create the same validation list in VBA, I get a run-time
error
message. On Error Resume Next doesn't help, it skips the .Add
Type:=xlValidateList, ... etc. line, but doesn't create the validation.
How can I tell VBA to behave itself like XL user interface?

Thanks,
Stefi

creating validation list in VBA
 

Thanks for your reply Bob, but it didn't help! But I made some tests and
found the following strange behaviour:

My source list is defined by an OFFSET function that contains a MATCH
function using the reference of the adjacent cell. When this function can be
evaluated for the FIRST cell of the range I want to apply the validation to,
VBA creates the validation for the WHOLE range, even if the function returns
an Error for the remaining cells. Otherwise it stops with a 1004 Run-time
error message.

I made a workaround by adding an extra first row with values ensuring
correct evaluation of the OFFSET and MATCH functions though I am not sure
wether this is the best solution!

Regards,
Stefi

€žBob Phillips€� ezt Ã*rta:

I can't be sure as I don't have 2003 but try adding

Application.DisplayAlerts = False

before the code and reset to True after.

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"Stefi" wrote in message
...
Hi All,

When I create a validation list manually, I get a message saying
"Evaluating
source cause an error! Do you want to continue?". If I reply Yes, XL2003
creates the data validation list, and I can choose new values from the
validation source.
If I want to create the same validation list in VBA, I get a run-time
error
message. On Error Resume Next doesn't help, it skips the .Add
Type:=xlValidateList, ... etc. line, but doesn't create the validation.
How can I tell VBA to behave itself like XL user interface?

Thanks,
Stefi

creating validation list in VBA
 

Stefi,

what is the formula, I would like to try and replicate?

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"Stefi" wrote in message
...
Thanks for your reply Bob, but it didn't help! But I made some tests and
found the following strange behaviour:

My source list is defined by an OFFSET function that contains a MATCH
function using the reference of the adjacent cell. When this function can
be
evaluated for the FIRST cell of the range I want to apply the validation
to,
VBA creates the validation for the WHOLE range, even if the function
returns
an Error for the remaining cells. Otherwise it stops with a 1004 Run-time
error message.

I made a workaround by adding an extra first row with values ensuring
correct evaluation of the OFFSET and MATCH functions though I am not sure
wether this is the best solution!

Regards,
Stefi

"Bob Phillips" ezt írta:

I can't be sure as I don't have 2003 but try adding

Application.DisplayAlerts = False

before the code and reset to True after.

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"Stefi" wrote in message
...
Hi All,

When I create a validation list manually, I get a message saying
"Evaluating
source cause an error! Do you want to continue?". If I reply Yes,
XL2003
creates the data validation list, and I can choose new values from the
validation source.
If I want to create the same validation list in VBA, I get a run-time
error
message. On Error Resume Next doesn't help, it skips the .Add
Type:=xlValidateList, ... etc. line, but doesn't create the
validation.
How can I tell VBA to behave itself like XL user interface?

Thanks,
Stefi

creating validation list in VBA
 

..Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:= _

"=OFFSET(OMSZ,1,MATCH(D2,OFFSET(OMSZ,0,0,1,COLUMNS (OMSZ)),0)-1,COUNTA(OFFSET(OMSZ,1,MATCH(D2,OFFSET(OMSZ,0,0,1, COLUMNS(OMSZ)),0)-1,ROWS(OMSZ),1)),1)"

where OMSZ is a range name referring to a sheet containing county names in
the first row and settlements names located in that county under them. The
basic idea is to group settlements into counties then enter a county name in
column D and choose a settlement (column E) from the list of settlements
located in that county.

In fact County names are also chosen from a validation list rather than
typed in:

..Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=OFFSET(OMSZ,0,0,1,COLUMNS(OMSZ))"

I'm looking forward to the result of your testing!
Regards,
Stefi

€žBob Phillips€� ezt Ã*rta:

Stefi,

what is the formula, I would like to try and replicate?

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"Stefi" wrote in message
...
Thanks for your reply Bob, but it didn't help! But I made some tests and
found the following strange behaviour:

My source list is defined by an OFFSET function that contains a MATCH
function using the reference of the adjacent cell. When this function can
be
evaluated for the FIRST cell of the range I want to apply the validation
to,
VBA creates the validation for the WHOLE range, even if the function
returns
an Error for the remaining cells. Otherwise it stops with a 1004 Run-time
error message.

I made a workaround by adding an extra first row with values ensuring
correct evaluation of the OFFSET and MATCH functions though I am not sure
wether this is the best solution!

Regards,
Stefi

"Bob Phillips" ezt Ã*rta:

I can't be sure as I don't have 2003 but try adding

Application.DisplayAlerts = False

before the code and reset to True after.

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"Stefi" wrote in message
...
Hi All,

When I create a validation list manually, I get a message saying
"Evaluating
source cause an error! Do you want to continue?". If I reply Yes,
XL2003
creates the data validation list, and I can choose new values from the
validation source.
If I want to create the same validation list in VBA, I get a run-time
error
message. On Error Resume Next doesn't help, it skips the .Add
Type:=xlValidateList, ... etc. line, but doesn't create the
validation.
How can I tell VBA to behave itself like XL user interface?

Thanks,
Stefi

creating validation list in VBA
 

Stefi,

I have tested it on XL2000 and 2002 and it fails. Even if I wrap in On Error
Resume Next, although it runs, no list gets created.

I will play some more over the weekend.

There is something odd about the formula, so I wonder if I am reading it
correctly. Can you send me a workbook, with the failing formula and one with
the workaround that I can play with?


--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"Stefi" wrote in message
...
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:= _


"=OFFSET(OMSZ,1,MATCH(D2,OFFSET(OMSZ,0,0,1,COLUMNS (OMSZ)),0)-1,COUNTA(OFFSET
(OMSZ,1,MATCH(D2,OFFSET(OMSZ,0,0,1,COLUMNS(OMSZ)), 0)-1,ROWS(OMSZ),1)),1)"

where OMSZ is a range name referring to a sheet containing county names in
the first row and settlements names located in that county under them. The
basic idea is to group settlements into counties then enter a county name
in
column D and choose a settlement (column E) from the list of settlements
located in that county.

In fact County names are also chosen from a validation list rather than
typed in:

.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=OFFSET(OMSZ,0,0,1,COLUMNS(OMSZ))"

I'm looking forward to the result of your testing!
Regards,
Stefi

"Bob Phillips" ezt írta:

Stefi,

what is the formula, I would like to try and replicate?

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"Stefi" wrote in message
...
Thanks for your reply Bob, but it didn't help! But I made some tests
and
found the following strange behaviour:

My source list is defined by an OFFSET function that contains a MATCH
function using the reference of the adjacent cell. When this function
can
be
evaluated for the FIRST cell of the range I want to apply the
validation
to,
VBA creates the validation for the WHOLE range, even if the function
returns
an Error for the remaining cells. Otherwise it stops with a 1004
Run-time
error message.

I made a workaround by adding an extra first row with values ensuring
correct evaluation of the OFFSET and MATCH functions though I am not
sure
wether this is the best solution!

Regards,
Stefi

"Bob Phillips" ezt írta:

I can't be sure as I don't have 2003 but try adding

Application.DisplayAlerts = False

before the code and reset to True after.

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"Stefi" wrote in message
...
Hi All,

When I create a validation list manually, I get a message saying
"Evaluating
source cause an error! Do you want to continue?". If I reply Yes,
XL2003
creates the data validation list, and I can choose new values from
the
validation source.
If I want to create the same validation list in VBA, I get a
run-time
error
message. On Error Resume Next doesn't help, it skips the .Add
Type:=xlValidateList, ... etc. line, but doesn't create the
validation.
How can I tell VBA to behave itself like XL user interface?

Thanks,
Stefi

creating validation list in VBA
 

Hi Bob,

I tried to send you an e-mail this morning to the address (-nothere) that is
given in your profile but it couldn't be
delivered. Please give me the address you want to receive the e-mail at!

Regards,
Stefi


€žBob Phillips€� ezt Ã*rta:

Stefi,

I have tested it on XL2000 and 2002 and it fails. Even if I wrap in On Error
Resume Next, although it runs, no list gets created.

I will play some more over the weekend.

There is something odd about the formula, so I wonder if I am reading it
correctly. Can you send me a workbook, with the failing formula and one with
the workaround that I can play with?


--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"Stefi" wrote in message
...
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:= _


"=OFFSET(OMSZ,1,MATCH(D2,OFFSET(OMSZ,0,0,1,COLUMNS (OMSZ)),0)-1,COUNTA(OFFSET
(OMSZ,1,MATCH(D2,OFFSET(OMSZ,0,0,1,COLUMNS(OMSZ)), 0)-1,ROWS(OMSZ),1)),1)"

where OMSZ is a range name referring to a sheet containing county names in
the first row and settlements names located in that county under them. The
basic idea is to group settlements into counties then enter a county name
in
column D and choose a settlement (column E) from the list of settlements
located in that county.

In fact County names are also chosen from a validation list rather than
typed in:

.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=OFFSET(OMSZ,0,0,1,COLUMNS(OMSZ))"

I'm looking forward to the result of your testing!
Regards,
Stefi

"Bob Phillips" ezt Ã*rta:

Stefi,

what is the formula, I would like to try and replicate?

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"Stefi" wrote in message
...
Thanks for your reply Bob, but it didn't help! But I made some tests
and
found the following strange behaviour:

My source list is defined by an OFFSET function that contains a MATCH
function using the reference of the adjacent cell. When this function
can
be
evaluated for the FIRST cell of the range I want to apply the
validation
to,
VBA creates the validation for the WHOLE range, even if the function
returns
an Error for the remaining cells. Otherwise it stops with a 1004
Run-time
error message.

I made a workaround by adding an extra first row with values ensuring
correct evaluation of the OFFSET and MATCH functions though I am not
sure
wether this is the best solution!

Regards,
Stefi

"Bob Phillips" ezt Ã*rta:

I can't be sure as I don't have 2003 but try adding

Application.DisplayAlerts = False

before the code and reset to True after.

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"Stefi" wrote in message
...
Hi All,

When I create a validation list manually, I get a message saying
"Evaluating
source cause an error! Do you want to continue?". If I reply Yes,
XL2003
creates the data validation list, and I can choose new values from
the
validation source.
If I want to create the same validation list in VBA, I get a
run-time
error
message. On Error Resume Next doesn't help, it skips the .Add
Type:=xlValidateList, ... etc. line, but doesn't create the
validation.
How can I tell VBA to behave itself like XL user interface?

Thanks,
Stefi