hyperlink function and macro

**hthom25** · 09-05-2010, 10:30 PM

Hi,

I am new here so I apologize in advance if I am not posting or explaining this properly. I have scoured the web and cannot find the answer so hopefully someone here can help...

I am using the =hyperlink function as follows...

Please Login or Register  to view this content.

Now the "Click Here" shows up fine and the link works when I click it manually...

But I tried to get a macro to automatically click it....

Please Login or Register  to view this content.

And the code only works if it is an actual typed in hyperlink and not the hyperlink function using the formula...

Hope I explained that right... any thoughts? Questions?

Thanks,

Henry

**Leith Ross** · 09-06-2010, 01:34 AM

Hello Henry,

Welcome to the Forum!

As you have discovered, not all Hyperlinks are created equal. They both produce the same results, but internally they differ in how those results are accomplished. The Hyperlink protocol only describes the Hyperlink's behaviour, not how it has to accomplish it. So, Excel does it one way and VBA does it another way, and browsers do it still in another fashion, but the end results are the same. They just don't play well with others.

**hthom25** · 09-06-2010, 02:17 AM

Thank you for the welcome...

So I guess my question is...

Is there a way to make them place nice together? I would like to use the macro to open multiple hyperlinks... the problem is the macro doesn't recognize excels formula syntax as an actual hyperlink?

Is that fair to say?

So can I run a script in the macro or change the hyperlink to a syntax that VBA recognizes?

Thanks,

Henry

**DonkeyOte** · 09-06-2010, 03:20 AM

You could iterate the formulae within the selection and test for HYPERLINK in that manner
(splitting the formula to extract the reference, building the hyperlink & following [then removing])

How viable that will be will largely depend on the consistency of the formulae themselves (eg embedded functions etc)

A very basic example assuming non-embedded function calls, valid (& basic) links etc...
(ie =HYPERLINK() rather than =IF(...,IF(...,HYPERLINK(...),""),"")

Please Login or Register  to view this content.

I've no doubt there's a better method...

**hthom25** · 09-06-2010, 03:25 AM

Thank you for the response DonkeyOte, but I just figured it out...

Thank you to Leith Ross... your post helped me understand the issue and rethink my solution...

Here is what I did... not sure if this would be considered "clean code" but it works for me...

Please Login or Register  to view this content.

Basically Leith Ross helped me realize that I needed VBA to see it as a valid hyperlink.

Thoughts?

Thanks,

Henry

**DonkeyOte** · 09-06-2010, 03:30 AM

If you need to Follow the hyperlink then genuine links is best approach.

FWIW, I noticed that the code I provided would not have worked for your samples (concatenation) and the strLink line would need to be revised

Please Login or Register  to view this content.

also as you have demonstrated there's no real need to add the links to the cells as I was doing

**justinep** · 12-20-2013, 05:21 PM

How would this code need to be modified to reference a sheet within the workbook?

hyperlink function and macro

LinkBack

Thread Tools

Rate This Thread

Display

hyperlink function and macro

Re: hyperlink function and macro

Re: hyperlink function and macro

Re: hyperlink function and macro

Re: hyperlink function and macro

Re: hyperlink function and macro

Re: hyperlink function and macro

Thread Information

Users Browsing this Thread

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions