autcompletion

Hi, Everybody!

I tried to combine the Dependent List feature from contextures.com
(http://www.contextures.com/xlDataVal02.html) with the ComboBox feature
(http://www.contextures.com/xlDataVal11.html), but I have relevant
dificulties making the code work.

The ideea is like this: the ComboBox works fine for the first column, it
shows the validation list that i was expecting, but in the second column,
that is dependent on the first one (validation list for second column is
defined as "= INDIRECT(M70)") nothing shows. I tried to debug the code
(watched the str variable) and it does not appear to evaluate the command, it
actualy contains exactly this: = INDIRECT(M70).
What troubles me is that i dont have at least this in the ComboBox list. It
is just blank.

I hope i expressed clearly what my problem is and i truly hope you can help
me!
Thank you very much in advance!

jad

the validation list for the second column should be a defined name that has a
refers to formula =Indirect(M70)

--
regards,
Tom Ogilvy




"jad" wrote:

> Hi, Everybody!
>
> I tried to combine the Dependent List feature from contextures.com
> (http://www.contextures.com/xlDataVal02.html) with the ComboBox feature
> (http://www.contextures.com/xlDataVal11.html), but I have relevant
> dificulties making the code work.
>
> The ideea is like this: the ComboBox works fine for the first column, it
> shows the validation list that i was expecting, but in the second column,
> that is dependent on the first one (validation list for second column is
> defined as "= INDIRECT(M70)") nothing shows. I tried to debug the code
> (watched the str variable) and it does not appear to evaluate the command, it
> actualy contains exactly this: = INDIRECT(M70).
> What troubles me is that i dont have at least this in the ComboBox list. It
> is just blank.
>
> I hope i expressed clearly what my problem is and i truly hope you can help
> me!
> Thank you very much in advance!
>
> jad
>

"Tom Ogilvy" wrote:

> the validation list for the second column should be a defined name that has a
> refers to formula =Indirect(M70)
>
> --
> regards,
> Tom Ogilvy
>
i know and that is what i expect it to be.. so if for example i choose lets
say "cdc" in first column .. i expect the validation list for column N70 to
be = INDIRECT(M70), which should evaluate to = cdc, where cdc is an already
defined list.. so i woul expect to see on my current cell a ComboBox that
would allow me to choose from the values in the cdc list... but it is not
like that... the ComboBox list is empty...

I have already told you the answer, but you don't seem to be paying attention.

=Indirect(M70)

as the source for the list or as the ListfillRange isn't going to evaluate
to =cdc

If I put cdc in M70, then

Insert Name define
Name: List5
Refersto: =Indirect($M$70)

I did that, then I put a combobox from the control toolbox toolbar and set
the ListFillRange to List5 and it worked fine for me.

or if I put a data=>Validation with list option in a cell and in the list
option specifiy
=List5

Again, it works fine.

--
Regards,
Tom Ogilvy

"jad" wrote:

>
>
> "Tom Ogilvy" wrote:
>
> > the validation list for the second column should be a defined name that has a
> > refers to formula =Indirect(M70)
> >
> > --
> > regards,
> > Tom Ogilvy
> >
> i know and that is what i expect it to be.. so if for example i choose lets
> say "cdc" in first column .. i expect the validation list for column N70 to
> be = INDIRECT(M70), which should evaluate to = cdc, where cdc is an already
> defined list.. so i woul expect to see on my current cell a ComboBox that
> would allow me to choose from the values in the cdc list... but it is not
> like that... the ComboBox list is empty...

"Tom Ogilvy" wrote:

> I have already told you the answer, but you don't seem to be paying attention.

hmm. i already replyed to this post, but nothing appeared, so i will do it
again. you are right, Mr. Ogilvy, i read your inital post too fast and didn't
process it right, so thank you very much for detailing it in your second
post.

now the problem is that this doesn't realy cover my need. it is like this: i
want cell N70 to refer to M70, but i want cell N71 to refer to M71. and i
can't do lists that refer to INIDIRECT(Mxx) for each row. is there a way that
this autoincrementes?

If you are doing this with the "virtual combobox" the Debra has at her site,
but just want the dropdown contents to be dependent on the value in the
adjacent column, then you will be using code to manage the appearance of the
combobox. In that code just assign the appropriate range (Modified code
from Debra's site, 2nd reference)

' this line changed
str = Range(Target.offset(0,-1).value).Address(1,1,xla1,true)
' line deleted
With cboTemp
'show the combobox with the list
.Visible = True
.Left = Target.Left
.Top = Target.Top
.Width = Target.Width + 5
.Height = Target.Height + 5
.ListFillRange = str
.LinkedCell = Target.Address
End With

--
Regards,
Tom Ogilvy


"jad" wrote:

>
>
> "Tom Ogilvy" wrote:
>
> > I have already told you the answer, but you don't seem to be paying attention.
>
> hmm. i already replyed to this post, but nothing appeared, so i will do it
> again. you are right, Mr. Ogilvy, i read your inital post too fast and didn't
> process it right, so thank you very much for detailing it in your second
> post.
>
> now the problem is that this doesn't realy cover my need. it is like this: i
> want cell N70 to refer to M70, but i want cell N71 to refer to M71. and i
> can't do lists that refer to INIDIRECT(Mxx) for each row. is there a way that
> this autoincrementes?

If you are doing this with the "virtual combobox" the Debra has at her site,
but just want the dropdown contents to be dependent on the value in the
adjacent column, then you will be using code to manage the appearance of the
combobox. In that code just assign the appropriate range (Modified code
from Debra's site, 2nd reference)

' this line changed
str = Range(Target.offset(0,-1).value).Address(1,1,xla1,true)
' line deleted
With cboTemp
'show the combobox with the list
.Visible = True
.Left = Target.Left
.Top = Target.Top
.Width = Target.Width + 5
.Height = Target.Height + 5
.ListFillRange = str
.LinkedCell = Target.Address
End With

--
Regards,
Tom Ogilvy


"jad" wrote:

>
>
> "Tom Ogilvy" wrote:
>
> > I have already told you the answer, but you don't seem to be paying attention.
>
> hmm. i already replyed to this post, but nothing appeared, so i will do it
> again. you are right, Mr. Ogilvy, i read your inital post too fast and didn't
> process it right, so thank you very much for detailing it in your second
> post.
>
> now the problem is that this doesn't realy cover my need. it is like this: i
> want cell N70 to refer to M70, but i want cell N71 to refer to M71. and i
> can't do lists that refer to INIDIRECT(Mxx) for each row. is there a way that
> this autoincrementes?