AutoRun Macro Based on a Sum Calculation

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Jared J - 21 Feb 2006 20:23 GMT

I have recorded a few macros in Excel but I have very little expirence with
Visual Basic. I have written the following macro and it works great except
I would like to automatically run when the sum of "af1" gets below 10.

Sub Macro1()

Dim MyData As DataObject
Dim strClip As Integer

If Range("af1").Value > 9 Then
Exit Sub
Else
Range("af1").Select
Selection.Copy
Range("A4:C29,D1:I9,J1:AC3").Select

Set MyData = New DataObject
MyData.GetFromClipboard
strClip = MyData.GetText

Selection.Replace What:=strClip, Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Range("Af1,a1:c3").Select
With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
Range("af1).ClearContents

End If

End Sub

Reply to this Message

Jim Thomlinson - 21 Feb 2006 20:49 GMT

Place this code directly in the worksheet (right click the sheet tab and
select view code).

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range

On Error Resume Next
Set rng = Intersect(Target.Dependents, Range("A1F"))
On Error GoTo 0
If Not rng Is Nothing And _
Range("AF1") < 10 Then MsgBox "Call Macro 1"
End Sub

Signature

HTH...

Jim Thomlinson

> I have recorded a few macros in Excel but I have very little expirence with
> Visual Basic. I have written the following macro and it works great except
[quoted text clipped - 29 lines]
>
> End Sub

Reply to this Message

Jim Thomlinson - 21 Feb 2006 20:56 GMT

Sorry you should turn off the events while the code is running like this...

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range

on error goto ErrorHandler
application.enableevents = false

On Error Resume Next
Set rng = Intersect(Target.Dependents, Range("A1F"))
On Error GoTo 0
If Not rng Is Nothing And _
Range("AF1") < 10 Then MsgBox "Call Macro 1"
ErrorHandler:
application.enableevents = true
End Sub

Signature

HTH...

Jim Thomlinson

> Place this code directly in the worksheet (right click the sheet tab and
> select view code).
[quoted text clipped - 42 lines]
> >
> > End Sub

Reply to this Message

Jared J - 22 Feb 2006 17:29 GMT

Thanks Jim for the reply, but it does not seem to work like I envisioned it.
I have a sum formula in af1 and as cells in the range get deleted the sum in
af1 will decrease to a number between 1 & 9 and as soon as that happens I
would like excel to run Macro 1 automatcally. Once Macro 1 runs, it will
delete the sum formula in af1 so it does not continously run. I have looked
through alot of posts on this group and others but I can not seem to find
someone with the same issue. Maybe I am asking too much of excel.

Reply to this Message