Jump to cell when choosing a value in a combo box

Jonasmj

New Member
Joined
Sep 23, 2002
Messages
38
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
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

Noir

Active Member
Joined
Mar 24, 2002
Messages
362
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

New Member
Joined
Sep 23, 2002
Messages
38
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

New Member
Joined
Sep 23, 2002
Messages
38

ADVERTISEMENT

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

Well-known Member
Joined
Apr 11, 2002
Messages
2,314
Right mouse over the combobox (in Design Mode) and select view code, then paste this in (change the range)

Code:
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

New Member
Joined
Sep 23, 2002
Messages
38

ADVERTISEMENT

my bad
 

Jonasmj

New Member
Joined
Sep 23, 2002
Messages
38
This code works. In a user form.

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

Code:
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

Code:
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

Well-known Member
Joined
Apr 11, 2002
Messages
2,314
Jonasmj said:
Code:
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

Code:
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

New Member
Joined
Sep 23, 2002
Messages
38
jimboy said:
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,553
Messages
5,636,989
Members
416,953
Latest member
broexc

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top