VBA custom data validation formula

[ShoYnn]

I am having some issues with a piece of code. The whole code is working great with the exception of the Custom Data Validation formula I am trying to add.

Explanation of code: the code adds a job to a calendar, then in the first cell of the added job it creates a hyperlink via a formula and not the hyperlink function. I went this route as my hyperlinks needed to be dynamic, so I built a table that the hyperlink uses a Vlookup to go to the right place. Since I went this route, I have to have the cell containing the hyperlink unlocked. Because of this, I am using the exact same formula in the cell for the custom data validation formula to ensure people don't accidentally change or delete it. The goofy thing is that when I add the data validation manually everything works as planned, but if I try to use VBA to do it for me it doesn't work.

Since the hyperlink formula will change based on where it gets added to the calendar, I have a string variable that is the exact same formula that was created. I am then trying to use this variable for the data validation and get yelled at (error 1004).

Sample of the hyperlink formula:

=Hyperlink("#"&VLOOKUP(J61,$AM$2:$AN$300,2,0),J61)

In this example, the hyperlink formula would be added to J60, J61 is the second day of the job and is being used for the Job Name, and AM:AN is the table that contains the job names and target cells for the hyperlink to follow.

VBA:
Dim valForm as String
valForm = Range("J60").Formula
Range("J60").Select
With Selection.Validation
.Delete
.Add Type:=xlValidateCustom, AlerStyle:=xlValidAlertStop, Formula1:=valForm

And it goes on through the rest of data validation commands. When the 1004 error occurs and I click on debug it highlights the .Add line. Thanks for any help you can give!

Also, if there is a more simple method to prevent people from accidentally changing/deleting a formula in an unlocked cell I am all ears, the only stipulations are that it needs to be something that can be automated, and in the event that the job is deleted (through the use of VBA) whatever method is used can be removed as to not cause errors.

 

[ShoYnn]

Ok, I figured out a solution. It is a bit unorthodox and silly, but it works, and there is no data validation or other silly things to make it work. This allows a hyperlink created by a formula to be followed if the cell it is in is locked.

First, write some VBA that trigger Before Double Click that unprotects the sheet that contains the hyperlinks. Then write another VBA that triggers when this very same sheet is activated that protects the sheet again.

The downside, you have to click like 4 times to follow the hyperlink, but the upside is that you minimize the risk of anyone accidentally editing or deleting the hyperlink.