Forms, Bound Columns etc

Hi All,

Hope this makes sense.

I have a combobox on a form (say called MyCBX) which when the form is
initialised is populated with a named range (say MyRange) consisting of 2
columns. The relevant properties of the combo box are:

BoundColumn 2
ColumnCount 2
Width 0pt;180pt

In the named range column 1 contains a dbCode and column 2 contains the
description.

So on initialising the form MyCBX.RowSource = "MyRange"
On entering data ActiveCell.Offset(1,0).Value =
MyCBX.Column(0)

This works fine and enters the code in a database style spreadsheet. The
problem arises when the same form is used to edit/amend a record that has
already been written.

So on initialising the form MyCBX.RowSource = "MyRange"
MyCBX.Value =
"ActiveCell.Offset(1,0).Value

It is this last line that causes the problem because it is retrieving the
dbCode written by the spreadsheet rather than the Description. The
description is not written to the database spreadsheet.

Is there a way that l can force the MyCBX.Value to the Description?

TIA

Regards

Michael Beckinsale

Hi All,

Just to let you know that the problem is sorted in case anybody is working
on it.

Solution was as follows:

this code placed in the form_initialize event

MyCBX.Value = Application.WorksheetFunction.VLookup(ActiveCell.Offset(0,
1).Value, Range("MyRange"), 2, False)

where "MyRange" is the named range used to populate the row source

Regards

Michael Beckinsale

"Michael Beckinsale" <michael.beckinsale@ntlworld.com> wrote in message
news:eH%234LgpTGHA.224@TK2MSFTNGP10.phx.gbl...
> Hi All,
>
> Hope this makes sense.
>
> I have a combobox on a form (say called MyCBX) which when the form is
> initialised is populated with a named range (say MyRange) consisting of 2
> columns. The relevant properties of the combo box are:
>
> BoundColumn 2
> ColumnCount 2
> Width 0pt;180pt
>
> In the named range column 1 contains a dbCode and column 2 contains the
> description.
>
> So on initialising the form MyCBX.RowSource = "MyRange"
> On entering data ActiveCell.Offset(1,0).Value =
> MyCBX.Column(0)
>
> This works fine and enters the code in a database style spreadsheet. The
> problem arises when the same form is used to edit/amend a record that has
> already been written.
>
> So on initialising the form MyCBX.RowSource = "MyRange"
> MyCBX.Value =
> "ActiveCell.Offset(1,0).Value
>
> It is this last line that causes the problem because it is retrieving the
> dbCode written by the spreadsheet rather than the Description. The
> description is not written to the database spreadsheet.
>
> Is there a way that l can force the MyCBX.Value to the Description?
>
> TIA
>
> Regards
>
> Michael Beckinsale
>