Hyperlink Question

D

David

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.....
 
D

Don Guillett

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 "
 
D

David

I "cleaned up" the section you showed me..thanks!
I'm confused about how to use the =HYPERLINK(INDIRECT(D14&"!a1")) though.
It's not in the code anywhere. Is this a function or does it go in the VBA
code? And what is the D14 referring to? THanks so much for helping me with
this...it's the LAST thing I need to get to work!
 
D

David

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!
 
D

David

I'm using the second indirect statement but still getting "The address of
this site is not valid. Check the address and try again."

Is there something I am not doing right for the "address" part of the
function? I left it as "address". Should I have put something else in the
quotes?

Thanks again!

Don Guillett said:
Didn't I give an example of using indirect?

--
Don Guillett
SalesAid Software
(e-mail address removed)
David said:
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 said:
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
(e-mail address removed)
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.....
Click to expand...
Click to expand...
Click to expand...
 
D

David

Never mind...had a typo...got it! It's too early.
Thanks much for all your help!

David

Don Guillett said:
Didn't I give an example of using indirect?

--
Don Guillett
SalesAid Software
(e-mail address removed)
David said:
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 said:
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
(e-mail address removed)
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.....
Click to expand...
Click to expand...
Click to expand...
 
D

David

This has created another problem.
Since the hyperlink is a formula now, my macro is not following the
hyperlink to the sheet. I recorded what does work, but can't get the syntax
right to use the variable I need. Can you help me with this last little piece?
This is what I had before using your formula:
Cells((RowNum), 1).Select
Selection.Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True

This is what I recorded that works, but has the fixed name in it:
Application.Goto Reference:="'Me, FL'!R1C1"

I need to replace "Me, FL with the "OldLabelName Variable".

This is what I tried....but it bombs:
Application.Goto Reference:="'(OldLabelName)' & !R1C1"

Thanks so much again!

David
 
D

David

I figured it out...I used:
Application.Goto Reference:=Worksheets(OldLabelName).Range("A1"), _
scroll:=True

Thanks again! What a relief!!

David
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Resetting Hyperlink to Worksheet 3
Adjusting alogarithm 18
Populate a userform from a worksheet 1
I need help with a "Run-Time Error 13: Type Mismatch" 2
Select rows equal to filter and highlight row 4
Combine Two Different Worksheet_Change codes 9
Range Formula Problem 6
Slow Text File Import 5

Top