Assign hyperlink to existing shape based on current column

[dunthank]

Hello,

I have searched for a long time but have not found a way to do this, either in Excel proper or with VBA. I need a way to construct a hyperlink address dynamically in real time and assign that link to a shape on the worksheet.

I have a workbook with links to specific (dated) columns on category detail sheets in the workbook. I have a "Summary Page" button on each of the category worksheets. I need to automatically attach a hyperlink, with the address of the current column and row 4 on the Summary Page, to the button on the category page.

As an example, a hyperlink on the Summary Page in Cell S15 links to Vendor Payments Due sheet, Cell S5 (a totals cell). On the page is a shape button. I need the hyperlink in the shape button to return to Summary Page!S4 in the example.

I haven't found a solution yet - any help?

TIA

 

[pbornemeier]

I believe this will give you a near solution:

Option Explicit

Sub AddConditionalHyperlink()
    
    Dim sAddress As String
    Dim sColumn As String
    Dim sSubAddress As String
    Dim shp As Shape
    
    Set shp = ActiveSheet.Shapes("Rectangle 2")   'Define a shape
    sAddress = shp.TopLeftCell.Address   
    sColumn = Split(Range(sAddress).Address(, , xlR1C1), "C")(1)    'Get column number of top left cell of shape
    sSubAddress = "Summary!" & Cells(4, CLng(sColumn)).Address(0, 0)    'for current column and row 4
    ActiveSheet.Hyperlinks.Add Anchor:=shp, Address:="", SubAddress:=sSubAddress

End Sub