Cell value to begin a macro !!!

Some videos you may like

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

villy

Active Member
Joined
May 15, 2011
Messages
489
You have to use another macro telling the cell to call the macro to run if the value meets your criteria..
 

RomulusMilea

Board Regular
Joined
May 11, 2002
Messages
168
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.

Code:
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

New Member
Joined
Sep 22, 2011
Messages
5
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

Board Regular
Joined
May 11, 2002
Messages
168

ADVERTISEMENT

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

New Member
Joined
Sep 22, 2011
Messages
5
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

MrExcel MVP
Joined
Jun 9, 2006
Messages
1,802
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:
Code:
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

Board Regular
Joined
Sep 1, 2011
Messages
205
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.

Code:
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!!!!
 

Watch MrExcel Video

Forum statistics

Threads
1,123,381
Messages
5,601,302
Members
414,440
Latest member
Kim0204

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top