Excel & Hyperlinks & Buttons with variables in link

[C0RR0SIVE]

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:


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

 

[PP Mguire]

Oh man, this is beyond my spreadsheeting capabilities lol.

 

[Joe C]

When I worked for a company called Game Tech, We did inventory on the devices we provided with excel. But that is as far as my excel experience went.

 

[Trotter]

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).

 

[C0RR0SIVE]

Holy crap this has been a long week!

Finally figured out how to do this...

Simple explination on how to do this.

On the Settings sheet, I have a few Columins. First column has the URL with out the variable. In this instance, A15 will contain https://WEBSITE.Web/employeeid=

Cell B1 to B99999 will contain a formula that gets the employee ID from another sheet, for instance 123456

Cell C15 has the formula =((A15)&(B15))

In C15 the formula will generate https://WEBSITE.Web/employeeid=123456


In the VBA, we have the following code

Sub ShapeHyperLink()

'Set our variable
Dim q As Variant

'Obtain cell value and store it in the variable
q = ThisWorkbook.Worksheets("Settings").Range("C15").Value

'Call just the variable that was stored
ActiveWorkbook.FollowHyperlink q

End Sub

Assign the ShapeHyperlink as a Macro to the shape, and BOOM! IT WORKS! Clicking on the shape icon, and it opens the default browser of a system and takes you straight to the generated URL.