Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Create button/icon that activates a changing hyperlink

0

Dear Sir!

I have a working hyperlink, but need a button instead because cells are being locked.
I have this fomula: =HYPERLINK(VLOOKUP(B2;Members!D3:J31;7;FALSE);"MAP") in cell E3. In the cell E3 it shows the word "MAP" as formula says.
B2 cell shows name of member picked from a combo box with a drop down menu of members.
"Members!D3:J31;7" refers to another sheet in the same workbook called "Members". Using Vlookup it finds the link to the home of the picked member. The link can be f.ex. like this ;https://www.google.no/maps/place/59%...!3m1!1s0x0:0x0
Each member have his own Google Map link in the sheet "Members".

So, when I click the word "MAP" in cell E3, it activates the link to Google Maps and opens Google Maps in the browser on the given coordinates in the link. Also after changing to another member. It all works well.

But, when other users are going to use this workbook, a number of cells will be locked to protect the formulas. The workbook containes many calculations about the same subject; time, speed, points and distances.
The cell E3 must also be locked to protect the formula from being deleted/changed by accident. But when the cell E3 is locked, clicking the word "MAP" of course does not work anymore.

The problem:
I would like to have a button looking like a Google Map ikon and does the same as when I click the word "MAP". So the user click the Google Map ikon and Google Maps opens in his browser on the given coordinates in the link above. This button/ikon will still be active/unlocked after cells are locked for protection.
How can this be done?? I would appreciate any help in this. Thank you.

excelmacros-vba
posted by: Roger_ on Sat Jul 2, 2016 at 6:38 am
Answer
Discuss

Answers

0
Selected Answer

The only step that might be an issue is getting the actual URL to open as this has proven to be annoying depending on the situation.

This little piece of code that I found online and have used before seems to work pretty well for sending the user to the URL:

Sub VisitWebsite()

Dim ie As Object

Set ie = CreateObject("INTERNETEXPLORER.APPLICATION")

ie.NAVIGATE "http://www.google.com"

ie.Visible = True

While ie.busy
 DoEvents
Wend

End Sub

It will open in internet explorer. Replace http://www.google.com with the desired website and see if that works. The final macro would just have that string of text replaced with a variable.

To get the correct hyperlink put this into the macro:

hyperlink_value = Application.WorksheetFunction.VLOOKUP(Range("B2"),Sheets("Members").Range("D3:J31"),7,FALSE)

That should translate your formula into the macro and then just replace the url in the first example with hyperlink_value (without the quotation marks).

If following the link in the first example worked on your system, you could use this as the final macro:

Sub VisitWebsite()

Dim ie As Object

hyperlink_value = Application.WorksheetFunction.VLookup(Range("B2"), Sheets("Members").Range("D3:J31"), 7, False)

Set ie = CreateObject("INTERNETEXPLORER.APPLICATION")

ie.NAVIGATE hyperlink_value

ie.Visible = True

While ie.busy
 DoEvents
Wend

End Sub

Then just create a button in the worksheet and attach that button to the macro. (Attach Macro to a Button)

Try this out and let me know how it works.

(also your link got a bit messed-up but its still there and it worked when I tested this on my system)

posted by: don (rep: 1989) on Sat Jul 2, 2016 at 1:10 pm
Discuss

Discussion

Yesss! This work like an oiled machine. I have been trying to find answer on this for more than half a year and asked other forums, but no solution. And in this awesome forum an answer is produced in a couple of hours. I am truly impressed! Thank You for the time and effort you put in this. :)
Roger_ Jul 2, '16 at 3:45 pm
Glad to help! :)
don (rep: 1989) Jul 3, '16 at 1:15 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login