Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Combo box problem | Excel Discussion (Misc queries) | |||
combo box problem! | Excel Programming | |||
Combo Box problem | Excel Discussion (Misc queries) | |||
Combo box problem | Excel Programming | |||
combo box problem | Excel Programming |