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

Some videos you may like

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

BENSONJR

New Member
Joined
Sep 13, 2009
Messages
10
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

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,779
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
 

Watch MrExcel Video

Forum statistics

Threads
1,109,021
Messages
5,526,299
Members
409,694
Latest member
bastos21

This Week's Hot Topics

Top