Forum Discussion
Go To a cell with a specific value using a combo or list box.
Hi there,
Can anyone tell me how to go to a cell containing the value entered in a combo box, please?
Thank you very much.
Tom
Not sure if understood your requirement, but if you wish to select a cell of which the reference is selected in the ComboBox, the following simple VBA code does the trick:
Sub Dropdown1_Change() Dim JumpTo As String Dim RowNum As Integer RowNum = ThisWorkbook.Sheets("Sheet1").Range("B1") JumpTo = ThisWorkbook.Sheets("Sheet1").Cells(RowNum, 1) Range(JumpTo).Select End SubIt is based on the assumption that the Input Range is in the column A (i.e. first column) and that the Cell Link is set to B1.
The attached workbook includes a working example. If this is not what you had in mind, please clarify what you want to achieve.
8 Replies
Not sure if understood your requirement, but if you wish to select a cell of which the reference is selected in the ComboBox, the following simple VBA code does the trick:
Sub Dropdown1_Change() Dim JumpTo As String Dim RowNum As Integer RowNum = ThisWorkbook.Sheets("Sheet1").Range("B1") JumpTo = ThisWorkbook.Sheets("Sheet1").Cells(RowNum, 1) Range(JumpTo).Select End SubIt is based on the assumption that the Input Range is in the column A (i.e. first column) and that the Cell Link is set to B1.
The attached workbook includes a working example. If this is not what you had in mind, please clarify what you want to achieve.
… am getting ´Run time error 13 Type mismatch´ in this line:
RowNum = ThisWorkbook.Sheets("Sheet1").Range("B1")
The B1 cell result is correct, but it does not jump.
Maybe I should explain my exact situation better:
I have a spreadsheet which contains a number of conjugated Spanish verbs.
Each verb uses 24 rows, plus two blank rows as spaces between the verbs. The conjugations occupy columns B to G.
In column A, in line with the first row of each new conjugation, is the infinitive of the verb. In effect, column A, therefore, has a new infinitive every 27 rows with 26 blank cells between them.
I want all of the verbs named in Column A (without the spaces) to appear in the combo box - and when selected, I want the active cell to be the selected verb infinitive in Column A - and for the active cell to scroll to the top row, so that that infinitive and its conjugation are all displayed on the screen.
I hope that makes sense...
Once again, I wish you and yours a very happ and prosperous New Year.
Thank you,
Tom
Perhaps you can upload a (partial) copy of your workbook, including the macro so that I can determine why it doesn't work.
Many thanks for your time and trouble!
I will try it tomorrow, provided I don´t celebrate too hard tonight!
Happy New Year to you and yours … and thanks again.
Tom
