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

    Goto a date (2003)

    I have a spreadsheet with a column full of dates - the entire year, in fact. I would like for the cursor to go to the cell containing the date on the day I open the spreadsheet.

    Any way I can have this happen automatically?

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

    Re: Goto a date (2003)

    The code below, placed in the workbook open event routine in the module behind the ThisWorkbook object will activate worksheet Sheet1, then search column A for the current system date and select the cell where it is found. Modify the code for the correct worksheet and column.

    <pre>Private Sub Workbook_Open()
    Dim oTgt As Range
    Worksheets("Sheet1").Activate
    Set oTgt = Range("A:A").Find(what:=Date)
    oTgt.Select
    End Sub
    </pre>

    Legare Coleman

  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: Goto a date (2003)

    Thanks, I sure hate to ask but.... how do I associate that code with an event?

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

    Re: Goto a date (2003)

    The ThisWorkbook module is where you put event procedures for the workbook-level events. By creating a procedure named Workbook_Open in this module, it automatically becomes the event handler for the Workbook_Open event. No extra steps needed.
    It'll only work if you use the exact name Workbook_Open, and if you put it in ThisWorkbook. Any other name, and any other location will fail.

  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: Goto a date (2003)

    Sorry, Hans. I don't get it. In Access, I can go to properties and events and insert code but I don't see how to do it here. I inserted a screen shot of what I have done. I know this is not right because it does not work.

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

    Re: Goto a date (2003)

    Never mind, I figured out what was wrong. Although,I still do not understand what you said in your last post. I feel as if i have been totally immersed in some foreign language. That is the best way to learn, I guess.

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

    Re: Goto a date (2003)

    Please read the replies by Legare and me carefully.
    <hr>It'll only work if you use the exact name Workbook_Open, and if you put it in ThisWorkbook. Any other name, and any other location will fail.<hr>
    You have NOT put the code in ThisWorkbook, as we both told you, but in the module for Sheet1. It will NOT work there.

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

    Re: Goto a date (2003)

    If you select Workbook in the Object dropdown list in the ThisWorkbook window, the Procedure dropdown will display a list of available events (see screenshot).

    If you type Workbook somewhere in the Visual Basic Editor and press F1, you'll get help about the Workbook object. You can click on Events to see a dropdown list of events, clicking on an item takes you to the (admittedly brief) help for that event.

    You can search this forum and the VB/VBA forum for Workbook_Open etc., you'll find lots of examples.

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

    Re: Goto a date (2003)

    To get the code into the ThisWorkbook module, find the ThisWorkbook object in the VBE Project Explorer. Right click on this object and select View Code from the pop up menu. Then do as Hans said and select Workbook from the left drop down list and Open from the right drop down list. Replace the Dummy procedure with my code.
    Legare Coleman

Posting Permissions

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