| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hyperlink Question
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..... |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Hyperlink Question
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! "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..... |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Hyperlink Question
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..... |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Hyperlink Question
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..... |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Hyperlink Question
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" 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..... |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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..... |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Hyperlink Question
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 "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..... |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Hyperlink Question
I figured it out...I used:
Application.Goto Reference:=Worksheets(OldLabelName).Range("A1"), _ scroll:=True Thanks again! What a relief!! David "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..... |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Hyperlink Question
Glad you were able to get it as desired.
-- Don Guillett SalesAid Software "David" wrote in message ... I figured it out...I used: Application.Goto Reference:=Worksheets(OldLabelName).Range("A1"), _ scroll:=True Thanks again! What a relief!! David "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 |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Hyperlink Question | Excel Discussion (Misc queries) | |||
| Hyperlink Question | Excel Discussion (Misc queries) | |||
| Hyperlink question | Excel Programming | |||
| hyperlink question | Excel Discussion (Misc queries) | |||
| VBA Hyperlink Question | Excel Programming | |||
Linear Mode
