Inputting Formulas into cells using VBA

I'd like to figure out a way to 'insert' a formula into a specific range of cells using VBA only when the Value of $A$7 is changed to a value that does NOT equal "Custom".

Heres what I have so far using Worksheet change event....

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Address = "$A$7" Then
        If Target.Value <> "Custom" Then
         Range("C13:C27").Formula = "=myfunction(A7)"
        End If
        End If
End Sub

As you can see above, I'd like to have '=myfunction(A7)' inputted as a forumla into some cells. Code above gets me into the second If statement, but none of the cells in my range update. Not certain I'm approaching it the right way. Any guidance is appreciated. Thanks. -FM

Foboman,

Remember to wrap your code next time.

Is the trigger a formula or a by the user changing A7. If it's a user change it works for me

Try adding this to the start

application.EnableEvents = false

and this to the end

application.EnableEvents = True

Also is the formula a custom User defined function ??

If so it might be worth posting that code

VBA Noob

Try this example

Event Macro Code

Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False
  If Target.Address = "$A$7" Then
        If Target.Value <> "Custom" Then
         Range("C13:C27").Formula = "=quicknetwork(A7)"
        End If
        End If
Application.ScreenUpdating = True
End Sub

Module Code

Function quicknetwork(quick1 As String)
Select Case LCase(quick1)
    Case "show all network space available", _
         "show all network space utilized", _
         "show all space available", _
         "show all space utilized"
          quicknetwork = "ALL"
    Case "Custom"
          quicknetwork = ""
    Case Else
         quicknetwork = "NONE"
    End Select
End Function

Amended - Or this shorter code

Function quicknetwork(quick1 As String)
Select Case True
        Case LCase(quick1) Like "*show all*"
        quicknetwork = "ALL"
        Case LCase(quick1) Like "*custom*"
        quicknetwork = ""
        Case Else
        quicknetwork = "NONE"
    End Select
End Function

ThisWorksheet Module To turn on events

Private Sub Workbook_Open()
    Application.ScreenUpdating = True
End Sub

VBA Noob

Why would you need VBA to do this? An IF formula would work.