run macro automatically in a loop

[BENSONJR]

****** 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 . . .

 

[BENSONJR]

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 . .

 

[mikerickson]

You could put this in a normal module

Sub GoToBlankSheet()
    ThisWorkbook.Sheets("blankSheet").Activate
End Sub

and this in ThisWorkbook

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