Set Formula For Cell Range Through VBA

[Azurulean]

I have a formula that I would like to input into a range of cells through VBA. This is because I don't want the users to accidentally delete it if I simply enter it into the formula bar. Locking the cells and protecting the worksheet doesn't seem to be an option because it prohibits sorting unless the cells to be sorted are all unlocked, thereby exposing the formula.

When I ran the Macro Recorder, it generated the coding below. How should it be modified so that =IF(E11="","",SUM(E11:I11)) is entered into J11, =IF(E12="","",SUM(E12:I12)) is entered into J12, etc. for the range J11 through J45?

ActiveCell.FormulaR1C1 = "=IF(RC[-5]="""","""",SUM(RC[-5]:RC[-1]))"
Selection.AutoFill Destination:=Range("J11:J45"), Type:=xlFillDefault

Thanks in advance for any and all assistance.

[ragulduy]

Range("J11:J45") = "=IF(E11="""","""",SUM(E11:I11))"

Edit: Have you tried ticking the "allow all users to sort" option when protecting the worksheet?

[Azurulean]

Thanks, that's what I needed.

I have tried ticking the "Sort" option, but because cells J11:J45 are locked, I get the error message:

"The cell or chart that you are trying to change is protected and therefore read-only.
To Modify a protected cell or chart, first remove protection using the Unprotect Sheet command (Review tab, Changes group). You may be prompted for a password."

If I set cells J11:J45 as unlocked, I am able to sort when the sheet is protected, but the formulas can then be accidentally deleted. Do you know any ways around this?

[ragulduy]

Ah of course, yes VBA is probably the solution then (I have seen workarounds using allow edit ranges and selecting locked cells but it looks a bit messy.

Put something like:

Private Sub Worksheet_Change(ByVal target as Range)
Application.EnableEvents = False
If not intersect(target,Range("J11:J45")) is nothing then Range("J11:J45") = "=IF(E11="""","""",SUM(E11:I11))"
Application.EnableEvents = True
End Sub

In the worksheet object module.

[Azurulean]

Thanks so much. I'd found some of those workarounds on the web, but couldn't figure them out. In fact, I've really only just begun tampering with VBA. Can you tell me what the difference is between slotting in your code into my existing one and creating/running a new worksheet object module? Or whether using either one of the codes you posted may be better since when I tried them out, they both produced the same result?

This has your second code slotted in. When I swap the three lines with the one-liner from your first post, it produced the same result only with a slight delay in the other calculations.

Private Sub Worksheet_Change(ByVal target As Range)
    Dim rInt        As Range
    Dim cell        As Range
    
    Application.EnableEvents = False
    If Not Intersect(target, Range("J11:J45")) Is Nothing Then Range("J11:J45") = "=IF(E11="""","""",SUM(E11:I11))"
    Application.EnableEvents = True

    Set rInt = Intersect(target, Range("E11:E45"))

    If Not rInt Is Nothing Then
        On Error GoTo Error
        Application.EnableEvents = False
        For Each cell In rInt
            If VarType(cell.Value2) = vbDouble Then
                cell.Value = cell.Value / 30 * 15
            End If
        Next cell
    End If
    
    Set rInt = Intersect(target, Range("F11:F45"))

    If Not rInt Is Nothing Then
        On Error GoTo Error
        Application.EnableEvents = False
        For Each cell In rInt
            If VarType(cell.Value2) = vbDouble Then
                cell.Value = cell.Value / 100 * 40
            End If
        Next cell
    End If
    
    Set rInt = Intersect(target, Range("G11:G45"))

    If Not rInt Is Nothing Then
        On Error GoTo Error
        Application.EnableEvents = False
        For Each cell In rInt
            If VarType(cell.Value2) = vbDouble Then
                cell.Value = cell.Value / 100 * 20
            End If
        Next cell
    End If
    
    Set rInt = Intersect(target, Range("H11:H45"))

    If Not rInt Is Nothing Then
        On Error GoTo Error
        Application.EnableEvents = False
        For Each cell In rInt
            If VarType(cell.Value2) = vbDouble Then
                cell.Value = cell.Value / 10
            End If
        Next cell
    End If
    
    Set rInt = Intersect(target, Range("I11:I45"))

    If Not rInt Is Nothing Then
        On Error GoTo Error
        Application.EnableEvents = False
        For Each cell In rInt
            If VarType(cell.Value2) = vbDouble Then
                cell.Value = cell.Value / 100 * 15
            End If
        Next cell
    End If
                
Error:
    Application.EnableEvents = True
End Sub

[ragulduy]

The first bit of code is just to set the formulas, I would expect it to be slightly quicker to check first if any of the formulas were changed (What the if not intersect is nothing bit does).

The application.enableevents part is to stop the code getting stuck in a loop, as when you change the formulas it will re-run the worksheet_change event.

You can only have one worksheet_change() event per worksheet (and one worksheet object module). The worksheet object module is what you have the code in currently so you need to incorporate it as you have done.

[Azurulean]

Thank you for the insight. Out of curiosity, is there any sort of coding etiquette regarding how it should ideally be incorporated or is it fine where I have it?

[ragulduy]

Where it is would be ok. It is only if the different things within the worksheet_change event could affect each other that you have to be careful with the location.

[Azurulean]

You are a saint. Thanks so very much for all the help you've given me!