Index number problem with Combo Box drop down

[bartk]

Hello members,


Basically, I have a number of Combo box drop down lists set up using 'Format Control' in Excel for Mac 2004. Some of these lists use the index value from a preceding list to determine the items to be shown. That all works fine in that the correct options are always displayed in each drop down list.

(Note: I did this initially using Data Validation Lists, but then discovered limitations with list lengths).


Problem #1

When an item selection in Drop Down 1 automatically changes the items in Drop Down 2, the index number linked to the item displayed in Drop Down 2 is not automatically refreshed. The desired item sometimes appears as the default in Drop Down 2 without the need to make a selection and sometimes luck has it that the corresponding index number already there is correct. However, if the incorrect index number is displayed it can only be refreshed by making a new selection in Drop Down 2.

How can one force the index number linked to Drop Down 2 to refresh simultaneously when a selection occurs in Drop Down 1? Also, if a subsequent selection is made in Drop Down 2, it will also need to refresh the index number.


Problem #2

What happens when Drop Down 2 only has one item? It seems that because no alternative selection can be made, then there is no way of refreshing the linked index number.


Problem #3

What to do when the same item is present in the different name list options for Drop Down 2?

-Please see Excel document: http://dl.dropbox.com/u/31831289/Combo%20Box%20Problem_Chandoor.xls

For example, "Tilt Top-to-Bottom" is item 2 in Name List "Both" and item 1 in Name List "TopToBot". This is problematic when I need to use INDEX to return the correct text (see red text).


I'd kindly appreciate assistance with the above.


Thank you.


Bart


Summary of Wooksheet:


Drop Down 1 has three items to select from:

Flip

Animation

3D


In Drop Down 2 the corresponding items are as follows:

Flip > Turn Left-to-Right

> Tilt Top-to-Bottom

Animation > Tilt Top-to-Bottom

3D > Turn Left-to-Right

 

[Luke M]

Sorry I don't have time to elaborate more, but here's the gist of my idea:

All drop downs need to have a blank as the first choice

Use a macro so that whenever a drop down changes, all subsequent boxes get reset to blank. Note that this will be easier if you are using ActiveX controls rather than Forms controls.

 

[NARAYANK991]

Hi Bart ,


If you are using Forms controls , you can assign a macro to the first drop down ; by default this will be DropDown1_Change. Put in the following code for this :

[pre]

Sub DropDown1_Change()
Range("C2").Value = 1
End Sub

[/pre]
You will have to insert a module , and put in this code.


Whenever the selection in the first drop down changes , the linked cell of the second drop down will be changed to 1 ; the second drop down will therefore always display the first item in the drop down list. If you wish to move to any other option , in case there are more than 1 , you can do so , otherwise the first and only option will remain selected.


Narayan

 

[Luke M]

@Narayan,


Well, I just learned something new! I did not know that you could assign a macro AND have it be a change event type macro. Very handy!

 

[bartk]

Thank you both Luke and Narayan for your responses.


Narayan, your solution is perfect and resolves all three problems -many thanks!