Alright, so with those conditions, it was much trickier. I ended up implementing a userform, and a Worksheet_SelectionChange event. There isn't actually a lot of code, its just spread out. Here's the different sections of code:
First of all, I created a button that I placed on Sheet1 named "New Project Link" and clicking it starts the process of adding a hyperlink to a project.
That button has the following code:
Basically that code asks the user if the cell he has currently selected is the intended target. If so, it runs the UpdateLink function. If not, it sets the public variable CellSelection to true (used by the Worksheet_SelectionChange event)
Let's pretend the current cell is not the intended target. Now the user has to click the intended cell, which prompts the Worksheet_SelectionChange event:
That code only runs if the public variable CellSelection is set to True. After running, it returns CellSelection to false, and then runs the UpdateLink function:
That function checks if the selected cell is valid. In order for the selected cell to be valid, only 1 cell may be selected (if multiple are selected it returns an error) and the selected cell must be in column A (otherwise it returns an error). If the selected cell is valid, it launches the userform.
The userform is very basic. It has a drop-down box that contains the sheetnames in the workbook. User selects one of the sheetnames and clicks the OK button. That will input the hyperlink and copy over the cells 5D and 5E from the target worksheet and put them in column B and C cells (the cells next to the now-hyperlinked cell in Sheet1). Clicking the red X or the Cancel button closes the userform with no hyperlink update. The following is the code for the userform:
I've attached my test workbook in which this was created. Let me know if you have any questions, or if it needs to be adjusted.