Results 1 to 5 of 5
  1. #1
    5 Star Lounger
    Join Date
    Oct 2001
    Location
    San Bernardino, California, USA
    Posts
    734
    Thanks
    0
    Thanked 0 Times in 0 Posts

    What does this sub routine do? (2003)

    I had this in a workbook and I must have gotten it somewhere but I do not recall what it does. Whatever it was supposed to do - it does not. When I go to debug, this is what I see.

    Private Sub Workbook_Open()
    Dim Tgt As Range
    Worksheets("2005").Activate
    Set Tgt = Range("A:A").Find(what:=Date)
    <span style="background-color: #FFFF00; color: #000000; font-weight: bold">Tgt.Select</span hi>
    End Sub

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: What does this sub routine do? (2003)

    It tries to find a cell containing the current date in column A on the 2005 worksheet, and to select this cell.

    I presume that 03/30/2006 is not present on a worksheet named "2005", so the code fails.
    If you have a sheet named 2006 with dates in column A, you could change "2005" to "2006".
    Otherwise, just delete the entire sub.

  3. #3
    5 Star Lounger
    Join Date
    Oct 2001
    Location
    San Bernardino, California, USA
    Posts
    734
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: What does this sub routine do? (2003)

    That makes perfect sense. I copied that worksheet into another workbook and would like it to automatically invoke when that worksheet tab is selected. I tried using the following but it does not do anything. What did I do wrong?

    I selected the TAB and VIEW CODE then entered

    Private Sub Worksheet_SelectionChange(gotocurrentdate)
    Dim Tgt As Range
    Worksheets("2006").Activate
    Set Tgt = Range("A:A").Find(what:=Date)
    Tgt.Select
    End Sub

    This is what happens:

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: What does this sub routine do? (2003)

    1) The Worksheet_SelectionChange event occurs when the user moves to another cell in the same worksheet, not when the user switches between worksheets.
    2) gotocurrentdate is not a valid argument for the SelectionChange event. All these event procedures have a rigidly prescribed syntax, you cannot just change it.

    Try the following:
    - Remove the code you now have.
    - Right-click the sheet tab of the 2006 sheet.
    - Select View Code from the popup menu.
    - Copy the following code into the module:

    Private Sub Worksheet_Activate()
    On Error Resume Next
    Range("A:A").Find(What:=Date).Select
    End Sub

  5. #5
    5 Star Lounger
    Join Date
    Oct 2001
    Location
    San Bernardino, California, USA
    Posts
    734
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: What does this sub routine do? (2003)

    Perfect, thank you.

Posting Permissions

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