Formula not working without selecting a cell reference

[stirlingmw]

Morning all

I am having trouble with 2 formulas that calculate range and bearing from latitudes and longitudes. Currently I have the formula in 2 cells on sheet1 (L38 - Range), L39 - Bearing).

L38 = IFERROR(distvincenty(signit(K26),signit(K27),signit(K33),signit(K34)),"--")
L39 = IFERROR(MROUND(MOD(DEGREES(ATAN2(COS(signit(K26)*PI()/180)*SIN(signit(K33)*PI()/180)-SIN(signit(K26)*PI()/180)*COS(signit(K33)*PI()/180)*COS(signit(K34)*PI()/180-signit(K27)*PI()/180),SIN(signit(K34)*PI()/180-signit(K27)*PI()/180)*COS(signit(K26)*PI()/180)))+360,360),1),"--")

K26 - Latitude 1, K27 Longitude 1, K33 - Latitude - 2 and K34 - Longitude 2
distvincenty - Public Function
signit - Function
ATAN2 - Public Function

The issue I am having is that to get these 2 formulas to function I have to unprotect the worksheet click into the formula, select a cell reference and then press return. I have to do this for both formula. Is there any way I can write these formula into a vba worksheet change function. Other people use this workbook and I need to have it password protected, but still need to the function to work.

thanks

Steve

 

[stirlingmw]

Think I have sorted it. Its not elegant coding, but it seems to be working.

If Not Intersect(Target, Range("K24,L24,M24,N24,K25,L25,M25,N25,K31,L31,M31,N31,K32,L32,M32,N32")) Is Nothing Then
Sheet1.Unprotect
    If Sheet1.Range("K24") <> "" And Sheet1.Range("L24") <> "" And Sheet1.Range("M24") <> "" And Sheet1.Range("N24") <> "" And _
    Sheet1.Range("K25") <> "" And Sheet1.Range("L25") <> "" And Sheet1.Range("M25") <> "" And Sheet1.Range("N25") <> "" And _
    Sheet1.Range("K31") <> "" And Sheet1.Range("L31") <> "" And Sheet1.Range("M31") <> "" And Sheet1.Range("N31") <> "" And _
    Sheet1.Range("K32") <> "" And Sheet1.Range("L32") <> "" And Sheet1.Range("M32") <> "" And Sheet1.Range("N32") <> "" Then
    Sheet1.Range("K26").Value = Sheet1.Range("K24").Value & "°" & Sheet1.Range("L24").Value & "'" & Sheet1.Range("M24").Value & Sheet1.Range("N24").Value
    Sheet1.Unprotect
    Sheet1.Range("K27").Value = Sheet1.Range("K25").Value & "°" & Sheet1.Range("L25").Value & "'" & Sheet1.Range("M25").Value & Sheet1.Range("N25").Value
    Sheet1.Unprotect
    Sheet1.Range("K33").Value = Sheet1.Range("K31").Value & "°" & Sheet1.Range("L31").Value & "'" & Sheet1.Range("M31").Value & Sheet1.Range("N31").Value
    Sheet1.Unprotect
    Sheet1.Range("K34").Value = Sheet1.Range("K32").Value & "°" & Sheet1.Range("L32").Value & "'" & Sheet1.Range("M32").Value & Sheet1.Range("N32").Value
    Sheet1.Unprotect
    Sheet1.Range("J37").Select
    ActiveCell.FormulaR1C1 = "=IFERROR(distvincenty(signit(R[-11]C[1]),signit(R[-10]C[1]),signit(R[-4]C[1]),signit(R[-3]C[1])),""--"")"
    Sheet1.Unprotect
    Sheet1.Range("K37").Select
    ActiveCell.FormulaR1C1 = _
        "=IFERROR(MROUND(MOD(DEGREES(ATAN2(COS(signit(R[-11]C)*PI()/180)*SIN(signit(R[-4]C)*PI()/180)-SIN(signit(R[-11]C)*PI()/180)*COS(signit(R[-4]C)*PI()/180)*COS(signit(R[-3]C)*PI()/180-signit(R[-10]C)*PI()/180),SIN(signit(R[-3]C)*PI()/180-signit(R[-10]C)*PI()/180)*COS(signit(R[-11]C)*PI()/180)))+360,360),1),""--"")"
    Range("K38").Select
        End If
End If
Sheet1.Protect

Basically the code checks to make sure that my deg, min and sec input boxes are not blank (wont fire if blank) it then concatenates the 2 latitudes and longitudes into cells K26, K27 (lat/long 1) and K33, K34 (lat/long 2), it then selects my "Range" cell (J37) and adds the correct formula and then does the same for the "Bearing" cell.

the only issue I have is that somewhere in the other code the sheet is protected, so i have to unprotect the sheet at each step. It doesnt visually slow the code down, so i am content to leave it as is for now.