Running macro depending on value in cell

Hi IrishBob

Welcome to the board.

Since 97 Excel has had Events. There are basically 2 kinds, Workbook Events and Sheet Events.

The quickest way to get to Excels Workbook Events is to right click on the sheet picture (top left next to "File") and select "View Code". Then choose an event from the "Procedure" drop down list box. For Excel 2000 + you will need to select "Workbook" first from the "Object" drop down list box first.

The quickest way to get to Excels Sheet Events is to right click on the sheet name tab and select "View Code". Then choose an event from the "Procedure" drop down list box. For Excel 2000 + you will need to select "Worksheet" first from the "Object" drop down list box first.

Now, for your needs you could use some code like this:

Private Sub Worksheet_Change(ByVal Target As Range)


    If Target.Address = "$A$1" Then
      If IsNumeric(Target) Then
       If Target.Value < 50 Then
          Run "MyMacro"
       End If
     End If
    End If


End Sub

This will fire only when Cell A1 contains a number and is below 50.

In regards to Excel VBA books, you find many Here with comments about their content.

Hope this helps

IrishBob,

I'm fairly new to VBA too, so I'll let the older hands recommend a book (I would also be interested). But as for the question, you can enter VBA code on a worksheet which will automatically activate when something on the worksheet changes; a simple if statement can then check to see if the appropriate cell is >50.

Open the Visual Basic Editor and in the Project section double-click on the appropriate sheet. In the code window, select "Worksheet" from the left drop down and "Change" from the right drop down. This will enter the start and finish bits for the code. then try something like this in between:

If Range("A1").Value > 50 Then
MsgBox "Greater than 50"
End If

You then either insert the code you want instead of the MsgBox above, or call it as another procedure.

Hope this helps.

DuckBill

Dave,

Looks like you beat me to it! (I thought you guys down under would be asleep at this time of day!)

BTW, am I right in thinking that the Target bit in your code means that the code only looks at changes in the target and nothing else? What other advantages are there in using targets.

DuckBill

Generally I've found that most things run from event macros can also be run using a minimum of code and formulas, so it really depends what you're trying to do. In the main, a well-constructed spreadsheet may cut down the amount of code you have to attach to event macros, and thus the margin of error.

Quote

Originally posted by DuckBill
Dave,

Looks like you beat me to it! (I thought you guys down under would be asleep at this time of day!)

BTW, am I right in thinking that the Target bit in your code means that the code only looks at changes in the target and nothing else? What other advantages are there in using targets.

DuckBill

Display More

Hi Bill

I should have been in bed, or at least watching TV :yawn:

Target is a predefined Range variable that represents th cell that causes the Change Event to fire. Using it prevents the Event from firing, until needed.

It has all the Properties and Methods that any other Range Object has.

Thanks Dave, very useful.

DuckBill