Help!!!!!!!!

[Steve]

I am trying to link combo boxes so depending on what i
pick in 1 list will detemine what list is presented in
the other.

I have been told to do the following.

Combo1 ; input range = $A$5:$A$6, cell link = $A$7. A5
= "List1", A6 =

"List2". Formula in A8 as follows -

Please help

=INDEX(A5:A6,A7,0)

Add a couple of named ranges somewhere, List1 and List2.

Add a Combo2 and assign this code to Combo1 -

Sub ChooseList()
ActiveSheet.Shapes("Combo2").Select
With Selection
.ListFillRange = Range("A8")
End With
SendKeys "{ESC}"
End Sub

So, I now have to lists (List1 & List2), have put in
text "List1" into A5 and text "List1" into A6.
I have changed the properties of Combo 1 to give me the
listfill of A5:A6 and linkcell of A7.

However, The Index command seems to give the #VALUE error.

Can anyone please help, i'm pulling my hair out.

Thanks

Steve

 

[Bob Phillips]

Steve,

I replicated this and whilst I find it overly messy and complex, it does
work.

The thing that may be giving a problem is that the combobox does not get a
shape name of Combo2 automatically, you will need to change it so.

I have a much simpler, fully VBA, version, that does not rely on named
ranges but allows the first combo to be populated by your values. If you
want, I will give you a copy of that ( I will need to know if you have XL97
or XL2000 as XL97 data validation changes do not trigger the worksheet
change event).

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Steve]

Bob

I would appreciate your help.

I am using Windows XP and Office XP if that helps.

Many Thanks

Steve