Linking Drop Down Lists

kailynlyn

New Member
Joined
Mar 2, 2005
Messages
4
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:
 

Some videos you may like

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

Zack Barresse

MrExcel MVP
Joined
Dec 9, 2003
Messages
10,881
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
  3. Web
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

New Member
Joined
Mar 2, 2005
Messages
4
I used the contol tool box to create the drop down lists. More specifcally I used the combo box from the choices.
 

Zack Barresse

MrExcel MVP
Joined
Dec 9, 2003
Messages
10,881
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
  3. Web
In your second sheet, right click the sheet tab and select View Code. Then paste this on the right ..

Code:
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

New Member
Joined
Mar 2, 2005
Messages
4

ADVERTISEMENT

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

MrExcel MVP
Joined
Dec 9, 2003
Messages
10,881
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
  3. Web
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 ..
Code:
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.

Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,156
Messages
5,600,040
Members
414,357
Latest member
Gemma_R

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top