How to pass the hyperlink string from one workbook to another

Question

I have two workbooks "UI.xlsm" and "Gas_Insulated_MV_Circuit.xlsm", in the first workbook (UI.xlsm) i ave maintained a list of hyperlinks in a particular column, on click of the link it will take me to a respective other workbooks.

The task is to capture the text of the clicked hyperlink from "UI.xlsm" workbook and i have to use the same value in "Gas_Insulated_MV_Circuit.xlsm"

Below is the code from UI.xlsm workbook where I am capturing the clicked hyperlink cell text and storing it as string

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
    Dim BR as String
    BR = Target.Range.Text  'Here i'm storing the clicked cell text 
End Sub

Now i need to use the BR string value in another workbook "Gas_Insulated_MV_Circuit.xlsm"

Public BR as String   ' I have declared BR as a public variable
  Private Sub CommandButton27_Click()
  Dim WbUI as Workbook
  Dim WsUI as Worksheet
  Dim File1 as string


  Set WbUI = Workbooks ("UI.xlsm")
  Set WsUI = WbUI.Sheets("Sheet1")

  File1 = BR ' I want something like this, where i can assign BR value from                        
                UI.xlsm workbook to File 1 of the current workbook

End Sub

Could anyone kindly help me in achieving it.

Unknown Unknown · Accepted Answer · 2015-12-14T04:30:28

I created a defined name (e.g. myBR) in the UI.xlsm workbook with =Sheet1!A1 as the Refers to:. With that defined name 'seeded', I changed your Worksheet.FollowHyperlink event macro to,

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
        ThisWorkbook.Names("myBR").RefersToR1C1 = _
          Chr(61) & Chr(34) & Target.Range.Text & Chr(34)
End Sub

In the Gas_Insulated_MV_Circuit.xlsm workbook, I can retrieve the most recently clicked hyperlink's cell text (i.e. TextToDisplay property) with,

=UI.xlsm!myBR

With both workbooks open, the cell value changes were immediate. Assignment to a string or integer variable in VBA should be no problem.

How to pass the hyperlink string from one workbook to another

1 Answers