multiple cell links

[Ed3m]

Is this possible and if so how.I would like to link a combo box to different cells on different sheets.

 

[Greg Truby]

Assuming this is for a ComboBox that is located on a Worksheet not a UserForm (though adapting it for a UserForm would not be too difficult). This works for me in testing:

<font face=Courier New>
<SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> ComboBox1_GotFocus()
    <SPAN style="color:#00007F">Dim</SPAN> rngCell <SPAN style="color:#00007F">As</SPAN> Range
    <SPAN style="color:#00007F">For</SPAN> i = Me.ComboBox1.ListCount - 1 <SPAN style="color:#00007F">To</SPAN> 0 <SPAN style="color:#00007F">Step</SPAN> -1
        Me.ComboBox1.RemoveItem i
    <SPAN style="color:#00007F">Next</SPAN> i
        
    <SPAN style="color:#007F00">' [RangeOne] = Cells A1:A5 on Sheet4</SPAN>
    <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> rngCell <SPAN style="color:#00007F">In</SPAN> [RangeOne].Cells
        Me.ComboBox1.AddItem rngCell.Value
    <SPAN style="color:#00007F">Next</SPAN> rngCell
    
    <SPAN style="color:#007F00">' [RangeTwo] = Cells F5:F8 on Sheet1</SPAN>
    <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> rngCell <SPAN style="color:#00007F">In</SPAN> [RangeTwo].Cells
        Me.ComboBox1.AddItem rngCell.Value
    <SPAN style="color:#00007F">Next</SPAN> rngCell

<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>

</FONT>

HTH

 

[viper]

Just use:

Combobox1.additem Sheet2.cells(1,1). Value
Combobox1.additem sheet3.cells(5,7).Value
Combobox1.additem Sheet8.cells(20,8).Value

HTH

 

[Ed3m]

i tried vipers suggestion however all i get is runtime error 424

 

[viper]

Sorry didn't specify where to put the code.

Put the code into your workbook_open event so the code will look like this:

'change the sheet1 to your sheet name that contains the combobox
'change the combobox1 to your combobox name if different
'change Sheet2 & 3 name to yours
'for reference Cells(7, 5) is equal to Cell E7 on Sheet2

Private Sub Workbook_Open()
    Sheet1.ComboBox1.AddItem Sheet2.Cells(7, 5).Value
    Sheet1.ComboBox1.AddItem Sheet3.Cells(6, 5).Value
    
End Sub