run macro automatically in a loop

BENSONJR

New Member
Joined
Sep 13, 2009
Messages
10
****** http-equiv="Content-Type" content="text/html; charset=utf-8">****** name="ProgId" content="Word.Document">****** name="Generator" content="Microsoft Word 11">****** name="Originator" content="Microsoft Word 11"><link rel="File-List" href="file:///C:%5CWindows%5CTemp%5Cmsohtml1%5C01%5Cclip_filelist.xml"><!--[if gte mso 9]><xml> <w:WordDocument> <w:View>Normal</w:View> <w:Zoom>0</w:Zoom> <w:HyphenationZone>21</w:HyphenationZone> <w:PunctuationKerning/> <w:ValidateAgainstSchemas/> <w:SaveIfXMLInvalid>false</w:SaveIfXMLInvalid> <w:IgnoreMixedContent>false</w:IgnoreMixedContent> <w:AlwaysShowPlaceholderText>false</w:AlwaysShowPlaceholderText> <w:Compatibility> <w:BreakWrappedTables/> <w:SnapToGridInCell/> <w:WrapTextWithPunct/> <w:UseAsianBreakRules/> <w:DontGrowAutofit/> </w:Compatibility> <w:BrowserLevel>MicrosoftInternetExplorer4</w:BrowserLevel> </w:WordDocument> </xml><![endif]--><!--[if gte mso 9]><xml> <w:LatentStyles DefLockedState="false" LatentStyleCount="156"> </w:LatentStyles> </xml><![endif]--><style> <!-- /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal {mso-style-parent:""; margin:0cm; margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:12.0pt; font-family:"Times New Roman"; mso-fareast-font-family:"Times New Roman";} span.googielink {mso-style-name:googie_link;} @page Section1 {size:612.0pt 792.0pt; margin:70.85pt 3.0cm 70.85pt 3.0cm; mso-header-margin:36.0pt; mso-footer-margin:36.0pt; mso-paper-source:0;} div.Section1 {page:Section1;} --> </style><!--[if gte mso 10]> <style> /* Style Definitions */ table.MsoNormalTable {mso-style-name:"Tabla normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-parent:""; mso-padding-alt:0cm 5.4pt 0cm 5.4pt; mso-para-margin:0cm; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:10.0pt; font-family:"Times New Roman"; mso-ansi-language:#0400; mso-fareast-language:#0400; mso-bidi-language:#0400;} </style> <![endif]--> Is it possible to create a macro that runs automatically for certain amount of time and in a loop waiting for the value of a cell to be X and if not, the cursor goes to a blank worksheet and if the user attempts to leave there and go somewhere else, the macro in question would start to run again? Or any other scheme to arrive to the same result. The idea is to devise a way to provide access to a workbook for people that knows where to put the X for example. Thank you for your attention and help . . .
Also, how can I update a cell with the =Now() automatically? Thanks again . . .
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
Is it possible to create a macro that runs automatically for certain amount of time and in a loop waiting for the value of a cell to be X and if not, the cursor goes to a blank worksheet and if the user attempts to leave there and go somewhere else, the macro in question would start to run again? Or any other scheme to arrive to the same result. The idea is to devise a way to provide access to a workbook for people that knows where to put the X for example. Thank you for your attention and help . . .
Also, how can I update a cell with the =Now() automatically? Thanks again . .
 
Upvote 0
You could put this in a normal module
Code:
Sub GoToBlankSheet()
    ThisWorkbook.Sheets("blankSheet").Activate
End Sub
and this in ThisWorkbook
Code:
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Excel.Range)
    Dim keyCell As Range, testFor As String
    Set keyCell = Sh.Range("A1"): Rem adjust
    testFor = "x": Rem adjust
    
    Select Case Sh.Name
        Case Is = "blankSheet"
            Rem do nothing
        Case Else
            If UCase(keyCell.Text) = UCase(testFor) Then
                Call stopTime
            End If
    End Select
End Sub

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
    Call stopTime
    If Sh.Name = "blankSheet" Then
               Call setTime
    End If
End Sub

Sub setTime()
    RunTime = Now + TimeValue("00:00:04"): Rem adjust delay
    Application.OnTime RunTime, "GoToBlankSheet"
End Sub

Sub stopTime()
    On Error Resume Next
    Application.OnTime RunTime, "GoToBlankSheet", schedule:=False
    On Error GoTo 0
End Sub
 
Upvote 0

Forum statistics

Threads
1,223,231
Messages
6,170,884
Members
452,364
Latest member
springate

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
Back
Top