VBA Express : Excel - Achieving Target Value in a cell

Excel

Achieving Target Value in a cell

Ease of Use

Easy

Version tested with

2000, 2002

Submitted by:

Justinlabenne

Description:

Deliver a message to a user when a cells value has reached a specified target value.

Discussion:

The attached example contains Worksheet_Calculate codes that can be used to tell when a cells value has been achieved, is too high, or is too low. The target cell must contain a formula. I put a formula in a hidden column that sum's up a range of cells. If the hidden cells value meets a certain limit, a message is delivered to the user informing them that the target value has been reached. Many more possibilities, and the example contains 3.

Code:

instructions for use

			Option Explicit 
 
Private Sub Worksheet_Calculate() 
     '   Target value:
    Const lVal As Long = 100 
     
    Dim rCell As Range 
     
     
     '   Put the cell you want to look at here
     '   Cell must be a formula!!
    Set rCell = Range("C1") 
     
     
     '   =================================================================
     '   If the target cells value equals the specified target value,
    If rCell.Value = lVal Then 
         
         '       then deliver a message
        MsgBox "Target value of " & lVal & " has been achieved", 64, "Target Met!" 
         
    End If 
     '   =================================================================
     
     
     '   Explicitly clear memory
    Set rCell = Nothing 
End Sub

How to use:

Open an Excel Workbook
Copy the code
Right Click on a Sheet tab > View Code
Paste code into the right pane
Press Alt+Q to return to Excel
Save workbook before any other changes

Test the code:

Place a SUM formula in a cell {C1 In the example}
Start placing numbers in the dependent cells of the SUM formula
When the target value {100 in the example} has been reached
a message will appear

Sample File:

Achieve Max Value.zip 8.84KB

Approved by lucas

This entry has been viewed 193 times.