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
>