Forum Discussion
Go To a cell with a specific value using a combo or list box.
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.
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.
Thanks! That helps. Could reduce the macro to one line of code. But I did add a Sheet2 where you list every verb that exists in Sheet1. Drag down the formula in column B until you reach your last verb. Column A will "fill" the combo box and column B calculates the row number in Sheet1 for the selected verb.
Sheet2 further contains the linked cell of the combo box in D1. Cell E1 determines to which row the screen in Sheet1 should scroll.
Hope you find this useful.