Indirect Hyperlink from a graphic to a calculated cell or range

[KenInPortland]

I need to make a hyperlink from a graphic to link indirectly to a cell or a
range of cells in the same worksheet.
The worksheet is alphabetized and I have created graphic buttons across the
top with the letters A to Z. The number of rows (1000+) changes frequently
and the A to Z tabs are used to index into the correct area of the
spreadsheet, which I have been manually updating.

I can calculate the starting address for each of the tabs, but I have yet to
find the proper combination of Hypertext, Cell, Indirect, &, “, ‘, # etc. to
jump from the 26 graphics labeled A to Z to the proper cells address which
are contained in K1 to K26.

 

[Bernie Deitrick]

Ken,

Assign a macro that reads the text from the button and acts on the basis of
the return. For example, assign this to each button to have the buttons
select the ranges addresses listed in K1:K26 - this assumes that the labels
are A, B, C, etc.

Sub WhoRang()
Dim myname As String
Dim myStr As String

myname = Application.Caller
myStr = Left(ActiveSheet.Shapes(myname).TextFrame.Characters.Text, 1)
Application.Goto Range(Range("K" & Asc(myStr) - Asc("A") + 1).Value)

End Sub

HTH,
Bernie
MS Excel MVP

 

[KenInPortland]

Thanks Bernie,
You put me on the right track, I have some work to do, since I simplified my
situation for the post.

Do you know of a way to accomplish this functionality without a macro or VBA?

The annoying "enable macros" "disable macros" message will create a problem
for the hundreds I distribute this to.

 

[Bernie Deitrick]

Ken,

Use a hyperlink in a cell:

=HYPERLINK("[book6.xls]Sheet1!" & K1,"A")

If you sheet name has spaces in it, then you need

=HYPERLINK("[book 6.xls]'Sheet 1'!" & K1,"A")

Change the book6.xls to the workbook name, the Sheet1 to the sheet name, and K1 to the cell with the
address that you want to jump to. And change the A to whatever label you want.

HTH,
Bernie
MS Excel MVP

 

[KenInPortland]

Thanks Bernie,
Using a cell per hyperlink is not an option (too many links not enough cells
of the appropriate size), so I bit the bullet and used the macro approach you
described below and it works great. Here is my code which compares the first
5 chars of the buttons to column J and jumps to the corresponding K column
location. We'll see how the users react to recieving a macro.

Thanks so much, I never would have found the answer elsewhere.
Ken

Ken,

Use a hyperlink in a cell:

=HYPERLINK("[book6.xls]Sheet1!" & K1,"A")

If you sheet name has spaces in it, then you need

=HYPERLINK("[book 6.xls]'Sheet 1'!" & K1,"A")

Change the book6.xls to the workbook name, the Sheet1 to the sheet name, and K1 to the cell with the
address that you want to jump to. And change the A to whatever label you want.

HTH,
Bernie
MS Excel MVP

Thanks Bernie,
You put me on the right track, I have some work to do, since I simplified my
situation for the post.

Do you know of a way to accomplish this functionality without a macro or VBA?

The annoying "enable macros" "disable macros" message will create a problem
for the hundreds I distribute this to.

 

[KenInPortland]

Here is the code,
Sub WhoRang()
Dim ButtonLabel As String
Dim First6chars As String
Dim RowNum As String

' This subroutine is attached to each graphic box
ButtonLabel = Application.Caller
' Get the first six characters of the ButtonLabel
First6chars =
Left(ActiveSheet.Shapes(ButtonLabel).TextFrame.Characters.Text, 6)
' Use the first six characters to match column J
RowNum = Range("J1:J32").Find(What:=First6chars).Row
' Use the resulting RowNum to get the Range of cells from column K to Go to
Application.Goto Range(Range("K" & RowNum).Value)

'Debug.Print ButtonLabel, First6chars, RowNum
End Sub

Ken,

Use a hyperlink in a cell:

=HYPERLINK("[book6.xls]Sheet1!" & K1,"A")

If you sheet name has spaces in it, then you need

=HYPERLINK("[book 6.xls]'Sheet 1'!" & K1,"A")

Change the book6.xls to the workbook name, the Sheet1 to the sheet name, and K1 to the cell with the
address that you want to jump to. And change the A to whatever label you want.

HTH,
Bernie
MS Excel MVP

Thanks Bernie,
You put me on the right track, I have some work to do, since I simplified my
situation for the post.

Do you know of a way to accomplish this functionality without a macro or VBA?

The annoying "enable macros" "disable macros" message will create a problem
for the hundreds I distribute this to.