Cell drives combo box change

[Jeremy]

I'm trying to get a combo box to change in reference to a cell. For example:

Say Sheet 1, B6 has a ID value of 1. I want the macro to read that there is
a 1 there, then go to sheet 2 where the combo box list is located (K6 is the
text, L6 is the ID value 1 associated with it), then have the combo box on
sheet 3 change to the selection.

Any ideas? I've tried some various things and am not getting very far.

Thanks.

 

[Tom Ogilvy]

Dim cell as Range, rng as Range, res as Variant
set cell = Worksheets("Sheet1").Range("B6")
With worksheets("Sheet2")
set rng = .Range("L1",.Range("L1").End(xldown))
End with
res = application.Match(cell,rng,0)
if not iserror(res) then
Combobox1.Value = rng(res).offset(0,-1).Value
End if

 

[Jeremy]

hmm, I keep getting an error when I get to the line:

Combobox1.Value = rng(res).offset(0,-1).Value


I put the name of the combo box in place of the "combobox1" in the line.

 

[Tom Ogilvy]

does combobox1 need to be qualified?

Is rng at least in column 2.

Demo'd from the immediate window:

set rng = Range("B1:B10")
Range("B5") = "AA"
res = Application.Match("AA",rng,0)
? rng(res).offset(0,-1).Address
$A$5

so the concept is proven.

Must be an implementation error.