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

desmonda

New Member
Joined
Aug 3, 2021
Messages
1
Office Version
  1. 2016
Platform
  1. Windows
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.
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
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
BCD
3Frank12
4
5Sheet3!A1
6Sheet2!B10
7DataSet2
8Dataset10
9Dataset11
10Frank12
11J100
12F11
Sheet1
Cells with Data Validation
CellAllowCriteria
B3List=$D$5:$D$12


Add this code to the worksheet you have the data validation pulldown with your choices of ranges.
VBA Code:
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]
 

Attachments

  • 1739222353007.png
    1739222353007.png
    23.4 KB · Views: 1
Upvote 0
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
ABC
1OptionRange
2Link 1Sheet3!A43
3Link 2Sheet2!A55
4Link 3DataSet2
5Link 4Dataset10
6Link 5Dataset11
7Link 6Frank12
8Link 7J100
9Link 8F11
10ResetSheet1!A1
11
Links


1739260190227.png
 
Upvote 0

Forum statistics

Threads
1,226,515
Messages
6,191,488
Members
453,659
Latest member
thomji1

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top