Jump to cell when choosing a value in a combo box

[Jonasmj]

I have created a combo box that has the choice of several different values.
I made a small list with those values located on the same sheet as the combo box. When I choose a value I want to be taken to the cell in a specified column where this value is presented for the first time. Ie a kind of search function based on what the user chooses in the combo box.

I know I can use
Cells.Find(What:="value" but I don't know how I find the argument "value" from the selected value in the combo box.
What code is used?

Regards Joans

 

[jimboy]

Try

Combobox1.value

 

[Noir]

Jonasmj,
From your post i'm not sure if you have your combobox in a userform or on the sheet but, here is code that will work if the combobox is located on a userform.

First, create your userform and add a combobox. Then install code in your userform that will populate your combobox with your sheet data. Something like;

Private Sub UserForm_Activate()
ComboBox1.List = Range("MyList").Value
End Sub

(if you happen to have the data in a named range)

Then, install the following code into your userform;

Private Sub ComboBox1_Change()
Select Case ComboBox1.Text
Case "A"
Range("A5").Select
Case "B"
Range("A9").Select
Case Else
Range("A1").Select
End Select
End Sub

If the letter "A" is selected from your combobox, the cursor will automatically jump to cell A5, if "B" selected, A9 . Add more "Range" and "Case" lines as needed. Don't forget to add commandbuttons to your sheet and userform to call and dismiss the userform.

HTH
Noir

 

[Jonasmj]

Hi

The combo box is placed directly in the sheet and that is how I would prefer it to be.

How do I determine the name of the combobox?

 

[Jonasmj]

I tried the userform idéa and it works...
Still I want the combo box only directly in the sheet, no form.
If i create a combo box using the forms toolbox rahter than with the controller toolbox I can't get it to work.

Can't I declare a variable that is set by the user when he chooses a value in the combo box? Then taking that variable and by using the find command locate the cell containg that value? If so, how do I do it?

 

[jimboy]

Right mouse over the combobox (in Design Mode) and select view code, then paste this in (change the range)

Private Sub ComboBox1_Click()
Dim c As Range
For Each c In Range("A10:A20")
If c.Value = ComboBox1.Value Then
c.Select
Exit Sub
End If
Next
End Sub

 

[Jonasmj]

my bad

 

[Jonasmj]

This code works. In a user form.

Private Sub ComboBox1_Change()
ComboBox1.List = Range("a1:a10").Value
End Sub

Private Sub UserForm_Initialize()
With ComboBox1
    For Each cel In Range("A1:a10").Cells (list of values to find)
        .AddItem cel
    Next cel
End With
End Sub

Private Sub ComboBox1_Click()
Dim c As Range
For Each c In Range("A11:A500") (this is where the values are)
If c.Value = ComboBox1.Value Then
c.Select
Exit Sub
End If
Next
End Sub

Can I get this to work directly on a sheet?

 

[jimboy]

Private Sub UserForm_Initialize()
With ComboBox1
    For Each cel In Range("A1:a10").Cells (list of values to find)
        .AddItem cel
    Next cel
End With
End Sub

Private Sub ComboBox1_Click()
Dim c As Range
For Each c In Range("A11:A500") '(this is where the values are)
If c.Value = ComboBox1.Value Then
c.Select
Exit Sub
End If
Next
End Sub

Can I get this to work directly on a sheet?

You don't need the first part, right mouse over the combobox and select Properties, then in ListFillRange enter A11:A500

 

[Jonasmj]

You don't need the first part, right mouse over the combobox and select Properties, then in ListFillRange enter A11:A500

I have no such properties on a combo box from the forms toolbox. Only from controllers. Using the combo box from controllers gives no option of adding code.
I use Excel 97.