Control Toolbox

You have to MATCH it against the combobox source data in then click event.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"John Data" <JohnData@discussions.microsoft.com> wrote in message
news:A593EFDA-CCDF-4C92-822F-62D338C93310@microsoft.com...
> If I link a cell in a combo box in the Forms module and look up the first
> item in a range, the linked cell fills in with number 1. If I select the
> secong item in the range, number 2 is filled in the linked cell and so on.
> How can I get the same result by using the combo box in the control
toolbox
> menu as it fills in the linked cell with the actual data from the lookup
> range rather than numbers 1, 2 etc?

Hi Bob
Sorry to be a pain

What exactly do you mean "MATCH it against the combobox source and click
event"?


"Bob Phillips" wrote:

> You have to MATCH it against the combobox source data in then click event.
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "John Data" <JohnData@discussions.microsoft.com> wrote in message
> news:A593EFDA-CCDF-4C92-822F-62D338C93310@microsoft.com...
> > If I link a cell in a combo box in the Forms module and look up the first
> > item in a range, the linked cell fills in with number 1. If I select the
> > secong item in the range, number 2 is filled in the linked cell and so on.
> > How can I get the same result by using the combo box in the control
> toolbox
> > menu as it fills in the linked cell with the actual data from the lookup
> > range rather than numbers 1, 2 etc?
>
>
>

I thought you might ask that <vbg>

Say the source data is in Sheet2, cells A2:A20. The combobox returns the
value, so you match against the source,

=MATCH(val,Sheet2!A2:A20,0)

in VBA

Application.MatchCombobox1.Value, Worksheets("Sheet2").Range("A2:A20"), 0)

This would be done within the combobox click event

--
HTH

Bob Phillips

"John Data" <JohnData@discussions.microsoft.com> wrote in message
news:25B75CE1-91DD-4787-B07E-665E502A903D@microsoft.com...
> Hi Bob
> Sorry to be a pain
>
> What exactly do you mean "MATCH it against the combobox source and click
> event"?
>
>
> "Bob Phillips" wrote:
>
> > You have to MATCH it against the combobox source data in then click
event.
> >
> > --
> >
> > HTH
> >
> > RP
> > (remove nothere from the email address if mailing direct)
> >
> >
> > "John Data" <JohnData@discussions.microsoft.com> wrote in message
> > news:A593EFDA-CCDF-4C92-822F-62D338C93310@microsoft.com...
> > > If I link a cell in a combo box in the Forms module and look up the
first
> > > item in a range, the linked cell fills in with number 1. If I select
the
> > > secong item in the range, number 2 is filled in the linked cell and so
on.
> > > How can I get the same result by using the combo box in the control
> > toolbox
> > > menu as it fills in the linked cell with the actual data from the
lookup
> > > range rather than numbers 1, 2 etc?
> >
> >
> >

If I link a cell in a combo box in the Forms module and look up the first
item in a range, the linked cell fills in with number 1. If I select the
secong item in the range, number 2 is filled in the linked cell and so on.
How can I get the same result by using the combo box in the control toolbox
menu as it fills in the linked cell with the actual data from the lookup
range rather than numbers 1, 2 etc?

You have to MATCH it against the combobox source data in then click event.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"John Data" <JohnData@discussions.microsoft.com> wrote in message
news:A593EFDA-CCDF-4C92-822F-62D338C93310@microsoft.com...
> If I link a cell in a combo box in the Forms module and look up the first
> item in a range, the linked cell fills in with number 1. If I select the
> secong item in the range, number 2 is filled in the linked cell and so on.
> How can I get the same result by using the combo box in the control
toolbox
> menu as it fills in the linked cell with the actual data from the lookup
> range rather than numbers 1, 2 etc?

Hi Bob
Sorry to be a pain

What exactly do you mean "MATCH it against the combobox source and click
event"?


"Bob Phillips" wrote:

> You have to MATCH it against the combobox source data in then click event.
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "John Data" <JohnData@discussions.microsoft.com> wrote in message
> news:A593EFDA-CCDF-4C92-822F-62D338C93310@microsoft.com...
> > If I link a cell in a combo box in the Forms module and look up the first
> > item in a range, the linked cell fills in with number 1. If I select the
> > secong item in the range, number 2 is filled in the linked cell and so on.
> > How can I get the same result by using the combo box in the control
> toolbox
> > menu as it fills in the linked cell with the actual data from the lookup
> > range rather than numbers 1, 2 etc?
>
>
>

I thought you might ask that <vbg>

Say the source data is in Sheet2, cells A2:A20. The combobox returns the
value, so you match against the source,

=MATCH(val,Sheet2!A2:A20,0)

in VBA

Application.MatchCombobox1.Value, Worksheets("Sheet2").Range("A2:A20"), 0)

This would be done within the combobox click event

--
HTH

Bob Phillips

"John Data" <JohnData@discussions.microsoft.com> wrote in message
news:25B75CE1-91DD-4787-B07E-665E502A903D@microsoft.com...
> Hi Bob
> Sorry to be a pain
>
> What exactly do you mean "MATCH it against the combobox source and click
> event"?
>
>
> "Bob Phillips" wrote:
>
> > You have to MATCH it against the combobox source data in then click
event.
> >
> > --
> >
> > HTH
> >
> > RP
> > (remove nothere from the email address if mailing direct)
> >
> >
> > "John Data" <JohnData@discussions.microsoft.com> wrote in message
> > news:A593EFDA-CCDF-4C92-822F-62D338C93310@microsoft.com...
> > > If I link a cell in a combo box in the Forms module and look up the
first
> > > item in a range, the linked cell fills in with number 1. If I select
the
> > > secong item in the range, number 2 is filled in the linked cell and so
on.
> > > How can I get the same result by using the combo box in the control
> > toolbox
> > > menu as it fills in the linked cell with the actual data from the
lookup
> > > range rather than numbers 1, 2 etc?
> >
> >
> >