Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
What does "Workbook has no bound sheets" mean? | Excel Discussion (Misc queries) | |||
Data bound VBA forms | Excel Programming | |||
bound columns - listbox question | Excel Programming | |||
Listbox with Bound Columns | Excel Programming | |||
Multi-column ListBox. Multiple bound columns??? | Excel Programming |