Cell drives combo box change

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
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
 
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.
 
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.
 
Back
Top