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]

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.

I wouldn't use Forms.

I use Excel2000 and you can code Comboboxes from the Control Toolbar, are you sure you can't for Excel97?

 

[Jonasmj]

I wouldn't use Forms.

I use Excel2000 and you can code Comboboxes from the Control Toolbar, are you sure you can't for Excel97?

I can code Comboboxes from the control toolbar. I meant the other way around What I meant is that I can ad code to a Combobox from the Control Toolbar but then I can't ad any ListFillRange. A ListFillRange is only possible with a Combobox from the forms toolbar.

 

[Jonasmj]

Is thera a way?

 

[jimboy]

I can code Comboboxes from the control toolbar. I meant the other way around What I meant is that I can ad code to a Combobox from the Control Toolbar but then I can't ad any ListFillRange. A ListFillRange is only possible with a Combobox from the forms toolbar.

You can use ListFillRange on a combobox from the control toolbar or by coding (see earlier post) - right mouse over the combobox (in Design mode - blue set square on toolbar) and select properties, enter range in ListFillRange or name your range and insert name.

 

[Jonasmj]

Sorry, I'm not shure what I have been doing
Anyhow, here's how the code should look for a combobox from the controller toolbar:

Private Sub ComboBox1_Click()
    ComboBox1.ListFillRange = "a1:a10"

Dim c As Range
For Each c In Range("A11:A500")
    If c.Value = ComboBox1.Value Then
    c.Select
    Exit Sub
    End If
    Next
End Sub

In the end very simple. Thank's for your help!

 

[Jonasmj]

How do I ad a function that jumps to the next value in the range?

Each value put in the combobox is found on more than one place in the specified range. If the user didn't want the first cell (as above code takes them to) how to I make it "look again" so to say? Jump to the next cell where this value occurs.

I want this to be done clicking on a button. I don't suppose it's very hard to accomplish this...

 

[jimboy]

Try this;

Private Sub ComboBox1_Click()
    ComboBox1.ListFillRange = "a1:a10"

Dim c As Range
For Each c In Range("A11:A500")
    If c.Value = ComboBox1.Value Then
    c.Select
    If MsgBox("Is this the value you are looking for?", vbYesNo) = vbNo Then GoTo myexit
    Exit Sub
    End If
myexit:
    Next
End Sub

 

[Jonasmj]

This isn't a good way... atleast not for me.
Beside the combobox I have a button. This button should work like "find next". The problem is that when this code is run it shouldn't take me to the very next cell with the same value but to a cell that is 12 rows below. (or more) Explanation: Every value choosed in the combobox occurs at least 12 times in the following rows (in the same column).
I wish to run through this column and look for the next cell where the choosen value is. So if I could identify the cell adress of the value just selected with the combobox I could ad 12 and look in that cell for the same value. If it isn't there I should run on another 12 rows. I guess som kind of DO - WHILE should work. I'm just not hacker enough to put it together.

some variables declared

varieble = activecell.adress
ad 12 to row-variable
look for value in that new cell adress
and so on....

anyone?

Thanks!

 

[jimboy]

This is not tested fully, but see what you think of this, if it can't find your value in the list below it will select A1 and start again;

Dim counter As Integer
counter = 0
    ComboBox1.ListFillRange = "a1:a10"

Dim c As Range
mystart:
For Each c In Range("A" & ActiveCell.Row + 1 & ":A500")
    If c.Value = ComboBox1.Value Then
    c.Select
    If MsgBox("Is this the value you are looking for?", vbYesNo) = vbNo Then GoTo myexit
    Exit Sub
    End If
myexit:
    Next
    If counter >= 1 Then Exit Sub
    Range("A1").Select
    counter = counter + 1
    GoTo mystart

 

[Jonasmj]

I will test it tomorrow but already now I see that a msgbox isn't what I would prefer. I would just have a loop to the next value when the button is clicked. Somewhat like pressing F3 (find next) when doing a search in a file.
I tried the findnext in vb but couldn't make it out...