Run macro from cell result

[alanhov]

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]

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]

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