Inserting or adding HyperLink to value in locked cell

Hey Folks,

I'm revamping our sample tracking log to be a little more user friendly, but it's not being friendly to me.

Using the sample sheet attached this is what I'm trying to do.

I have a range of cells in column H, "SRN Link" (H2:H7) formatted to take values from the corresponding rows in column A, "Lab Work Order #", because we want the value to be the same. I used the basic formula =$A2.

We would eventually add a hyperlink to the value in "SRN Link" from a folder we keep our SRN docs in on SharePoint.

I would like to be able to lock the range of cells in column H so that the formula isn't accidentally deleted by summer students, or someone not paying attention, but be able to add a hyperlink to the locked cell once we receive our SRN doc for that corresponding work order.

I know how to lock the cells, protect the sheet etc. I've tried Data Validation and some VBA code, but can't seem to figure it out. I've tried other ways to auto populate the values from column A to column H with no luck. Any ideas? Is this possible?

Attached is a sample worksheet, TIA!

Cheers,
EnviroTechSample Work Book - Lab Sample Tracking Log.xlsx

One VBA option would be to add an item to the right-click menu which would unprotect the sheet, show the hyperlink dialog and reprotect it.

So in a standard module is a routine that does that. The Option Private Module prevents it showing in the list of available macros.

Please Login or Register  to view this content.

Then, in the ThisWorkbook object add this code.
It executes before a right-click and adds a menu item to run the above code if you're on a single cell in H2:H7 of Sheet1

Please Login or Register  to view this content.

Thanks for the reply!

I was able to get that code to work, and was going to run with it. I tried to fill out the tracking sheet from the web version of excel and it didn't work. Some of our techs edit this sheet online, some in excel, so needed another solution.

I was able to use the formula =HYPERLINK(CONCATENATE("General File Path",$A2,"/",$B2,"/",$G2,"_SRN.pdf"),$G3). We re organized our folders on SharePoint and then assigned validated lists to columns to reflect those folders/file path needed. Now the link will just automatically generate without needing to manually insert the hyperlink.