Combo Box Problems

  • Thread starter Thread starter daphoenix
  • Start date Start date
D

daphoenix

I would like to make a combo box in my worksheet do exactly what the Name Box
does. When a item in the combo box is selected scroll right or left through
the worksheet and find that heading that was selected in the combo
box?.....Is there a way to do this? Thanks.
 
I would like to make a combo box in my worksheet do exactly what the NameBox
does. When a item in the combo box is selected scroll right or left through
the worksheet and find that heading that was selected in the combo
box?.....Is there a way to do this? Thanks.
Click to expand...

Use the Name Box itself, then? Create Named Ranges with the same name
as the headings.

Otherwise, you can write macro code which scrolls across the row,
comparing the combo box value to the value in the cell, stopping at a
match. Is the combo box in a Form, or is it in a cell as a Data
Validation? That'll determine whether you write the code as a
callback to the combobox.change event or as triggered by the change in
the value of a cell on the worksheet.
 
How would I go about doing your first option scrolling acroos the row and
stopping at a match?
 
The first option is to create a named range. Highlight each heading
and go to the Insert menu and choose Name... Define. In the Define
Name dialog box, everything should be filled in the way you want it by
default, so just press OK and you'll have a named range. Now you can
use the Name Box to quickly go to each heading.
 
sorry if i were misunderstanding, but the option of using the combo box and
the macro to look for values? how would i start to do that? thanks
 
Create a macro in the specific sheet (Alt+F11, double-click the
specific Sheet under Microsoft Excel Objects). Paste the following
code:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Not Application.Intersect(Target, Range("A1")) Is Nothing Then
Range("A3").Select
While (Selection() <> Range("A1"))
ActiveCell.Offset(0, 1).Select
Wend
End If
End Sub

Replace A1 with the address of the cell that contains your combobox
and replace A3 with the address of the first cell in the row that you
want to search through
 
if your combobox is a form item, you can right-click it and assign a
macro - just create a new one and use the code from the body of the
function I sent you:

If Not Application.Intersect(Target, Range("A1")) Is Nothing Then
Range("A3").Select
While (Selection() <> Range("A1"))
ActiveCell.Offset(0, 1).Select
Wend
End If
 
Back
Top