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

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Run macro from cell result

alanhov

Mechanical

Guidoo

Chemical

Guidoo

Chemical

Similar threads

Part and Inventory Search

Sponsor