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

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

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.

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.