VBA Trigger

[ljrezn]

Hi,

I am wondering if it is possible to run a VBA procedure based on the value of a cell, or an automatic cell value change (not user driven).

Option 1: I have a cell that outputs one of two different messages based on a conditional statement. I only want the VBA code to run automatically when a certain value is shown.

Option 2: I enter input information in cells A1, A2, A3 which calculate together to provide a value in cell A5. I would like a VBA code to run when the cell A5 changes.

 

[Taul]

you could use a worksheet change event

Private Sub Worksheet_Change(ByVal Target As Range)

        Dim rng As Range
         '   Only look at single cell changes
        If Target.Count > 1 Then Exit Sub
        '   Set Target Range
        Set rng = Range("A5")
        '   Only look at that range
        If Intersect(Target, rng) Is Nothing Then Exit Sub
        
        'more code here
End Sub

 

[kpark91]

It is possible and
google up
Worksheet_change() event in VBA

then you will have ur answer and a tutorial and some knowledge in VBA :D
3 birds with 1 stone :P

 

[ljrezn]

I am aware of how the worksheet change event works, but that only triggers when the change is user driven. This cell i want to monitor is changed by a formula in the cell. Therefore worksheet change will not work in this case.

 

[Taul]

I enter input information in cells A1, A2, A3

How about making the range A1:A3 the target range

Private Sub Worksheet_Change(ByVal Target As Range)

        Dim rng As Range
         '   Only look at single cell changes
        If Target.Count > 1 Then Exit Sub
        '   Set Target Range
        Set rng = Range("A1:A3")
        '   Only look at that range
        If Intersect(Target, rng) Is Nothing Then Exit Sub
        
        'test to see if formula result in cell A5 gets entered into cell B5
        Sheet1.Range("B5").Value = Sheet1.Range("A5").Value
End Sub

 

[ljrezn]

That would work if my worksheet was that simple. I have numerous input cells that calculate many intermediate values and are lookups for tables. I would end up selecting basically all of the inputs.

It would work, but at that point I may as well just leave it triggered by the button.

Thanks for you help

 

[kpark91]

I am aware of how the worksheet change event works, but that only triggers when the change is user driven. This cell i want to monitor is changed by a formula in the cell. Therefore worksheet change will not work in this case.

Sure it would!

All you need is a public variable in a normal module as it will last throughout the whole session of the workbook.

so, declare a public variable.
Initialize the public variable when the workbook opens.
and use Worksheet_Change() event to constantly check A5 when a value changes in the worksheet (or you can be more specific if you want) and reset the value of the public variable to the current one if you find difference between the current and previous value!!

 

[ljrezn]

Ok I will give it a try.

I am a little unclear on how the Worksheet_Change Event will be able to monitor a value change in a cell that is not user changed. Do I define my Public Variable as that cell i want to monitor?

 

[kpark91]

Ok I will give it a try.

I am a little unclear on how the Worksheet_Change Event will be able to monitor a value change in a cell that is not user changed. Do I define my Public Variable as that cell i want to monitor?

Yes.

it'll look something like this

in normal module

Public Blarg#

in workbook module

_Initialize()
Blarg = Worksheets("Blarg").Range("A5").Value
End Sub

in worksheet module

_Change(ByVal Target As Range)
If Target.Address = "$A$5" Then
    If Target.Value <> Blarg then
        'Your action
        Blarg = Target.Value
    End If
End If
End Sub

 

[Taul]

nice one Kpark91,
I'll add that technique to my code list