Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Kitchener, Ontario, Canada
    Posts
    228
    Thanks
    0
    Thanked 0 Times in 0 Posts

    sheet control (2002)

    This is my first attempt at VB in excel so please bear with me.

    I am trying to make a timer that pauses a macro. on the sheet (Data - General info) is a cell which is used to set the amount of time for the timer the problem I am having is that the wookbook has a few sheets in it and if I try and use the

    Sheets("Data - General info").Select
    Range("A11").Select
    PauseTime = ActiveCell * 60 ''' Sets duration in seconds.

    portion of the code the page changes to (Data - General Info) from below you can see what I attempted to do; jumt to the page holding the number in the cell the best way to fix this would be to not jump to that page like I am doing but I have no clue to call info from cell (A11) off of page (Data - General info) without going to that page and selecting the cell. If I have to do it this way then I lose the page I was on when this bit of code started to run.

    Dim PauseTime, Start, Currentpage

    sh.Name = Currentpage
    Sheets("Data - General info").Select
    Range("A11").Select
    PauseTime = ActiveCell * 60 ''' Sets duration in seconds.
    Currentpage.Select
    Start = Timer '''''''''''''''''''''''' Set start time.
    Do While Timer < Start + PauseTime
    DoEvents ''''''''''''''''''''''''' Yield to other processes.
    Loop

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: sheet control (2002)

    Welcome to the Lounge and the fun of VBA programming. I will make several comments (you said you wanted to learn):

    1- Your first statement is:

    Dim PauseTime, Start, Currentpage

    That statement will define those three variables as Variants. Although this will work most of the time, it is very inefficient. It is always better to define the variable as what it is going to be used for, Like this:

    <pre>Dim PauseTime As Date, Start As Date, Currentpage As Worksheet
    </pre>


    Date is a data type that includes the date and the time.

    2- You don't need to select a worksheet or cell to get the value from it. You can get the contents of cell A11 on sheet "Data - General info" like this.

    <pre> PauseTime = Worksheets("Data - General info").Range("A11").Value
    </pre>


    3- Using the Timer function for what you are trying to do could be very dangerous. If the Timer loop begins just before midnight and ends after midnight, you could end up with a pause that never ends. I also could not tell exactly what is in cell A11 (why are you multiplying it by 60). If cell A11 contains the number of seconds you want to pause, then you could do something like this:

    <pre>Dim PauseTime As Date, EndTime As Date, Currentpage As Worksheet
    PauseTime = TimeSerial(0, 0, Worksheets("Data - General info").Range("A11").Value)
    EndTime = Now() + PauseTime
    Do While Now() < EndTime
    DoEvents
    Loop
    </pre>


    4- It also looks like you were trying to save the active sheet when the code starts so you could go back to it. Although this is not necessary with my code, I will show you a couple of ways to do this.

    Method 1:

    <pre>Dim strACName As String
    strACName = ActiveSheet.Name
    'do stuff here
    Worksheets(strACName).Activate
    </pre>


    Method 2:

    <pre>Dim oAS As Worksheet
    Set oAS = ActiveSheet
    'do stuff here
    oAS.Activate
    </pre>

    Legare Coleman

  3. #3
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Kitchener, Ontario, Canada
    Posts
    228
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: sheet control (2002)

    The *60 was because cell A11 is in minutes.

    I will give it a try thanks

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: sheet control (2002)

    If A11 is in minutes, then change my code to:

    <pre>Dim PauseTime As Date, EndTime As Date, Currentpage As Worksheet
    PauseTime = TimeSerial(0, Worksheets("Data - General info").Range("A11").Value, 0)
    EndTime = Now() + PauseTime
    Do While Now() < EndTime
    DoEvents
    Loop
    </pre>

    Legare Coleman

  5. #5
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Kitchener, Ontario, Canada
    Posts
    228
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: sheet control (2002)

    That works great.....Thanks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •