Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using Excel Hyperlinks to refer to a Subroutine rather than a Cell
Hello Again -
I have found that given a list of worksheet names I can create hyperlinks to those worksheets (let's say specifically Cell P2) using the following code: ws.Activate For row = tRow To bRow ws.Cells(row, col).Select With Selection t2D = Selection.Value ' Note that the Cell value is the WS Name subA = "'" & t2D & "'!P2" .ClearContents ws.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:=subA, TextToDisplay:=t2D End With Next I can also scroll a worksheet to exactly the right cell using: Sub scroll2Column(wsName) Sheets(wsName).Activate ActiveWindow.ScrollColumn = 16 ActiveSheet.Range("P2").Select End Sub I would like to combine these two concpets and create a hyperlink which doesn't just refer to the cell but to the subroutine which brings the user to the cell and then conviently scrolls the worksheet as well. Despite many attempts at this, I have not gotten that to work. Does anyone know how this might be done? Thanks in advance, Chris Tauss ) |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using Excel Hyperlinks to refer to a Subroutine rather than a Cell
Try some code like the following:
Sub AAA() Dim R As Range Dim HL As Hyperlink Set R = Range("C3") Set HL = R.Hyperlinks.Add(anchor:=R, _ Address:=vbNullString, _ SubAddress:="'Sheet Three'!K128", _ TextToDisplay:="click for K28") End Sub Cordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group Pearson Software Consulting, LLC www.cpearson.com (email on web site) On Wed, 15 Oct 2008 07:25:01 -0700, ct60 wrote: Hello Again - I have found that given a list of worksheet names I can create hyperlinks to those worksheets (let's say specifically Cell P2) using the following code: ws.Activate For row = tRow To bRow ws.Cells(row, col).Select With Selection t2D = Selection.Value ' Note that the Cell value is the WS Name subA = "'" & t2D & "'!P2" .ClearContents ws.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:=subA, TextToDisplay:=t2D End With Next I can also scroll a worksheet to exactly the right cell using: Sub scroll2Column(wsName) Sheets(wsName).Activate ActiveWindow.ScrollColumn = 16 ActiveSheet.Range("P2").Select End Sub I would like to combine these two concpets and create a hyperlink which doesn't just refer to the cell but to the subroutine which brings the user to the cell and then conviently scrolls the worksheet as well. Despite many attempts at this, I have not gotten that to work. Does anyone know how this might be done? Thanks in advance, Chris Tauss ) |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using Excel Hyperlinks to refer to a Subroutine rather than a
Thanks Chip -
That will bring the user to the cell -- but not actually scroll the WS. Ideally, I would like to add the scrolling aspect so I think I need to refer to a subroutine rather than a cell ref. I am just wondering if this is possible. Best Regards, Chris "Chip Pearson" wrote: Try some code like the following: Sub AAA() Dim R As Range Dim HL As Hyperlink Set R = Range("C3") Set HL = R.Hyperlinks.Add(anchor:=R, _ Address:=vbNullString, _ SubAddress:="'Sheet Three'!K128", _ TextToDisplay:="click for K28") End Sub Cordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group Pearson Software Consulting, LLC www.cpearson.com (email on web site) On Wed, 15 Oct 2008 07:25:01 -0700, ct60 wrote: Hello Again - I have found that given a list of worksheet names I can create hyperlinks to those worksheets (let's say specifically Cell P2) using the following code: ws.Activate For row = tRow To bRow ws.Cells(row, col).Select With Selection t2D = Selection.Value ' Note that the Cell value is the WS Name subA = "'" & t2D & "'!P2" .ClearContents ws.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:=subA, TextToDisplay:=t2D End With Next I can also scroll a worksheet to exactly the right cell using: Sub scroll2Column(wsName) Sheets(wsName).Activate ActiveWindow.ScrollColumn = 16 ActiveSheet.Range("P2").Select End Sub I would like to combine these two concpets and create a hyperlink which doesn't just refer to the cell but to the subroutine which brings the user to the cell and then conviently scrolls the worksheet as well. Despite many attempts at this, I have not gotten that to work. Does anyone know how this might be done? Thanks in advance, Chris Tauss ) |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using Excel Hyperlinks to refer to a Subroutine rather than a Cell
Let me answer my own question.
There is a Workbook Event "Workbook_SheetFollowHyperlink" which allows for code to be run after a hyperlink is clicked. So, I put in the following code which does what I was hoping for: Private Sub Workbook_SheetFollowHyperlink(ByVal Sh As Object, ByVal Target As Hyperlink) Dim rangeString As String Dim rangeSubString As String Dim whereExclaim As Integer Dim scrollCol As Integer rangeString = Target.SubAddress whereExclaim = InStr(1, Target.SubAddress, "!") If whereExclaim = 0 Then Exit Sub End If rangeSubString = Mid(rangeString, whereExclaim + 1) scrollCol = Range(rangeSubString).Column ActiveWindow.ScrollColumn = scrollCol End Sub Note that, I also freeze the panes of each worksheet first then set the scroll column. The net effect is that the data is lined up very nicely with the frozen columns to the left and the headers at the top. Nice - if I do say so myself. Hope this helps. Chris ) "ct60" wrote: Hello Again - I have found that given a list of worksheet names I can create hyperlinks to those worksheets (let's say specifically Cell P2) using the following code: ws.Activate For row = tRow To bRow ws.Cells(row, col).Select With Selection t2D = Selection.Value ' Note that the Cell value is the WS Name subA = "'" & t2D & "'!P2" .ClearContents ws.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:=subA, TextToDisplay:=t2D End With Next I can also scroll a worksheet to exactly the right cell using: Sub scroll2Column(wsName) Sheets(wsName).Activate ActiveWindow.ScrollColumn = 16 ActiveSheet.Range("P2").Select End Sub I would like to combine these two concpets and create a hyperlink which doesn't just refer to the cell but to the subroutine which brings the user to the cell and then conviently scrolls the worksheet as well. Despite many attempts at this, I have not gotten that to work. Does anyone know how this might be done? Thanks in advance, Chris Tauss ) |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using Excel Hyperlinks to refer to a Subroutine rather than a
If the hyperlink is on one worksheet and it points to a cell on
another sheet, Excel will scroll to the destination sheet when the user clicks on the link. For example, with Sheet1 active, use the code I provided and change the reference from "Sheet Three" to any worksheet other than Sheet1. When click on the link on Sheet1, Excel will take you to the worksheet that is the target of the link. Cordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group Pearson Software Consulting, LLC www.cpearson.com (email on web site) On Wed, 15 Oct 2008 08:29:02 -0700, ct60 wrote: Thanks Chip - That will bring the user to the cell -- but not actually scroll the WS. Ideally, I would like to add the scrolling aspect so I think I need to refer to a subroutine rather than a cell ref. I am just wondering if this is possible. Best Regards, Chris "Chip Pearson" wrote: Try some code like the following: Sub AAA() Dim R As Range Dim HL As Hyperlink Set R = Range("C3") Set HL = R.Hyperlinks.Add(anchor:=R, _ Address:=vbNullString, _ SubAddress:="'Sheet Three'!K128", _ TextToDisplay:="click for K28") End Sub Cordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group Pearson Software Consulting, LLC www.cpearson.com (email on web site) On Wed, 15 Oct 2008 07:25:01 -0700, ct60 wrote: Hello Again - I have found that given a list of worksheet names I can create hyperlinks to those worksheets (let's say specifically Cell P2) using the following code: ws.Activate For row = tRow To bRow ws.Cells(row, col).Select With Selection t2D = Selection.Value ' Note that the Cell value is the WS Name subA = "'" & t2D & "'!P2" .ClearContents ws.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:=subA, TextToDisplay:=t2D End With Next I can also scroll a worksheet to exactly the right cell using: Sub scroll2Column(wsName) Sheets(wsName).Activate ActiveWindow.ScrollColumn = 16 ActiveSheet.Range("P2").Select End Sub I would like to combine these two concpets and create a hyperlink which doesn't just refer to the cell but to the subroutine which brings the user to the cell and then conviently scrolls the worksheet as well. Despite many attempts at this, I have not gotten that to work. Does anyone know how this might be done? Thanks in advance, Chris Tauss ) |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using Excel Hyperlinks to refer to a Subroutine rather than a
hi, guys !
copy/paste (or type)... the following in ThisWorkbook code module... Dim fromHyperlink As Boolean Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range) fromHyperlink = Target.Hyperlinks.Count If fromHyperlink Then ActiveWindow.ScrollRow = ActiveCell.Row ActiveWindow.ScrollColumn = ActiveCell.Column fromHyperlink = False End If End Sub hth, hector. Chip Pearson wrote in message ... If the hyperlink is on one worksheet and it points to a cell on another sheet, Excel will scroll to the destination sheet when the user clicks on the link. For example, with Sheet1 active, use the code I provided and change the reference from "Sheet Three" to any worksheet other than Sheet1. When click on the link on Sheet1, Excel will take you to the worksheet that is the target of the link. Cordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group Pearson Software Consulting, LLC www.cpearson.com (email on web site) On Wed, 15 Oct 2008 08:29:02 -0700, ct60 wrote: Thanks Chip - That will bring the user to the cell -- but not actually scroll the WS. Ideally, I would like to add the scrolling aspect so I think I need to refer to a subroutine rather than a cell ref. I am just wondering if this is possible. Best Regards, Chris "Chip Pearson" wrote: Try some code like the following: Sub AAA() Dim R As Range Dim HL As Hyperlink Set R = Range("C3") Set HL = R.Hyperlinks.Add(anchor:=R, _ Address:=vbNullString, _ SubAddress:="'Sheet Three'!K128", _ TextToDisplay:="click for K28") End Sub Cordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group Pearson Software Consulting, LLC www.cpearson.com (email on web site) On Wed, 15 Oct 2008 07:25:01 -0700, ct60 wrote: Hello Again - I have found that given a list of worksheet names I can create hyperlinks to those worksheets (let's say specifically Cell P2) using the following code: ws.Activate For row = tRow To bRow ws.Cells(row, col).Select With Selection t2D = Selection.Value ' Note that the Cell value is the WS Name subA = "'" & t2D & "'!P2" .ClearContents ws.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:=subA, TextToDisplay:=t2D End With Next I can also scroll a worksheet to exactly the right cell using: Sub scroll2Column(wsName) Sheets(wsName).Activate ActiveWindow.ScrollColumn = 16 ActiveSheet.Range("P2").Select End Sub I would like to combine these two concpets and create a hyperlink which doesn't just refer to the cell but to the subroutine which brings the user to the cell and then conviently scrolls the worksheet as well. Despite many attempts at this, I have not gotten that to work. Does anyone know how this might be done? Thanks in advance, Chris Tauss ) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Call a subroutine using variable subroutine name | Excel Discussion (Misc queries) | |||
Help! Can't refer to a subroutine on a worksheet | Excel Programming | |||
sql that will refer to a cell in excel | Excel Programming | |||
Excel Hyperlinks- cell content v. hyperlinks | Excel Discussion (Misc queries) | |||
How do I refer to the tab name in a cell formula in Excel? | Excel Discussion (Misc queries) |