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 a

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

Forms, Bound Columns etc
 

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.O ffset(0,
1).Value, Range("MyRange"), 2, False)

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

Regards

Michael Beckinsale

"Michael Beckinsale" wrote in message
...
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 a

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