hyperlink searchability

[rtgjeg]

I have a workbook with over 100 sheets. I have some text on sheet1 (about 100 rows) that I want to link to one of the other sheet in that workbook. Same thing for sheet 2, 3, etc. I know how to add a link using Excel's built-in insert a hyperlink command. This presents you with a dialog box. When I choose 'Places in this document' I am presented with a list of sheets that I can link to. Is it possible to search this list rather than scroll through it. As I said the workbook has over 100 sheets, and each sheet has about 100 rows to be linked. It is very tiresome to scroll through this dialog. I'm looking for a way to speed it up. Thanks.

 

[rtgjeg]

Each sheet is the source code of a vba routine. The line 5 on sheet 2 says "call x". Sheet 50 has the code for Sub x(). Therefore I need to put a link on line 5 of sheet 2 that takes me to sheet 50. Rather than scrolling through the dialog box that the insert hyperlink command gives me I'm looking for a search routine that I can use so that when I'm on that line I can just type "sub x" and it will insert a link to that sheet.

 

[My Aswer Is This]

Hoping someone else will jump in here and help but until that time.
You said:

The line 5 on sheet 2 says "call x".

Row(5) on sheet 2 has 15,500 cells.

So you need to be more specific

Lets say you click on Range("G4") of sheet(2) and in Range("G4") you have "David" we put a link in that cell to Range("A1") of sheet named "David" would that not work ??


​

 

[rtgjeg]

Sorry, I guess I wasn't clear the only cells with data are in column B, so row 5 only has one cell. Each sheet has only one column with data. To give an example Sheet 1 line 1 says Sub Main() in column B. Row 2 is blank. Row 3 says Dim x As Integer in column B, row 4 says Dim y As Integer in column B. Line 5 says Call x in column B. Sheet 50 says Sub x() in column B. I want to put a link in Column B on sheet 1 row 5 that goes to cell A1 on sheet 50 when the user clicks it. When I'm putting that link on Sheet 1, I don't want to have to scroll through the dialog box to choose sheet 50. I'm looking for a 'search box' so that I can type 'sheet 50' and it will find it and link to it automatically.

 

[My Aswer Is This]

You cannot run Excel vba scripts like this:

 

[My Aswer Is This]

You said earlier

I have some text on sheet1 (about 100 rows)

And then you said Column(B)


Are you saying if you click on a cell in Column B of sheet(1) you want to be linked to another sheet.

So if you click on Range("B1") you will be taken to Sheet(2) Range("A1")

So if you click on Range("B2") you will be taken to Sheet(3) Range("A1")

This can be done very easily if you let me do it my way.

I will put each sheet name in column B of sheet(1) and then if you click on that sheet named you will be taken to that sheet.

So on Sheet(1) if you click on Mom which will be entered by my script you will be Taken to sheet named Mom Range("A1")



​

_{^{<strike>
</strike>}}​

 

[My Aswer Is This]

My script in previous post will probable not work if what you said in Post 3 is correct.

But I see no logic in what your saying in Post 3

You said in post 3

Sheet 2, line 10 to sheet 3, line 15 to sheet 6, line 20 to sheet 1 and so on.

And so on?? Line 10 to 3 line 15 to 6 line 20 to 1

I see no trend here.

So how can you say ​

and so on.​

​

 

[rtgjeg]

There is no trend. Sheet 1 contains vba source code as does sheet 2 - 100+ The source code is different on each sheet. The code on sheet 1 line 5 says to 'call x'. Sub x() is on sheet 50 line 1 column B. I want to link sheet 1 line 5 to sheet 50. Now sheet 1 line 10 says "call y'. Sub y() is found on sheet 2. I want to link sheet 1 line 10 to sheet 2. Now I know how to do this. Just use Excel's built-in command Insert hyperlink and scroll the dialog box until the proper sheet is shown and highlight it then click ok. I was just looking for some way to implement a search box instead of scrolling through the dialog,

 

[My Aswer Is This]

Show me the code your using:

I want to see the code

 

[My Aswer Is This]

Here is what code looks like:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("A:A")) Is Nothing Then
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
If Target.Hyperlinks.Count > 0 Then Target.Offset(, 1).Value = Date
End If
End Sub

I want you to post the code your using here in this forum

 

[rtgjeg]

[TABLE="width: 1029"]
<tbody>[TR]
[TD]Sub z_Favorite_Bookmarks()[/TD]
[/TR]
[TR]
[TD]     If Not bDebug Then On Error GoTo etrap:[/TD]
[/TR]
[TR]
[TD]     Application.EnableEvents = True[/TD]
[/TR]
[TR]
[TD][URL="https://www.mrexcel.com/forum/#RANGE!B17"]     ZZ_Favorite_Bookmarks[/URL][/TD]
[/TR]
[TR]
[TD]     If Not bDebug Then On Error Resume Next[/TD]
[/TR]
[TR]
[TD]     Application.EnableEvents = True[/TD]
[/TR]
[TR]
[TD]     Exit Sub[/TD]
[/TR]
[TR]
[TD]etrap:[/TD]
[/TR]
[TR]
[TD]     Application.EnableEvents = True[/TD]
[/TR]
[TR]
[TD]     Application.ScreenUpdating = True: MsgBox "The request could not be done: " & Error.Description, , addinTitle[/TD]
[/TR]
[TR]
[TD]     Application.ScreenUpdating = True[/TD]
[/TR]
[TR]
[TD]     End[/TD]
[/TR]
[TR]
[TD]End Sub[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]Sub ZZ_Favorite_Bookmarks()[/TD]
[/TR]
[TR]
[TD]     Dim I                         As Integer[/TD]
[/TR]
[TR]
[TD]     Dim selectedEntries           As String[/TD]
[/TR]
[TR]
[TD]     Dim bPosition                 As Boolean[/TD]
[/TR]
[TR]
[TD]     bRun = False[/TD]
[/TR]
[TR]
[TD][URL="https://www.mrexcel.com/forum/#OptionsMsgForm!A1"]     Unload OptionsMsgForm[/URL][/TD]
[/TR]
</tbody>[/TABLE]