Is there a function of excel vb that will continuously check the sheet?

Question

I have a combobox with the various options. For every option certain cells are formatted based on the values.

As the values changes every few second, the program needs to keep checking to see if the conditions are satisfied. Does anyone know of a way in which the program can check the sheet every few seconds???

yes. That all works fine. But the values in the cells changes as the data changes. — Chane, Jan 04 '12 at 12:10

score 0 · Answer 1 · edited May 08 '18 at 21:49

Depending on your needs, any of three different approaches might meet your needs.

The Excel Worksheet_Change event fires when the worksheet is changed by the user, excluding changes in cell formulas' values due to recalculation, but including changes by code. VBA code would typically look like:

    Sub Worksheet_Change(ByVal Target As Range)
       Application.EnableEvents = False
       ' <test or set scope of action of the macro>
       ' <do something>
       Application.EnableEvents = True
    End Sub

The Target argument is built-in and always refers to the cell that was changed and triggered the event. Your code must be placed in a worksheet module (most easily accessed by right-clicking the worksheet tab and selecting View Code), not a standard module.

The Worksheet_Calculate event occurs after the worksheet is recalculated, including automatic recalculation. Code would look like:

    Sub Worksheet_Calculate()
       Application.EnableEvents = False
       ' <do something>
       Applicaton.EnableEvents = True
    End Sub

Note that this event does not provide a target, that is, it triggers for all cells recalculated, not a single one. If your code does not force a recalculation, you would not need to disable and then re-enable events. If you want your code to recalculate just selected cells you can set calculation to manual (Application.Calculation=xlManual) and then reset it to automatic when you exit the procedure (Application.Calculation=xlAutomatic). As with the Worksheet_Change event, your code must be placed in a worksheet module.

Or finally, you can set a timer to rerun your code periodically using Excel's Application.OnTime event or Windows timers. Your procedure must include code to start the timer and to stop it. For an example of the use of the OnTime event, see this SO answer. For an explanation of using a Windows timer (as well as the Application.OnTime event), see Chip Pearson's review here.

Is there a function of excel vb that will continuously check the sheet?

1 Answers1