Thread
:
Hyperlink Question
View Single Post
#
7
Posted to microsoft.public.excel.programming
David
external usenet poster
Posts: 1,560
Hyperlink Question
Never mind...had a typo...got it! It's too early.
Thanks much for all your help!
David
"Don Guillett" wrote:
Didn't I give an example of using indirect?
--
Don Guillett
SalesAid Software
"David" wrote in message
...
I got it working using this:
=HYPERLINK("[Book1]June!A1", "A1")
Is there any way to get rid of the workbook name as it changes every
month?
Thanks again!
Gold star for you!
"Don Guillett" wrote:
Why not use INDIRECT for the hyperlink?
=HYPERLINK(INDIRECT(D14&"!a1"))
or to goto
=HYPERLINK("#"&CELL("address",INDIRECT("'"&D14&"'! A1")),D14)
The rest could use a bit of pruning.
RowNum = Application.InputBox(prompt:="What is the Row Number of the
Location You Want to Change?", Type:=2)
FranNum = Application.InputBox(prompt:="What is the Franchise Number You
Want to Use?", Type:=2)
LabelName = Application.InputBox(prompt:="What is the Franchise Name You
Want To Use?", Type:=2)
Sheets("Summary Report of Revenue").Select '<<= Where Location Names Are
If RowNum = < 0 Then
OldLabelName = Cells((RowNum), 1).Value
Columns("B").Hidden = False
Cells((RowNum), 2)= (FranNum)
Cells((RowNum), 1)= (LabelName)
'etc
Cells((RowNum), 1).Select
Selection.Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True
Sheets(OldLabelName).Select
Sheets(OldLabelName).Name = (LabelName)
Sheets("Summary Report of Revenue").Select
Cells((RowNum), 2).Select
Sheets("Summary Report of Revenue").Cells((RowNum), 2).Hyperlinks.Add
Cells((RowNum), 2), "", (LabelName), "My Hyperlink "
--
Don Guillett
SalesAid Software
"David" wrote in message
...
I am changing Location Names in Column A by Row Number. Each Location in
Column A has a hyperlink to a worksheet. I am also changing the
worksheet
name.
When I do this, the hyperlink breaks.
I need to update the hyperlink in Column A to the new name of the
worksheet.
Example. The old location name in Column A, Row 16 may be Richmond, VA.
A16 is also a hyperlink to a worksheet named Richmond, VA.
When I change A16 to Baltimore, MD, the worksheet Richmond, VA is
changed
to
Baltimore, MD, but the hyperlink in A16 no longer functions. I need to
update
the hyperlink in A16 to the new worksheet name to go to Baltimore, MD.
PLEASE HELP!
Here is the code I'm using to change the names...
RowNum = Application.InputBox(prompt:="What is the Row Number of the
Location You Want to Change?", Type:=2)
FranNum = Application.InputBox(prompt:="What is the Franchise Number
You
Want to Use?", Type:=2)
LabelName = Application.InputBox(prompt:="What is the Franchise Name
You
Want To Use?", Type:=2)
Sheets("Summary Report of Revenue").Select '<<= Where Location Names
Are
If RowNum = "0" Then
'do nothing
Else
Cells((RowNum), 1).Select
OldLabelName = Cells((RowNum), 1).Value
Columns("B:B").Select
Selection.EntireColumn.Hidden = False
Cells((RowNum), 2).Select
ActiveCell.FormulaR1C1 = (FranNum)
'Range("A10").Select
Cells((RowNum), 1).Select
ActiveCell.FormulaR1C1 = (LabelName)
'Range("A10").Select
Cells((RowNum), 1).Select
Selection.Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True
Sheets(OldLabelName).Select
Sheets(OldLabelName).Name = (LabelName)
Sheets("Summary Report of Revenue").Select
Cells((RowNum), 2).Select
Sheets("Summary Report of Revenue").Cells((RowNum),
2).Hyperlinks.Add
Cells((RowNum), 2), "", (LabelName), "My Hyperlink "
Don't know what I'm doing with the last line.....
Reply With Quote
David
View Public Profile
Find all posts by David