looping a macro with a cell value

earp_

Active Member
Joined
Apr 30, 2008
Messages
305
If i have a cell b1 in sheet2 with a boolean value and i want to run my macro always if that cell is true how can i do that?
 

Some videos you may like

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

xld

Banned
Joined
Feb 8, 2003
Messages
5,378
Code:
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 be
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.
 

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Possibly

Code:
Sub test()
Do
'do stuff
While Sheets("Sheet2").Range("B2").Value = False
End Sub
 

earp_

Active Member
Joined
Apr 30, 2008
Messages
305
thanks i will try both.
i guess with the first one i don't need to activate my macro but write or false or true.
in the second case i have to start my macro with the macro panel.
 

earp_

Active Member
Joined
Apr 30, 2008
Messages
305

ADVERTISEMENT

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

that is run just once, what about if I want a loop?
 

xld

Banned
Joined
Feb 8, 2003
Messages
5,378
It runs every time B2 changes. What will drive the llop, B2 won't change on its own?
 

earp_

Active Member
Joined
Apr 30, 2008
Messages
305
B2 it's a dynamic data through link, DDE.
I fix the loop thing with this
Sub MyMacro()
Do
SetLinkOnData "FDF|Q!'MYVALUE;timestamp'", "myMacro2"
Loop Until Sheets("Sheet1").Range("B2").Value = False
End Sub
but i don't know if once i call 'SetLinkOnData' it always runs or i have to call everytime with the loop.
Do you know it?
Should I open a new thread for this answer?
...
i will open a new thread
 
Last edited:

earp_

Active Member
Joined
Apr 30, 2008
Messages
305
that worked but only once!
instead if i put my code
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
inside a loop it gets stucked and excel freezes.

Why everything worked with a button but not with a cell?
the code with the button activation is
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,368
Messages
5,595,749
Members
414,017
Latest member
surajks

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