Macros and variable Hyperlinks

[Jenny_O]

Hi There,

Very new to all this and first ever forum post so take it easy on me if this is not in the right location or explained perfectly etc.

I am trying to create a macro that is linked to a picture. When the picture is clicked I need it to reference a cell in excel and follow the hyperlink. This hyperlink changes based on a vlookup formula.

For Example

In Sheet1 I have a drop down menu located in cell B9 (Cell name =SelectedNewCar), based on what is selected the formula in cell A27 looks up a hyperlink in a table in sheet2 (The table range is= NewCarDetails) the formula is =HYPERLINK(VLOOKUP(SelectedNewCar,NewCarDetails,10,FALSE))

I have tried creating a macro with the following:-

Range("A27").Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True

I have tried a few variations on this but gett runtime error 9 or I get a subscription out of range error.

I have trawled through various posts online but can't seem to find a suitable solution.

Is anyone able to assist in this area?

Thank You

[abousetta]

Hello and welcome to the forum,

Maybe try something like this (untested):

Please Login or Register  to view this content.

[Jenny_O]

Hello and welcome to the forum,

Maybe try something like this (untested):

Please Login or Register  to view this content.

I get the following error:-

Object doesn't support this property or Method.

Any other suggestions?

[abousetta]

Could you upload a sample workbook? The data can be dummy but the structure needs to be the same as the original.

[abousetta]

Sorry, it should be ActiveWorkbook instead of ActiveSheet. Try that and see if fixes your problem.

[Jenny_O]

Now I get a mismatched error. I've attached a file, the cell references are a little different to my first post and the data is just made up but the concept is the same.

In cell B2 is a drop down menu that when selected can change the outcome of cell A10 based on the formula in that cell. I have assigned the macro to the picture and when the picture clicked I want the URL to open.

Maybe I'm entering the info in VB wrong.
Thanks for the help on this.

[abousetta]

You're fine, but need to tell Excel that the word you provided is a named range so something like Range(NamedRange)

This works fine for me:

Please Login or Register  to view this content.

[Jenny_O]

Awesome thanks for that, it works perfectly for what I need.