CODEWITHSUNDEEP
excelvbahyperlink
Danny Papadopulos
Danny Papadopulos

Reputation: 465

WorkbookA macro to create hyperlink in WorkbookB to call a macro in WorkbookA without Worksheet_FollowHyperlink event

I have encountered a fun (I think) problem.

Now what I know/what I've tried:

1) If I was opening the same Excel Workbook each time I could just add the 'Follow Hyperlink' event: Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink) into the Worksheet where I would like my hyperlink to be ran,

however,

Since I open a new file each time it's not going to have this piece of code in the Worksheet so the 'Follow Hyperlink' even won't work.

2) Now to solve the problem in point "1" I know that I could probably create another macro, which would insert the Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink) code into the newly opened Excel Workbook i.e. a VBA code which writes a VBA code, however I don't think that's a very elegant way of solving this problem.

To sum up: I would like the (macro book) to insert a hyperlink into another workbook, and when that hyperlink would be clicked I would like it to run a macro from the 'macro book' WITHOUT using the Worksheet_FollowHyperlink event.

Any ideas? Thanks!

Upvotes: 0

Views: 141

Answers (1)

FaneDuru
FaneDuru

Reputation: 42236

Try using the next simple way of accomplishing what you need, in fact:

Private Sub AddSheetEventCallProc()
   'It needs a reference to 'Microsoft Visual Basic for Applications Extensibility x.x'
    Dim wb As Workbook, wProj As VBIDE.VBProject, wCom As VBIDE.VBComponent
    Dim wMod As VBIDE.CodeModule, Hrng As Range, LineI As Long

    Set wb = Workbooks.Add 'any existing workbook can be used here
    Set Hrng = wb.Worksheets(1).Range("A1") 'use here the cell you need
    'Add the hyperlink:
    Hrng.Hyperlinks.Add Anchor:=Hrng, _
            Address:="", SubAddress:=Hrng.Address, TextToDisplay:="Call Macro"
    With wb
        Set wProj = .VBProject
        Set wCom = wProj.VBComponents(Worksheets(1).codename)
        Set wMod = wCom.CodeModule
        With wMod
            On Error Resume Next
            LineI = .CreateEventProc("FollowHyperlink", "Worksheet"): LineI = LineI + 1
            .InsertLines LineI, "  If Target.Range.Address = """ & Hrng.Address & """ Then": LineI = LineI + 1
            .InsertLines LineI, "      Application.Run ""ADDINPRESA.xlam!testMsgbox""": LineI = LineI + 1
            .InsertLines LineI, "  End If"
            On Error GoTo 0
        End With
    End With
End Sub

The second (more elaborate) way will be to make your add-in create a new Ribbon Tab:

  1. Having the addin, it is good to create a new standard module naming it 'modRibbon', or similar. It is not mandatory, but it is good to separate the ribbon specific module from the one doing the add-in job. So, in this one where you must create the Subs able to be called by controls of the newly Tab to be created:

    Option Explicit

    Sub Test1(control As IRibbonControl) appTest1 'use here your Sub name End Sub

    Sub Test2(control As IRibbonControl) appTest2 End Sub

    Sub Test3(control As IRibbonControl) appTest3 End Sub

It is useful to copy this code in a Notepad window, in order to use it when the xml file will be created.

  1. Then, create the module where the Subs called by the above ones will exist:

    Sub appTest1() MsgBox "It's coming...", , "Test1" End Sub Sub appTest2() MsgBox "It's coming...", , "Test2" End Sub Sub appTest3() MsgBox "It's coming...", , "Test3" End Sub

  2. Now, you need a, so named, OfficeRibbonEditor application. I use OfficeRibbonXEditor downloaded from here. Please, downoad 'OfficeRibbonXEditor-NETFramework.zip'. After downloading it, extract the file from archive and just use it. No installation need. Now, run it and press Open button and browse for your add-in workbook and press OK. Select the open add-in and press 'Insert' menu. Select "Office 2010+ Custom UI Part" for Office 2010 or above. For 2007, select the other option. If you need the add-in to be compatible with both cases, both xml files will be inserted. I will show how to create a ribbon Tab only for first variant. Double click 'customUI14.xml`, which has been created and copy the next code. Now, it is assorted with the above subs, but, in order to make it call your specific subs, use the Notepad window where you copied the Subs name:

Now, close the add-in from Excel and press Validate Button. If you made a mistake in the xml code, the line with the mistake will be highlighted. If everything OK, press 'Save' button and open your add-in. It will create a new Ribbon Tab (TestTab) having three buttons....

  1. There are on the internet ways to find all imageMSO types. It is also possible to use your own icons. I did it some years before. I do not remember exactly how, but you need this option I can check my files archive.

For clarifying issues, a very good resource would be the Ron de Bruin site, which clarifies the way of doing such a Ribbon Tab, from more then ten years. You can find it here.

And the add-in does not 'import' the whole Ribbon. It only creates a new Tab adding it two the existing one and make it disappear when closed.

It is also good to know that when you modify the xml, all modifications done in the VBA add-in code will be lost and viceversa... That's why it is good to take care of closing the other one in the moment you modify something. If necessary, I can post a link to such an add-in done now, but, I would like to believe that it is easy to create one by yourself.

This is the way I create a new Ribbon Tab, but there are some other ways, too. When I will find some time, I will try doing it only in VBA. Practically, you have to read all subs you need to be called by your new Tab, then unzip the Excel file, create or modify the XML file in order to call your subs and rezip the file...

Upvotes: 0

Related Questions