Linking Drop Down Lists

[kailynlyn]

Does anyone know how to link drop down lists? Is this possible? What I want to do: I have two identical worksheets....and I want to be able to make changes in worksheet 1 and have them appear on worksheet 2. I have already linked text cells, but it would be nice if when I make a choice on a drop down list in worksheet 1 that it will show up on the drop down list in worksheet 2.
CAn anyone help me? :wink:

 

[Zack Barresse]

Hi Kailynlyn, welcome to MrExcel boards!!


Can you describe for us what kind of drop down boxes you are dealing with here? Have you used Data Validation, or a control of sorts (either Forms of Controls)? Whichever you used, can you describe the location/name(s) of each?

 

[kailynlyn]

I used the contol tool box to create the drop down lists. More specifcally I used the combo box from the choices.

 

[Zack Barresse]

In your second sheet, right click the sheet tab and select View Code. Then paste this on the right ..

Private Sub Worksheet_Activate()
    Me.ComboBox1.Value = Sheets("Sheet1").ComboBox1.Value
End Sub

Change the ComboBox's to what they actually are for you.



HTH

 

[kailynlyn]

WOW...macro langauge is beyond me right now. I was wondering if there was an easier way...or if you could breifly explain the code.

 

[Zack Barresse]

Easier way? No. Not with the objects you are dealing with.

There are two parts to the code. Imagine a split line going right down the middle of the equal sign. The left half ..

Me.ComboBox1.Value

This is in the worksheet code, so the Me. portion is referring to the worksheet object which the code (module) resides in. It's saying that the ComboBox1 (value) is what were looking at. The second part is what will be used to set such value.

Sheets("Sheet1").ComboBox1.Value

Since were on a different sheet, we must specify the sheet object we want to look in, this is Sheet1. The ComboBox on sheet 1 is also called ComboBox1. We are taking the Value of this ComboBox and putting it as the Value of the ComboBox on sheet 2 (or the sheet with the code in it).

Note that this is all inside of a Worksheet_Activate code. That is the trigger event. When you activate the worksheet, the code will be compiled and run. That's it.

A good intro to the Visual Basica Editor (VBE) - a good place to start learning about Excel macro language (VBA) is here.


HTH

 

[kailynlyn]

Thanks a lot.....I will try this!!!