Hi,
We have a spreadsheet which details a significant amount in information.
In order to navigate to specific parts I want to create a Listbox with Hyperlinks (or similar) to specific locations in the sheet.
I have done created the hyperlinks in cells, bit want to tidy them up into the Dropdown menu.
Thanks.
I have a Userform which lists the links contained in a worksheet named Links.
The Userform floats on the top the active sheet so you don't have to go back to the sheet with the Listbox on it.
Upon selecting an option on the listbox, the appropriate sheet is activated and the active window scrolls to the row two rows above the row of the first
cell of the range and to the column two columns to the left of the first cell of the range.
The 'Reset' option will, for each sheet in the workbook, scroll to the first row and first column and end up on the sheet and cell specified as the range for the
Reset option.
The Userform just has one control, a listbox named lstReferences with two columns.
You will need to set the columns widths and Userform width to suit the length of the data in the links sheet.
The sheet and cell selected by the link is referenced in the caption of the Userform.
Column two on the listbox can be hidden by changing the column width to 0.
Paste this VBA code into Userform Code Module.
You could have a button or a shortcut key to load the Userform.
If you want to implement this and need help just ask.
VBA Code:
Option Explicit
Private Sub lstReferences_Click()
Dim lngRow As Long
Dim rng As Range
Dim Ws As Worksheet
If Me.lstReferences.Column(0) = "Reset" Then
For Each Ws In Worksheets
Ws.Activate
ActiveWindow.ScrollRow = 1
ActiveWindow.ScrollColumn = 1
Next Ws
End If
On Error Resume Next
Set rng = Range(Me.lstReferences.Column(1)).Cells(1)
On Error GoTo 0
If Not rng Is Nothing Then
Me.Caption = rng.Parent.Name & " " & rng.Address
Worksheets(rng.Parent.Name).Activate
ActiveWindow.ScrollRow = WorksheetFunction.Max(rng.Row - 2, 1)
ActiveWindow.ScrollColumn = WorksheetFunction.Max(rng.Column - 2, 1)
Application.Goto Range(Me.lstReferences.Column(1)).Cells(1)
End If
End Sub
Private Sub UserForm_Initialize()
Dim rng As Range
Dim arr() As Variant
Dim i As Integer
Set rng = Worksheets("Links").Range("A2:B" & Worksheets("Links").Range("A" & Rows.Count).End(xlUp).Row)
arr = rng.Value
For i = 1 To UBound(arr)
With Me.lstReferences
.AddItem
.List(i - 1, 0) = arr(i, 1)
.List(i - 1, 1) = arr(i, 2)
End With
Next i
End Sub
Listbox Links.xlsm |
---|
|
---|
| A | B | C |
---|
1 | Option | Range | |
---|
2 | Link 1 | Sheet3!A43 | |
---|
3 | Link 2 | Sheet2!A55 | |
---|
4 | Link 3 | DataSet2 | |
---|
5 | Link 4 | Dataset10 | |
---|
6 | Link 5 | Dataset11 | |
---|
7 | Link 6 | Frank12 | |
---|
8 | Link 7 | J100 | |
---|
9 | Link 8 | F11 | |
---|
10 | Reset | Sheet1!A1 | |
---|
11 | | | |
---|
|
---|