Cell drives combo box change
 

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.

Cell drives combo box change
 

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

--
Regards,
Tom Ogilvy


"Jeremy" wrote:

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.

Cell drives combo box change
 

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" wrote:

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

--
Regards,
Tom Ogilvy


"Jeremy" wrote:

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.

Cell drives combo box change
 

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.

--
Regards,
Tom Ogilvy


"Jeremy" wrote:

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" wrote:

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

--
Regards,
Tom Ogilvy


"Jeremy" wrote:

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.