Have macro run automatically

Question

Hi,

I've been trying to have a macro run automatically whenever the file is opened and am running into issues. I've been able to save the macro as an add-in and add it to my quick access toolbar for easy execution, but I'd like to distribute to my large team (many of whom are add-in adverse), so I'd like to distribute just as an Excel file they can click on which will execute the macro on whatever workbook they're working on.

It's a fairly basic 'Batch Open" macro

Sub BatchOpenHyperLinks_SelectedRanges()
Dim objSelectedRange As Excel.Range
Dim objHyperlink As Excel.Hyperlink

'Get selected ranges  
Set objSelectedRange = Excel.Application.Selection  
For Each objHyperlink In objSelectedRange.Hyperlinks  
    objHyperlink.Follow  
Next  

End Sub

So when the user has a range of cell selected in their workbook that contain links to webpages, I want them to be able to click on the "Batch Open" file, and automatically execute the macro within without it resulting in a second workbook opening. Just run the macro to open the links. I can't seem to get it to work.

I've tried doing:

Sub Auto_Run()

'file name here'

End Sub

The 'file name' is the macro saved as an .xlam add-in but it doesn't work. Not sure what I'm doing wrong but hoping someone can help.

Thanks in advance

Answer 1

If you want to use your code as build-in excel addin, you can only share it to use as xlam file.
Then autorun procedure is not nesesery when you want to use it any time (not first after run).
In your code I can't find references to activeworkbook to be sure that change 'll do on right instance (many opened excel's files and selection commend can be insufficient).
But VBA code is not all your concern.

Anyway, You should add to this add-in menu structure in XML to get icon to run.
You can start from "Custom UI Editor For Microsoft Office" app. That is a free MS application to add code to ziped excel files.
I build add-in, with MS icons to use selected picture by names in your ribbon.

Regards