Two (2) Combobox Linked Together

[NelsonX]

Hi, I've created two comboboxes (from the control toolbar) and linked them to the same named range (list) as well as the same named range (LinkedCell). The comboboxes are on different sheets.

I've noticed when I use one of the comboboxes the linked cell changes value (as expected), but the other combobox is not updated to this view.

Is there a way to make sure both comboboxes reflect the same value when one is changed?

thanks

Nelson

 

[MickG]

Hi In sheet(1) Combobox you could have the code:-

Private [COLOR="Navy"]Sub[/COLOR] ComboBox1_Change()
[COLOR="Navy"]With[/COLOR] Sheets("sheet2").ComboBox1
.value = ComboBox1.List(ComboBox1.ListIndex)
[COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

and in sheet (2)

[COLOR="Navy"]Sub[/COLOR] MG05Oct09
Private [COLOR="Navy"]Sub[/COLOR] ComboBox1_Change()
[COLOR="Navy"]With[/COLOR] Sheets("sheet1").ComboBox1
.value = ComboBox1.List(ComboBox1.ListIndex)
[COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[NelsonX]

Hi Mick, I can't get that code to work. I have excel 2003 SP3.

So in sheet 1 the combobox should refer to the sheet2 listindex and vice versa right?

thanks

Nelson

 

[MickG]

Hi, You need to remove the "Sub bit" at the beginning of the second bit of code. (My mistake,Added in error).
Both codes should go in the combobox Change Events.
Double click the combobox in "Design mode" this will automaically open the VB Editor in the Combobox change event, so you'll have remove the duplicate "Private Sub ComboBox1_Change()"
bit from the code.
Regards Mick