Combo box problem
 

Hi JohnathanW -

You could accomplish your goal with extra columns and formulas, but you are
correct in assuming that VBA is the better approach; it provides a simpler
solution in this case.

1. Open the Control Toolbox Toolbar and add a combobox to the worksheet.

2. Right-click on the combobox, choose |Properties|, and find the
ListFillRange property. Type A1:A5 for the property (or whatever the range
is that contains your paper sheet sizes).

3. Close the Properties window, right-click on the combobox, and choose
|View Code|. This throws you into the VB Editor. Copy and paste the
following code into the main window:

Private Sub ComboBox1_Change()
refValue = ComboBox1.Value
Range("A6") = Range("A1:A5").Find(refValue, _
LookIn:=xlValues, lookat:=xlWhole).Offset(0, 1)
Range("A7") = Range("A1:A5").Find(refValue, _
LookIn:=xlValues, lookat:=xlWhole).Offset(0, 2)
End Sub

4. Adjust the reference to "A1:A5" as necessary for your application. For
example, if your list is 400 rows long with paper sizes in column D, make the
reference "D1:D400".

5. Switch back to Excel (Alt-F11) and turn off DesignMode (from the Control
Toolbox). You're done. Test it out.

--
Jay


"JonathanW" wrote:

Hi,
I posted this question yesterday but I'm still having difficulty. I would
like a combo box, to display the options from column A and then return the
value from coloumn B into cell A6 and value from C into A7. I don't want to
use validation because I want those cells to not have any formula to allow
for 'custom sizes' which isn't in the combo box.
A B c
A4 297 210
A3 420 297

I think I have to use VBA but have no VBA knowledge so your help would be
much appreaciated.