| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Resetting Hyperlink to Worksheet
I have a program that renames the names in Column A and its associated
worksheet. All the names in Column A are hyperlinked to its associated worksheet. The names in Column A and the worksheet name are the same. When I make the name changes, the hyperlink is broken. I just need to set the hyperlink in the cell that was changed in Column A to it's associated worksheet again. Here is the code I use to make the changes. Thanks! 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) If RowNum = "0" Then 'do nothing Else Cells((RowNum), 1).Select OldLabelName = Cells((RowNum), 1).Value Cells((RowNum), 2).Select ActiveCell.FormulaR1C1 = (FranNum) Cells((RowNum), 1).Select ActiveCell.FormulaR1C1 = (LabelName) Cells((RowNum), 1).Select Selection.Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True Sheets(OldLabelName).Select Sheets(OldLabelName).Name = (LabelName) NEED CODE HERE TO RESET THE HYPERLINK TO THE SHEET ONLY - NOT TO A CELL ON THE SHEET. David |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Resetting Hyperlink to Worksheet
Perhaps using a separate column for the active "clickable" links. If you are
updating a link in column A and the result does not "click", then in another column enter: =HYPERLINK(A1) This should always give a "clickable" result. -- Gary''s Student - gsnu200721 "David" wrote: I have a program that renames the names in Column A and its associated worksheet. All the names in Column A are hyperlinked to its associated worksheet. The names in Column A and the worksheet name are the same. When I make the name changes, the hyperlink is broken. I just need to set the hyperlink in the cell that was changed in Column A to it's associated worksheet again. Here is the code I use to make the changes. Thanks! 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) If RowNum = "0" Then 'do nothing Else Cells((RowNum), 1).Select OldLabelName = Cells((RowNum), 1).Value Cells((RowNum), 2).Select ActiveCell.FormulaR1C1 = (FranNum) Cells((RowNum), 1).Select ActiveCell.FormulaR1C1 = (LabelName) Cells((RowNum), 1).Select Selection.Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True Sheets(OldLabelName).Select Sheets(OldLabelName).Name = (LabelName) NEED CODE HERE TO RESET THE HYPERLINK TO THE SHEET ONLY - NOT TO A CELL ON THE SHEET. David |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Resetting Hyperlink to Worksheet
The worksheet is fixed and used by multiple people in the office.
The way the program works, people need to change the number in Column B, the Location Name in Column A, and have the Location Name in Column A be a hyperlink to a worksheet that will be named the same as the Location Name in Column A. Once the worksheet name is changed, the hyperlink is broken. I need to reset the hyperlink to the new worksheet name. Thanks! David "Gary''s Student" wrote: Perhaps using a separate column for the active "clickable" links. If you are updating a link in column A and the result does not "click", then in another column enter: =HYPERLINK(A1) This should always give a "clickable" result. -- Gary''s Student - gsnu200721 "David" wrote: I have a program that renames the names in Column A and its associated worksheet. All the names in Column A are hyperlinked to its associated worksheet. The names in Column A and the worksheet name are the same. When I make the name changes, the hyperlink is broken. I just need to set the hyperlink in the cell that was changed in Column A to it's associated worksheet again. Here is the code I use to make the changes. Thanks! 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) If RowNum = "0" Then 'do nothing Else Cells((RowNum), 1).Select OldLabelName = Cells((RowNum), 1).Value Cells((RowNum), 2).Select ActiveCell.FormulaR1C1 = (FranNum) Cells((RowNum), 1).Select ActiveCell.FormulaR1C1 = (LabelName) Cells((RowNum), 1).Select Selection.Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True Sheets(OldLabelName).Select Sheets(OldLabelName).Name = (LabelName) NEED CODE HERE TO RESET THE HYPERLINK TO THE SHEET ONLY - NOT TO A CELL ON THE SHEET. David |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Resetting Hyperlink to Worksheet
Hyperlinks to a different sheet in the same workbook can be implemented by
either: Insert Hyperlink or the HYPERLINK () function. The advantage to using the function is that if you have many hyperlinks of the form: =HYPERLINK("#Sheet3!A1","table of contents") where they all refer to Sheet3 then the better form is: =HYPERLINK("#" & A1 & "!Z100","table of contents") Where you put Sheet3 in cell A1. All these hyperlinks will refer to cell A1 for the sheetname. You only have to change one cell to "un-break" all the links. -- Gary''s Student - gsnu200721 "David" wrote: The worksheet is fixed and used by multiple people in the office. The way the program works, people need to change the number in Column B, the Location Name in Column A, and have the Location Name in Column A be a hyperlink to a worksheet that will be named the same as the Location Name in Column A. Once the worksheet name is changed, the hyperlink is broken. I need to reset the hyperlink to the new worksheet name. Thanks! David "Gary''s Student" wrote: Perhaps using a separate column for the active "clickable" links. If you are updating a link in column A and the result does not "click", then in another column enter: =HYPERLINK(A1) This should always give a "clickable" result. -- Gary''s Student - gsnu200721 "David" wrote: I have a program that renames the names in Column A and its associated worksheet. All the names in Column A are hyperlinked to its associated worksheet. The names in Column A and the worksheet name are the same. When I make the name changes, the hyperlink is broken. I just need to set the hyperlink in the cell that was changed in Column A to it's associated worksheet again. Here is the code I use to make the changes. Thanks! 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) If RowNum = "0" Then 'do nothing Else Cells((RowNum), 1).Select OldLabelName = Cells((RowNum), 1).Value Cells((RowNum), 2).Select ActiveCell.FormulaR1C1 = (FranNum) Cells((RowNum), 1).Select ActiveCell.FormulaR1C1 = (LabelName) Cells((RowNum), 1).Select Selection.Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True Sheets(OldLabelName).Select Sheets(OldLabelName).Name = (LabelName) NEED CODE HERE TO RESET THE HYPERLINK TO THE SHEET ONLY - NOT TO A CELL ON THE SHEET. David |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Chart worksheet names keep resetting | Charts and Charting in Excel | |||
| Hyperlink - particular worksheet | Excel Discussion (Misc queries) | |||
| Troubleshoot resetting hyperlink base to a network drive | Excel Worksheet Functions | |||
| Resetting the end of a worksheet | New Users to Excel | |||
| resetting worksheet properties | Excel Programming | |||
Linear Mode
