Run Macro

ibrahimaa · #1 05-11-2012, 03:41 AM

I know that we can configure the macro to be run automatically once we open the xls file of by asking the user to click a specific button that trigger the macro.

However, I need to run the micro once the user enters a specific value in specific cell

.

I heard about event macros

and I do not know if it can solve this issue and how. I appreciate any help.

Thank you.

macropod · #2 05-11-2012, 07:59 PM

Hi ibrahimaa,

You could use a 'Worksheet_SelectionChange' macro like the following in the relevant worksheet's code module:

Code:

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
If Target.Address = "$A$3" Then
  If Target.Value = 123 Then
    Application.EnableEvents = False
    'Call your other macro here
    Application.EnableEvents = True
  End If
End If
End Sub

In the above example, your macro will be called if cell A3 is selected and its value is 123. Note that merely selecting the cell when it has this value will be enough to trigger the macro. If you don't want that, you might prefer to use the 'Worksheet_Calculate' event. That event, though, won't be triggered by inputting anything into the cell unless that results in the worksheet re-calculating.

Code:

Private Sub Worksheet_Calculate()
If ActiveCell.Address = "$A$3" Then
  If ActiveCell.Value = 123 Then
    Application.EnableEvents = False
    'Call your other macro here
    Application.EnableEvents = True
  End If
End If
End Sub

You'll note that, in both case, I've included some Application.EnableEvents lines. Depending on what you're doing you may or may not need these.

Peter Wienand · #3 05-11-2012, 09:02 PM

There is also the Worksheet_Change() function which will trigger when cells on the worksheet are changed by the user or by an external link. Note however that this event does not occur when cells change during a recalculation.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
How do I assign a macro to a button when the macro is in my personal workbook?	foolios	Excel Programming	2	07-27-2011 02:41 PM