Indirect Hyperlink from a graphic to a calculated cell or range

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

KenInPortland - 07 Feb 2008 01:18 GMT

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.

Reply to this Message

Bernie Deitrick - 07 Feb 2008 01:43 GMT

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

>I need to make a hyperlink from a graphic to link indirectly to a cell or a
> range of cells in the same worksheet.
[quoted text clipped - 10 lines]
> jump from the 26 graphics labeled A to Z to the proper cells address which
> are contained in K1 to K26.

Reply to this Message

KenInPortland - 07 Feb 2008 17:01 GMT

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.

> Ken,
>
[quoted text clipped - 31 lines]
> > jump from the 26 graphics labeled A to Z to the proper cells address which
> > are contained in K1 to K26.

Reply to this Message

Bernie Deitrick - 07 Feb 2008 17:47 GMT

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
[quoted text clipped - 40 lines]
>> > jump from the 26 graphics labeled A to Z to the proper cells address which
>> > are contained in K1 to K26.

Reply to this Message

KenInPortland - 11 Feb 2008 21:06 GMT

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,
>
[quoted text clipped - 57 lines]
> >> > jump from the 26 graphics labeled A to Z to the proper cells address which
> >> > are contained in K1 to K26.

Reply to this Message

KenInPortland - 11 Feb 2008 21:10 GMT

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,
>
[quoted text clipped - 57 lines]
> >> > jump from the 26 graphics labeled A to Z to the proper cells address which
> >> > are contained in K1 to K26.

Reply to this Message

KenInPortland - 07 Feb 2008 16:35 GMT

Thanks Bernie,
You have put me on the right track. I simplified my needs for the posting so
I will have to expand on it. But I am getting the proper kind of response
from Excel.
Thanks.

Is there a way to do this without a macro, or VBA?
Using these solutions creates a problem for the hundreds of users whom I
distribute this spreadsheet to (and those that download it) with the annoying
"Enable macros" and "Disable macros" messages everytime they open it.
Thanks again,

Reply to this Message