ComboBox

[Ben S]

I have set up a comboBox which allows me to choose 1 of 5 options using the drop down menu.

I would like to use a command button to automatically take me to the relevant section of the worksheet.

E.g if Ao1 was chosen within the comboBox when i click on the command button it will take me directly to range Ao1 within the worksheet.

 

[Andrew Poulsom]

Why don't you do it straight from your ComboBox using the Click or Change event procedure?

 

[Ben S]

How can i set up an event procedure on a comboBox

 

[Andrew Poulsom]

Is it from the Control ToolBox or the Forms Toolbar?

 

[Ben S]

Its from the control toolbox

 

[Andrew Poulsom]

Good.

What are the items in your ComboBox? Are they cell addresses or named ranges?

 

[Ben S]

There are named ranges

Call them cost1, Cost2, Cost3, cost4 & Cost5

 

[Andrew Poulsom]

Activate the Control ToolBox and click the Design icon. Right click your ComboBox and choose View Code. Paste this into the window on the right:

Private Sub ComboBox1_Change()
    Application.Goto Reference:=ComboBox1.Text
End Sub

Note you should already see the first and last lines, so just add the middle one. The name (ComboBox1) may be different

Press Alt+F11 to return to your worksheet and click the Design icon again.

Now select something from your ComboBox.