Autorun Macro from a range of cells
Posted by Richard Winfield on January 04, 2002 11:58 AM
I have what is probably a very simple question, but I seem to be having troubvle finding the answer. I have seen posted here a few months ago a bit of VBA that would automatically run a macro when a change was made to a cell on a spreadsheet. I have a number of woorkbooks that have somewhere in the range of 30 lines of hard coded data, and then anywhere from 5-15 optional items. What I would like to be able to do is to call a macro when a value is entered into ANY of these optional items. The range for example may be C35:C50. At the opening of the woorkbook those cells are empty. If the user enters a value into any of those cells I would like to be able to have an existing macro run each time. At the same time if for example 2 options are selected I would like to have the macro run each time. Thanks in advance for any help :)
Posted by Gary Bailey on January 04, 2002 1:05 PM
Try using the following code in the code module behind the sheet in question. It runs everytime something non-blank is entered into C35:C50.
It runs once for each value entered into C35:C50. Say if you simultaneously enter something into C35 and C36 it runs twice, for example.
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim rngCell As Range, rngIntersect As Range
On Error Resume Next
Set rngIntersect = Intersect(Target, Range("c35:c50"))
If rngIntersect Is Nothing Then
Exit Sub
End If
For Each rngCell In rngIntersect.Cells
If rngCell.Value <> "" Then
MsgBox "Your macro"
End If
Next rngCell
End Sub
Gary
