New Excel Forum

This forum has been moved to TeachExcel.com

Ask all future questions in the New Excel Forum.

ExcelKey

Setting Cell Values Depending Upon Another Cell’s Value

Free Excel Macros

Setting Cell Values Depending Upon Another Cell’s Value

Postby Sisyphus » Sat Mar 24, 2012 3:29 am

This is a small collection of simple VBA tools designed to be at once instantly deployable, easy to understand and modify, and nearly unlimited in its capacity for expansion. With this target in mind it was impossible to follow a code structure such as created by Excel’s macro recorder. If the recorder was your primary teacher you’ll find the structure introduced here both strange and easier to follow.

Whereas the capability of the code in the attached workbook is declared as “Setting Cell Values Depending Upon Another Cell’s Value” its range of application extends from simply adding a date stamp when a cell is set to “Completed” as demonstrated in the example to setting different results in multiple cells in response to various cell contents. In fact, the code is so versatile that any VBA action at all can be prompted by the value of the referenced cell. It is with this versatility in mind that the code was structured into five components as follows:

  1. Definition of Action and Target cells,
    where Action cells are those whose content trigger an action while Target cells are those whose value is set by the program.
  2. Worksheet_Change event procedure, installed at sheet level.
    This procedure determines whether the changed cell is an Action cell or not.
  3. Sub ‘CheckAction’,
    whose job it is to determine whether the contents of the changed cell now warrants action to be taken.
  4. Sub ‘TakeAction’,
    which carries out the pre-determined action, and
  5. Function ‘ActionIndex’
    which returns the index number of the Target cell.
All of these components are very short, very easy to understand and fully independent. For example, if you wanted to change 15 cells instead of only 1 you would only need to modify the Definitions, and if you wanted to insert a worksheet instead of stamping a date you would only need to modify ‘TakeAction’.

Since, due to their brevity and naming, as well as the naming of variables, the code is self-explanatory only the Definitions be discussed here. These consist of two constants declared as Public so that they may be available to all procedures in the project:
Public Const ActionList As String = "E4,E7"
Public Const TargetList As String = "F4,F7"

The requirement is that they hold valid cell addresses, separated from each other by commas, and that the number of elements in both strings be the same. There is no limit to the number of cells that you can list, except that you can’t have less than 1. You can use range definitions like A3:A5 in either of the strings, and the size of the matching strings may be different. For example, you may color the Target range A1:A64000 yellow in response to the Action range C16 being “Stalled”.

All components should be installed in a normal code module as was done in the attached example, except for the event procedure which must be installed in the code sheet of the worksheet that holds the referenced cell or cells.
You do not have the required permissions to view the files attached to this post.
Have a great day! :D

Sisyphus
I do this for "honour and country" - much less of the latter, actually.
If I helped you, award points, plenty of them.
If I bored you, deduct points for being too long-winded. (I know, :lol)
Sisyphus
Former Moderator
 
Posts: 4454
Joined: Dec 7, 2011
Location: Shanghai
Reputation: 203
Excel Version: 2010

Return to Excel Macros

Who is online

Users browsing this forum: No registered users and 33 guests