Run a macro using cell value as trigger.

[Ilyas1979]

Hi all,

I just wanted to know whether we can run a macro by using a cell as trigger instead of activex control or alt+key.

Suppose i have a macro and to run it, I just have to type a letter (like 1 or 0) in a cell say A1. Or just tab from A1 to trigger the macro.

Regards,
Ilyas

 

[NARAYANK991]

Hi Ilyas ,

Is that not a waste of effort ?

Assume that you are working in a worksheet / workbook ; now , just in order to run the macro , you will navigate to cell A1 and enter 1 , and then your macro should run ; why not use a keyboard shortcut so that whenever you press the shortcut keys , the macro is executed.

Narayan

 

[Ilyas1979]

Hi Narayan,

Ya offcourse its a waste of time if i am using a single cell but suppose if i am using a range of cells and as soon as i change the cell value, the macro should run for the cell in the same row (maybe adjacent or a few columns away). I want to use this feature to save using the formula again and again (that results in slowing down of the system).

Regards,
Ilyas

 

[Hui]

You can use the VBA Sub Worksheet_SelectionChange event
Check that the cell is a pre-defined cell or not and then act accordingly
This is a fairly common practice

Maybe tell us more specifically what you want to do and we can suggest a way to achieve it

 

[Ilyas1979]

Thank You very much Hui.

 

[Hui]

I use this technique when I have cells that I want to feed a Pivot Table or Filter
This way the cell is monitored and when it changes it runs a macro to refresh a PT or update or apply a filter etc

 

[vijehspaul]

Hui,
In my code below (in sheet1), I am trying to call/run a macro based on a cell value change. The below code is working for me if the source cell (B7) and target cells/range in macro (reset and playback) are in same sheet.
But my workbook is bit large and have several sheets.
So i want to run macro on a page based on cell value change on another page.
How can i get it done ?

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$B$7" Then
    Dim text As String
    text = Range("B7").Value
    If text = "" Then
    reset
    ElseIf text = "Playback Device" Then
    playback
    End If
End If

End Sub

 

[Hui]

You can put a code in each Worksheet Code Module that you want to use

Private Sub Worksheet_Change(ByVal Target As Range)
  Call MySub(Me.Name, Target.Address, "$B$7")
End Sub

Change the Final Address in each Worksheet module to suit or link it to a cell if required

then add a Code Module with the following code

Sub MySub(Sht As String, Target As String, Addr As String)

If Target = Addr Then
  Dim text As String
  text = Range(Addr).Value
  If text = "" Then
  Reset
  ElseIf text = "Playback Device" Then
  playback
  End If
End If

End Sub

See attached example

 

[vijehspaul]

Thanks Hui. Wonderful.

 

[Hui]

Vijehapaul

Please start your own Post next time instead of jumping on the back of a similar post

There are two main reasons

1. It makes following the original thread so much easier
2. Searching is much improved