Listbox - dropdown to jump to a specific part of a Sheet

[desmonda]

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.

 

[Jeffrey Mahoney]

Please try this:

In my example:
Cell B3 is named "Navigate"
Other named ranges are: Dataset2, Dataet10, Dataset11, Frank12

The examples show you can use hyperlinks referring to cells on other sheets, named ranges, and cells on the current sheet.

Book4
	B	C	D
3	Frank12
4
5			Sheet3!A1
6			Sheet2!B10
7			DataSet2
8			Dataset10
9			Dataset11
10			Frank12
11			J100
12			F11
Sheet1

Cells with Data Validation
Cell	Allow	Criteria
B3	List	=$D$5:$D$12

Add this code to the worksheet you have the data validation pulldown with your choices of ranges.

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim i As Range
  Dim NavTxt As String
  Dim ShtName As String
  Dim CellAddr As String
  Dim Cel As Range
  Dim Exc As Long
  Dim N As Name
  Dim NRT As String
  Dim Sht As Worksheet
  
  Set i = Intersect(Target, Range("Navigate"))
  If Not i Is Nothing Then
    NavTxt = i.Value
    Exc = InStr(NavTxt, "!")
    On Error Resume Next
      Set Cel = Range(NavTxt)
    On Error GoTo 0
    If Exc > 0 Then
      ShtName = Left(NavTxt, Exc - 1)
      CellAddr = Mid(NavTxt, Exc + 1, 100)
      Set Sht = Worksheets(ShtName)
      Sht.Activate
      On Error Resume Next
        Set Cel = Sht.Range(CellAddr)
        Application.Goto Cel
      On Error GoTo 0
    ElseIf Not Cel Is Nothing Then
      Application.Goto Cel
    Else
      Application.Goto Reference:=NavTxt
    End If
  End If
  
    
End Sub

[ATTACH type="full" size="1227x112"]122237[/ATTACH]

 

[HighAndWilder]

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.

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
Links

 

[desmonda]

Please try this:

In my example:
Cell B3 is named "Navigate"
Other named ranges are: Dataset2, Dataet10, Dataset11, Frank12

The examples show you can use hyperlinks referring to cells on other sheets, named ranges, and cells on the current sheet.

Book4
	B	C	D
3	Frank12
4
5			Sheet3!A1
6			Sheet2!B10
7			DataSet2
8			Dataset10
9			Dataset11
10			Frank12
11			J100
12			F11
Sheet1

Cells with Data Validation
Cell	Allow	Criteria
B3	List	=$D$5:$D$12

Add this code to the worksheet you have the data validation pulldown with your choices of ranges.

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim i As Range
  Dim NavTxt As String
  Dim ShtName As String
  Dim CellAddr As String
  Dim Cel As Range
  Dim Exc As Long
  Dim N As Name
  Dim NRT As String
  Dim Sht As Worksheet
 
  Set i = Intersect(Target, Range("Navigate"))
  If Not i Is Nothing Then
    NavTxt = i.Value
    Exc = InStr(NavTxt, "!")
    On Error Resume Next
      Set Cel = Range(NavTxt)
    On Error GoTo 0
    If Exc > 0 Then
      ShtName = Left(NavTxt, Exc - 1)
      CellAddr = Mid(NavTxt, Exc + 1, 100)
      Set Sht = Worksheets(ShtName)
      Sht.Activate
      On Error Resume Next
        Set Cel = Sht.Range(CellAddr)
        Application.Goto Cel
      On Error GoTo 0
    ElseIf Not Cel Is Nothing Then
      Application.Goto Cel
    Else
      Application.Goto Reference:=NavTxt
    End If
  End If
 
   
End Sub

[ATTACH type="full" size="1227x112"]122237[/ATTACH]

Hi,

I have created this in a stand alone spreadsheet, however when I try to recreate it in the sheet I am working with, I get an error. When debugging is goes to the "Application.Goto Reference:=NavTxt" line.
Thoughts?

 

[desmonda]

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.

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
Links

View attachment 122242

Please try this:

In my example:
Cell B3 is named "Navigate"
Other named ranges are: Dataset2, Dataet10, Dataset11, Frank12

The examples show you can use hyperlinks referring to cells on other sheets, named ranges, and cells on the current sheet.

Book4
	B	C	D
3	Frank12
4
5			Sheet3!A1
6			Sheet2!B10
7			DataSet2
8			Dataset10
9			Dataset11
10			Frank12
11			J100
12			F11
Sheet1

Cells with Data Validation
Cell	Allow	Criteria
B3	List	=$D$5:$D$12

Add this code to the worksheet you have the data validation pulldown with your choices of ranges.

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim i As Range
  Dim NavTxt As String
  Dim ShtName As String
  Dim CellAddr As String
  Dim Cel As Range
  Dim Exc As Long
  Dim N As Name
  Dim NRT As String
  Dim Sht As Worksheet
 
  Set i = Intersect(Target, Range("Navigate"))
  If Not i Is Nothing Then
    NavTxt = i.Value
    Exc = InStr(NavTxt, "!")
    On Error Resume Next
      Set Cel = Range(NavTxt)
    On Error GoTo 0
    If Exc > 0 Then
      ShtName = Left(NavTxt, Exc - 1)
      CellAddr = Mid(NavTxt, Exc + 1, 100)
      Set Sht = Worksheets(ShtName)
      Sht.Activate
      On Error Resume Next
        Set Cel = Sht.Range(CellAddr)
        Application.Goto Cel
      On Error GoTo 0
    ElseIf Not Cel Is Nothing Then
      Application.Goto Cel
    Else
      Application.Goto Reference:=NavTxt
    End If
  End If
 
   
End Sub

[ATTACH type="full" size="1227x112"]122237[/ATTACH]

Hi,

I have created this in a stand alone spreadsheet, however when I try to recreate it in the sheet I am working with, I get an error. When debugging is goes to the "Application.Goto Reference:=NavTxt" line.
Thoughts?

@ Jeffery I've figured out the issue. The text in the cells and Defined names need to be exactly the same. Is there anyway around this, as using the exact terms will result in a confusing name structure.

Jeffrey Mahoney​