Run macro from cell result

alanhov · Jan 5, 2004

Is there any way to get a VBA procedure (macro) to run automatically based upon the logical result of a spreadsheet formula cell. I need to test a field value and if true then change the properties of several cells , and later reset the same properties if the valeu is something else.

I do not want the user to push buttons, or use the macro menu.

alanH

Guidoo · Jan 5, 2004

Alanhov.

You could use the "Change Event" in VBA. See the example below. In this example, cell A2 gets value 1 if cell A1 is "TRUE", otherwise it gets value 2. Procedure is run every time the worksheet is changed, so no buttons are required.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
addr = Target.Address
If Not (addr = "$A$1&quot

Then Exit Sub
If [A1].Value Then [A2].Value = 1 Else [A2].Value = 2
End Sub 'WorkSheet_Change

Guidoo · Jan 5, 2004

After testing the example above, if found that you have to delete the third line, otherwise it will only work if you actually type TRUE or FALSE into cell A1. So the example becomes:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
addr = Target.Address
If [A1].Value Then [A2].Value = 1 Else [A2].Value = 2
End Sub 'WorkSheet_Change

Eng-Tips is the largest engineering community on the Internet

Intelligent Work Forums for Engineering Professionals

Run macro from cell result

alanhov

Mechanical

Recommended for you

Guidoo

Chemical

Guidoo

Chemical

Similar threads

Part and Inventory Search

Sponsor