Excel & Hyperlinks & Buttons with variables in link

c0rr0sive

asdf
Messages
9,185
Location
asdf
This is going to be a bit difficult, as I can't upload the file persay thanks to work requirements.

I have been working on a little project, can't go into explicit details, but I am trying to add what is a small feature, that has proven difficult at best.

In the file I have a few worksheets, Heatmap, Labor Board, Roster, Settings

The Labor Board, an associate gets their badge scanned into a cell, and all adjacent cells auto-populate with station ID's, names, and so on.

The Heatmap is a graphical layout of the building, that auto-populates using data from Labor Board, and pulls data from a few internal web-sites that displays the employee performance.

The roster is where all the employee data is stored, this is used to just get data.

The settings page is where things get fun!

Here we put into certain cells, a URL that has variables, based upon cell values. So a cell such as A3, will have a formula that will look something like the following.

="URL;https://website.internal/employees/time=" & B3 &""

Cell B3 gets its data from the time cell on Labor Board.

This works fine and dandy, the hyperlink turns into what is needed, and the macros that call that cell for pulling data in, storing and parsing it into another sheet works great.

However, what I am trying to do now, is insert a clear shape over each station listed in the heatmap, and have that button call the URL that gets generated on another sheet that is relevant to that cell, this should open the browser, or active browser, and take us to the URL that's generated.

From my understanding, the following should work

Sub Follow()
ThisWorkbook.FollowHyperlink (ThisWorkbook.Sheets("settings").Range("A3"))
End Sub

However, I get an error that I can't figure out.

"Run-Time Error '-2147221014 (800401ea)':
Cannot open the specified file.


I am not an Excel Guru, and a lot of what I know is from a little google and lots of trial and error, but this has me 100% stumped. The biggest issue is that we are referencing a cell value as part of the URL, so a lot of the hyperlink stuff in excel, like directly linking a button, or inserted image doesn't seem to work properly because of that.

Anyone have suggestions? Tips? Attached is an image, the blue box will eventually be clear, and would open up our browser to the website that links to the employee listed there. Just giving an example.

Heatmap:
testy.PNG

Settings Section: The red outlined areas are the variables, but having these variables seems to break things, or not open the browser correctly?
testy2.png
 
Last edited:

Trotter

Grandfather of Techist, ¯\_(ツ)_/¯
Staff member
Messages
33,147
Location
The South
I will try to have my wife look at this. She's an Excel wizard and lives and dies by it (she's an accounts payable accountant).
 
Top