Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "B2" '<== change to suit
On Error GoTo ws_exit
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
If Target.Value Then Call MyMacro
End If
ws_exit:
Application.EnableEvents = True
End Sub
This is worksheet event code, which means that it needs to beCode:Private Sub Worksheet_Change(ByVal Target As Range) Const WS_RANGE As String = "B2" '<== change to suit On Error GoTo ws_exit Application.EnableEvents = False If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then If Target.Value Then Call MyMacro End If ws_exit: Application.EnableEvents = True End Sub
placed in the appropriate worksheet code module, not a standard
code module. To do this, right-click on the sheet tab, select
the View Code option from the menu, and paste the code in.
but i don't know if once i call 'SetLinkOnData' it always runs or i have to call everytime with the loop.Sub MyMacro()
Do
SetLinkOnData "FDF|Q!'MYVALUE;timestamp'", "myMacro2"
Loop Until Sheets("Sheet1").Range("B2").Value = False
End Sub
inside a loop it gets stucked and excel freezes.Sub boolTis()
Dim wbTis As Workbook
Set wbTis = Workbooks("myFile.xls")
bRunNow = wbTis.Worksheets("Sheet1").Range("N1").Value
Do
If bRunNow Then
wbTis.SetLinkOnData "REUTER|IDN!'IBM,LAST'", "shareTis"
Else:
wbTis.SetLinkOnData "REUTER|IDN!'IBM,LAST'", "myDisabled"
End If
Loop Until bRunNow = False
End Sub
Sub Test1()
With ActiveWorkbook
bRunNow = .Worksheets("Sheet1").Range("C1").Value
If bRunNow Then
.SetLinkOnData "REUTER|IDN!'IBM,LAST'", "Test2"
Else .SetLinkOnData "REUTER|IDN!'IBM,LAST'", ""
End If
End With
End Sub
Sub Test2()
MsgBox "StockUpdated.", vbOKOnly, "Set Link On Data"
End Sub
Sub ButtonTest()
Test1
End Sub