Cell value to begin a macro !!!

[MarkReddell]

Does anyone know how to start a macro based on a cells value??? Thx.

 

[villy]

You have to use another macro telling the cell to call the macro to run if the value meets your criteria..

 

[RomulusMilea]

Hello Mark,

Read carefully below code, adapt it to your needs and insert it in the module of the sheet where target cell is hosted.

Private Sub Worksheet_Calculate()
    Application.EnableEvents = False
    If ThisWorkbook.ActiveSheet.Cells(2, 2).Value = 2 Then '<<<--- Cell B2 is defined and its value - modify them to suit your needs
    Call Your_Macro_to_be_run '<<<--- Modify this to suit your needs, make sure the macro runs smoothly
    End If
    Application.EnableEvents = True
End Sub

Please confirm this solution solves your problem, as expected. Thank you.

Romulus.

 

[coaxial]

Perhaps not the most efficient approach but you can always place your macro into one of the events for the Sheet itself, instead of in a module. Try this:

In the VB Editor double click Sheet1 (Sheet1) on the left hand side and add the macro code like shown below. The Worksheet_Calculate event is triggered whenever Excel has to perform a calculation on the page. Essentially whenever a cell value is changed, a filter is applied, etc.

Private Sub Worksheet_Calculate()

'This code runs whenever the Excel Calculate function is called
If Worksheets("Sheet1").Range("E5").Value = "Hello World" Then
MsgBox ("Code Execution!")
End If

End Sub

 

[RomulusMilea]

Hi Coaxial,

Your idea is OK, very similar to my idea. The code you proposed will not run any predefined macro, it will just display a Message box. It needs to be reviewed, I guess...

 

[coaxial]

RomulusMilea,

Yah, it looks like were both typing out replies at exactly the same time.

I'm a little confused that you would bother typing out a reply to me indicating that my code simply opens a message box. Clearly that's all it does. The idea was to show how you could get any arbitrary code to execute triggered by the value of a cell.

Thanks.

 

[Cindy Ellis]

If you want to trigger the macro based on the cell's content, you use a Worksheet_Change event, with the macro in the code sheet for the Worksheet rather than in a standard module.
Something like:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address <> "$A$1" Then Exit Sub 'change address to the cell that you're interested in
    'If the value is the result of a formula, use the address(es) of the cells that are inputs to the formula
        If Range("A1").Value = "testValue" Then 'replace testValue with the value that will trigger the rest of the macro
        'Code goes here when cell A1 has the value of interest
            MsgBox "hello"
        End If
End Sub

Hope that helps,
Cindy

 

[MarkReddell]

Hello Mark,

Read carefully below code, adapt it to your needs and insert it in the module of the sheet where target cell is hosted.

Private Sub Worksheet_Calculate()
    Application.EnableEvents = False
    If ThisWorkbook.ActiveSheet.Cells(2, 2).Value = 2 Then '<<<--- Cell B2 is defined and its value - modify them to suit your needs
    Call Your_Macro_to_be_run '<<<--- Modify this to suit your needs, make sure the macro runs smoothly
    End If
    Application.EnableEvents = True
End Sub

Please confirm this solution solves your problem, as expected. Thank you.

Romulus.

Thx. for your help!!! Question: If A1=0 or "", How would invoke a macro that aleady exits? Thanx 4 your help!!!!