excel - program a button to stop infinite loop -

i want display excel sheets in workbook 1 continuously use infinite loop. questions when need update data in sheets must stop infinite loop, how can using vba code? example want press button in workbook 2 stop infinite loop in workbook 1 (btw cant put button in workbook 1 because sheets changed continuously

or have better idea how display sheets continuously?

here code : **so sheet macrokeys contains word in cell a1 =yes trigger display each sheet 2 seconds (sheet1,sheet2,sheet3 sheet 1 again & repeat process infinitely

sub show_sheet1() dim macrokeys worksheet: set macrokeys = sheets("macrokeys") dim sh1 worksheet: set sh1 = sheets("sheet1") dim sh2 worksheet: set sh2 = sheets("sheet2") dim sh3 worksheet: set sh3 = sheets("sheet3")  macrokeys.range("a1") = "yes" ' [...] sh1.visible = true sh1.select sh2.visible = false sh3.visible = false  ' [...] if macrokeys.range("a1") = "yes" alerttime = + timevalue("00:00:02") application.ontime alerttime, "show_sheet2"     end if end sub  sub show_sheet2() dim macrokeys worksheet: set macrokeys = sheets("macrokeys") dim sh1 worksheet: set sh1 = sheets("sheet1") dim sh2 worksheet: set sh2 = sheets("sheet2") dim sh3 worksheet: set sh3 = sheets("sheet3")      macrokeys.range("a1") = "yes"     ' [...]     sh2.visible = true     sh2.select     sh1.visible = false     sh3.visible = false      ' [...]     if macrokeys.range("a1") = "yes"     alerttime = + timevalue("00:00:02")     application.ontime alerttime, "show_sheet3"     end if     end sub  sub show_sheet3() dim macrokeys worksheet: set macrokeys = sheets("macrokeys") dim sh1 worksheet: set sh1 = sheets("sheet1") dim sh2 worksheet: set sh2 = sheets("sheet2") dim sh3 worksheet: set sh3 = sheets("sheet3")  macrokeys.range("a1") = "yes" ' [...] sh3.visible = true sh3.select sh1.visible = false sh2.visible = false  ' [...] if macrokeys.range("a1") = "yes" alerttime = + timevalue("00:00:02") application.ontime alerttime, "show_sheet1" end if end sub 

i presume you're using sort of application.ontime() function "continuous loop". set boolean variable "loop" "true" or "false", , in method change sheet, have check whether boolean true or not. have button sets boolean false.

if post code you're using, can refine it.

edit: code / more definition: can use modeless form control start/stop. this, use vbmodeless argument in .show() method:

dim oform userform1 set oform = new userform1 oform.show vbmodeless 

the userform can have buttons start , stop looping: start it, set public boolean trueto continue looping, , start loop. stop it, have button set same boolean false.

use better button names, here simple code:

private sub commandbutton1_click() continueloop = true loopingcontroller end sub  private sub commandbutton2_click() continueloop = false unload me end sub 

in terms of other code: variables control looping (in standard module):

public continueloop boolean public const intervalseconds = 5 public const codetorun = "loopingcontroller" 

the code run loop:

public sub loopingcontroller() if continueloop = true     loopaction     application.ontime + timeserial(0, 0, intervalseconds), codetorun       '//this calls itself, long looping variable 'true' end if end sub 

and code runs each time:

public function loopaction() dim visiblesheetindex long visiblesheetindex = thisworkbook.activesheet.index  if (visiblesheetindex = thisworkbook.sheets.count)     visiblesheetindex = 1 else     visiblesheetindex = visiblesheetindex + 1 end if  thisworkbook.sheets(visiblesheetindex).activate end function 

you can kick off via:

public sub test()  dim oform userform1 set oform = new userform1 oform.show vbmodeless  end sub 

sorry - i'm spoon-feeding code here, challenge. can set start-up position make little less obtrusive.

also, similar togglebutton on ribbon.