User Definded Function in Worksheet_Change Event

[Gregorys05]

Hi All,
I am trying to get the below code to add a formula to cell after i type a value in, for example.
i type 140 in Cell B2 and the code the puts the formula =Calctime(140) in B2.

I have the code for the FormulaBelow) but i keep getting the error 1004

Also When i just Hit end on the debug the formula is in the right cell with the right value
Any Ideas??

Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Integer
Dim Test As String
Dim T As Variant
If Target.Cells.Count > 1 Then Exit Sub
i = Target.Column
If Intersect(ActiveCell, Range("A1:J13")) Is Nothing Then
        Exit Sub
 
    Else
 
Test = Target.Value
Target.Offset(0, 0).FormulaR1C1 = "=CalcTime(" & Test & ")"
 
 
End If
 
End Sub

Function CalcTime(Seconds) As String
    Dim tData1, tData2
    If Not IsNumeric(Seconds) Then Exit Function
    If Seconds < 60 Then
        If Len(Seconds) = 1 Then
            CalcTime = "00:0" & Seconds
        Else
            CalcTime = "00:" & Seconds
        End If
    Else
        tData1 = RoundDown(Seconds / 60)
        tData2 = Seconds - tData1 * 60
 
        If Len(tData1) = 1 Then
            tData1 = "0" & tData1
        End If
 
        If Len(tData2) = 1 Then
            tData2 = "0" & tData2
        End If
 
        CalcTime = tData1 & ":" & tData2
 
    End If
End Function
Function RoundDown(Number)
    Dim tData1
    tData1 = InStr(Number, ".")
    If tData1 = 0 Then
        RoundDown = Number
    Else
        RoundDown = Left(Number, tData1 - 1)
    End If
End Function

 

[VoG]

Try Formula instead of FormulaR1C1

 

[Andrew Poulsom]

I think you need:

Application.EnableEvents = False

at the begiining of your code, and:

Application.EnableEvents = True

at the end.

 

[Gregorys05]

HI VoG,
I still get the same error and it now puts 0 into the calctime value instead of 140??

 

[Gregorys05]

Combo of the two sorted it out... thanks guys

 

[Latchmaker]

How can I have multiple Private Sub Worksheet_Change events...I am getting an ambiguious name error if I list them seperately

 

[RoryA]

You can't - you have to incorporate all the code into one routine (or call separate routines from the Change event)

 

[Ruddles]

You can't have more than one Worksheet_Change subroutine per worksheet. Any code that you want to execute on Worksheet_Change has to be placed inside that one subroutine.

 

[Latchmaker]

How do I combine these routines then into one code


Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
Set rng = Target.Parent.Range("B:B")
If Target.Count > 1 Then Exit Sub
If Intersect(Target, rng) Is Nothing Then Exit Sub
If Target.Value >= 0 Then Target.Offset(0, -1).Value = Now()


End Sub
Private Sub Worksheet_Change(ByVal Target As Excel.Range)

ActiveWorkbook.Save

End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
Set rng = Target.Parent.Range("H:H")
If Target.Count > 1 Then Exit Sub
If Intersect(Target, rng) Is Nothing Then Exit Sub
If Target.Value >= 0 Then Target.Offset(0, 4).Value = Now()

End Sub

 

[Andrew Poulsom]

One way:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rng As Range
    Set rng = Target.Parent.Range("B:B")
    If Target.Count > 1 Then Exit Sub
    If Not Intersect(Target, rng) Is Nothing Then
        If Target.Value >= 0 Then Target.Offset(0, -1).Value = Now()
    End If
    Set rng = Target.Parent.Range("H:H")
    If Not Intersect(Target, rng) Is Nothing Then
        If Target.Value >= 0 Then Target.Offset(0, -1).Value = Now()
    End If
    ActiveWorkbook.Save
End Sub