VBA newbie needs a little help
 

I have three comboboxes, the last two dependent, which appear when i double
click into a cell with a data validation list.

The code that I have shows

str = "=" & Target.Offset(0, -1).Value

for the listfillrange (listfillrange=str) for the two dependent lists. The
problem with this is that I named my two word ranges as one word, e.g.
NewYork, which came up fine in the data validation list which uses

=indirect(substitute(A1," ","")

However, the combo box does not pick up the listfillrange for the dependent
list if the one that it depends on has two words, because the name of the
list range is all one word.

Is there any way for me to tell it str needs to equal the value of the cell
to its left, as it is doing now, but to disregard spaces in that cell, as the
substitution formula was doing in my data validation list.

VBA newbie needs a little help
 

Not totally sure I get the question, but I will take a pot.

str = "=" & Replace(Target.Offset(0, -1).Value," ","")


--

HTH

RP
(remove nothere from the email address if mailing direct)


"BB" wrote in message
...
I have three comboboxes, the last two dependent, which appear when i
double
click into a cell with a data validation list.

The code that I have shows

str = "=" & Target.Offset(0, -1).Value

for the listfillrange (listfillrange=str) for the two dependent lists. The
problem with this is that I named my two word ranges as one word, e.g.
NewYork, which came up fine in the data validation list which uses

=indirect(substitute(A1," ","")

However, the combo box does not pick up the listfillrange for the
dependent
list if the one that it depends on has two words, because the name of the
list range is all one word.

Is there any way for me to tell it str needs to equal the value of the
cell
to its left, as it is doing now, but to disregard spaces in that cell, as
the
substitution formula was doing in my data validation list.

VBA newbie needs a little help
 

Bob-

you got the question and then some. Worked perfectly! Thanks for the speedy
reply.

"Bob Phillips" wrote:

Not totally sure I get the question, but I will take a pot.

str = "=" & Replace(Target.Offset(0, -1).Value," ","")


--

HTH

RP
(remove nothere from the email address if mailing direct)


"BB" wrote in message
...
I have three comboboxes, the last two dependent, which appear when i
double
click into a cell with a data validation list.

The code that I have shows

str = "=" & Target.Offset(0, -1).Value

for the listfillrange (listfillrange=str) for the two dependent lists. The
problem with this is that I named my two word ranges as one word, e.g.
NewYork, which came up fine in the data validation list which uses

=indirect(substitute(A1," ","")

However, the combo box does not pick up the listfillrange for the
dependent
list if the one that it depends on has two words, because the name of the
list range is all one word.

Is there any way for me to tell it str needs to equal the value of the
cell
to its left, as it is doing now, but to disregard spaces in that cell, as
the
substitution formula was doing in my data validation list.