Running macro depending on value in cell

  • Hi - I'm only new to macros and am still trying to figure them out, but I've hit a snag - I need to run a macro depending a the value of a cell. For example, if the value in the cell is less than 50, I need the macro to run, otherwise nothing happens. It's probably very easy but I can't figure it out.


    By the way, are there any good books on VBA for Excel with plenty of examples of code.


    Thanks for much needed help,
    Bob

  • 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:



    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.


  • 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.