Thread: Simple Hyperlink Macro

Hi. I am using MS Excel 2003, and I am very new to the program. I don't know
how to use visual basic or programming of any kind. I need help with a
macro, and I'm hoping this is the best place to ask for help.

I have a worksheet entitled "NT." In column "A" I have a very long list of
vocabulary words (not alphabetized). For reasons I won't bother you with, I
want to establish hyperlinks for each of these words (column A) in another
worksheet entitled "LINKS" (also in column A). Thus, when I click on the
hyperlink for a word in LINKS, I will be taken to the hyperlink in NT.

I tried several times to make a macro but failed. I couldn't for the life
of me get the macro to change panes or windows (Control F6 and so on). Could
someone please help me with this macro? I would deeply appreciate your help.
Also, how can I set up a hot key so I don't have to run the macro one by one
through the entire column A in NT. In other words, could the macro be run
once and all hyperlinks be inserted in LINKS, instead of run laboriously word
by word?

Hi Rebecca,

Try this and see if it does what you want to create the links.

I assume that both sheets are in the same workbook, and that there are
no blank cells in your word list. This macro will stop as soon as it
encounters an empty cell in the column.

You need to put this code into a module. Press Alt+F11 to open the
Visual Basic Editor and find your workbook's project. Right-click on it
and select Insert > Module. Now enter this macro in the module's code
window (to the right):

Sub CreateLinks()
' select the first name cell before running
Dim sCellAddress As String
Dim sFormulaPrefix As String
Dim SLinkFormula As String

sFormulaPrefix = "=Hyperlink(""[" & _
ActiveWorkbook.FullName & "]" & _
ActiveSheet.Name & "!"

While Not IsEmpty(ActiveCell.Value)
sCellAddress = ActiveCell.Address
SLinkFormula = sFormulaPrefix & _
sCellAddress & """,""" & _
CStr(ActiveCell.Value) & """)"
Worksheets("Links").Activate
Range(sCellAddress).Formula = SLinkFormula
Worksheets("NT").Activate
ActiveCell.Offset(1, 0).Select
Wend
End Sub

Return to Excel (Alt+F11), select the first word in the NT sheet, and
run the macro (Tools > Macro > Macros...).

This should build your hyperlinks using the same words as in the NT
sheet, and in the corresponding cells. It's not anything fancy or
general purpose, just a straightforward approach to do what you
described (as I understood it :-).

I hope that it works for you,

Gary
[DropContiguousDigitsTwoThruFiveForDirect]

Hi Rebecca,

Try this and see if it does what you want to create the links.

I assume that both sheets are in the same workbook, and that there are
no blank cells in your word list. This macro will stop as soon as it
encounters an empty cell in the column.

You need to put this code into a module. Press Alt+F11 to open the
Visual Basic Editor and find your workbook's project. Right-click on it
and select Insert > Module. Now enter this macro in the module's code
window (to the right):

Sub CreateLinks()
' select the first name cell before running
Dim sCellAddress As String
Dim sFormulaPrefix As String
Dim SLinkFormula As String

sFormulaPrefix = "=Hyperlink(""[" & _
ActiveWorkbook.FullName & "]" & _
ActiveSheet.Name & "!"

While Not IsEmpty(ActiveCell.Value)
sCellAddress = ActiveCell.Address
SLinkFormula = sFormulaPrefix & _
sCellAddress & """,""" & _
CStr(ActiveCell.Value) & """)"
Worksheets("Links").Activate
Range(sCellAddress).Formula = SLinkFormula
Worksheets("NT").Activate
ActiveCell.Offset(1, 0).Select
Wend
End Sub

Return to Excel (Alt+F11), select the first word in the NT sheet, and
run the macro (Tools > Macro > Macros...).

This should build your hyperlinks using the same words as in the NT
sheet, and in the corresponding cells. It's not anything fancy or
general purpose, just a straightforward approach to do what you
described (as I understood it :-).

I hope that it works for you,

Gary
[DropContiguousDigitsTwoThruFiveForDirect]

Thanks, Gary, it worked perfectly.

"GaryDK" wrote:

> Hi Rebecca,
>
> Try this and see if it does what you want to create the links.
>
> I assume that both sheets are in the same workbook, and that there are
> no blank cells in your word list. This macro will stop as soon as it
> encounters an empty cell in the column.
>
> You need to put this code into a module. Press Alt+F11 to open the
> Visual Basic Editor and find your workbook's project. Right-click on it
> and select Insert > Module. Now enter this macro in the module's code
> window (to the right):
>
> Sub CreateLinks()
> ' select the first name cell before running
> Dim sCellAddress As String
> Dim sFormulaPrefix As String
> Dim SLinkFormula As String
>
> sFormulaPrefix = "=Hyperlink(""[" & _
> ActiveWorkbook.FullName & "]" & _
> ActiveSheet.Name & "!"
>
> While Not IsEmpty(ActiveCell.Value)
> sCellAddress = ActiveCell.Address
> SLinkFormula = sFormulaPrefix & _
> sCellAddress & """,""" & _
> CStr(ActiveCell.Value) & """)"
> Worksheets("Links").Activate
> Range(sCellAddress).Formula = SLinkFormula
> Worksheets("NT").Activate
> ActiveCell.Offset(1, 0).Select
> Wend
> End Sub
>
> Return to Excel (Alt+F11), select the first word in the NT sheet, and
> run the macro (Tools > Macro > Macros...).
>
> This should build your hyperlinks using the same words as in the NT
> sheet, and in the corresponding cells. It's not anything fancy or
> general purpose, just a straightforward approach to do what you
> described (as I understood it :-).
>
> I hope that it works for you,
>
> Gary
> [DropContiguousDigitsTwoThruFiveForDirect]
>
>