MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Making a macro run automatically


Posted by Andrew on July 20, 2001 1:29 AM

Is it possible to make a macro run all the time, in the same manner as, for exmple, the conditional formatting tool works. So when you change values in cells it automatically rechecks every cell as opposed to having to click a button every time you want to run the macro.
Thanks
Andrew


Posted by Dax on July 20, 2001 5:35 AM

One way you can do this is to use the Worksheet_Change event. Try something like this:-

On your worksheet, right click the tab and click View Code. From the left hand combo box pick Worksheet and from the right hand combo box choose Change. This will create an empty procedure that will be carried out every time a change is made to any cell. For example, say you wanted to check that any cell value in column B to make sure that it was lower than the cell in column A (same row) you'd use something like this:-

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Column <> 2 Then Exit Sub
If Target.Value > Target.Offset(0, -1).Value Then
Application.Undo
MsgBox "Value in column B must be lower than value in column A", , "Invalid Entry"
End If
End Sub


This gives you some idea of what you can do, the opportunities are endless!

Regards,
Dax.

Posted by Andrew on July 20, 2001 1:05 PM

Thanks, I'll have a look.