Page 1 of 2 12 LastLast
Results 1 to 15 of 28

Thread: Name That Tab!

  1. #1
    Star Lounger
    Join Date
    Feb 2001
    Posts
    60
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Name That Tab!

    Hi,
    I have a workbook that has a sheet corresponding to each month of the year, ( Aug 01, Sept 01, Oct 01, etc.)The users wanted the sheet to open with the active cell in the top left corner, which I have done. Now they want the workbook to open at the current month. I know this will involve refering to the Tab name, but I lack the know-how to incorporate this into code. Can it be done???

    Thank you, in anticipation.:-)

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

    Re: Name That Tab!

    How about something like this:

    <pre> Worksheets(Format(Today(), "mmm") & " 01").Activate
    </pre>

    Legare Coleman

  3. #3
    Platinum Lounger
    Join Date
    Dec 2000
    Location
    Queanbeyan, New South Wales, Australia
    Posts
    3,730
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Name That Tab!

    What Lefare said- plus you need to put it into the Workbook_Open event.

    This screenshot will show you how to find it in the VBA editor (just in case you don't know)
    Attached Images Attached Images
    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

  4. #4
    Star Lounger
    Join Date
    Feb 2001
    Posts
    60
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Name That Tab!

    Dear L G and gwhit, thanks for the quick response.(apologies for the abb.)
    I have this in ThisWorkbook - ('workbook' and 'open')

    Private Sub workbook_activate()
    Range("c8").Activate
    End Sub
    'for the cell they want, which is fine.
    'and your thoughts (below) for the correct tab, or current month.

    Private Sub Workbook_Open()
    Worksheets(Format(Today(), "mmm") & " 01").Activate
    End Sub
    The snag is that it comes up with a compile error:
    'Sub or Function not defined.', with the word 'Today' highlighted.
    Any thoughts?; bearing in mind I am capable of mistakes.

    Thank you.

  5. #5
    Platinum Lounger
    Join Date
    Dec 2000
    Location
    Queanbeyan, New South Wales, Australia
    Posts
    3,730
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Name That Tab!

    Change "today" to "now".
    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

  6. #6
    Star Lounger
    Join Date
    Feb 2001
    Posts
    60
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Name That Tab!

    Dear gwhitfield and Legare Coleman,
    How large a slap on the back would you like, and for how long?
    You are formally endowed with the title 'Supreme Beings'.
    GW, the 'now' change was the canine g******s. Thank you. Now I can go to bed.

    All the best,
    Farside.

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

    Re: Name That Tab!

    Change Today to Now. Today is a worksheet function not a VBA function. Sorry, I didn't test that line and I thought Today worked in both places.

    Do you really want that code in the activate event procedure. The use will be put back to that sheet and cell every time he switches from the sheet to anything else. That would be very agrivating to me. Wouldn't the Workbook open event be a better place?
    Legare Coleman

  8. #8
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    NJ, USA
    Posts
    239
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Name That Tab!

    Would concur with Legare. You could use the Workbook_Open event as follows:

    <font color=blue>Private Sub Workbook_Open()
    Dim wks As Excel.Worksheet

    For Each wks In ThisWorkbook.Worksheets
    wks.Activate
    Range("C8").Activate
    Next wks

    Worksheets(Format(Now(), "mmm") & " 01").Activate
    End Sub</font color=blue>

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

    Re: Name That Tab!

    Did you mean to use the Select method on the Range("C8")? I believe that the Activate method will only activate a cell if it is in the current selection, and in your code you do nhot know if it is or is not.
    Legare Coleman

  10. #10
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    NJ, USA
    Posts
    239
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Name That Tab!

    Thanks. To be honest, I'm often not sure whether to use .Select or .Activate. I believe for a single cell, they're functionally equivalent(?). I did test the code and it works, - even if other ranges were previously selected. Presumably whatever was selected and/or activated previously is superseded.

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

    Re: Name That Tab!

    OK, I didn't actually try it, I just read the help file. According to the help file, Activate used on a Range object is just supposed to move the active cell within the current selection. If it is actually changing the selection, then it may be a bug that gets fixed one of these days, or it may be a feature and the documentation may be changed. If it was me, I would not risk the bug being fixed and my code not working at some time in the future.
    Legare Coleman

  12. #12
    Star Lounger
    Join Date
    Feb 2001
    Posts
    60
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Name That Tab!

    Dear All,
    Just to let you know; I was in to work early and put the code in and it works fine. The wb opens at the month tab and the cell C8 is selected (that's the Boss's part of the sheet by the way!)Of course, with all the planning ahead these days, the monthly sheets for 2002 are a different matter! I think a new wb is the way ahead.:-(

    Regards,
    Farside

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

    Re: Name That Tab!

    Does that mean I get half of your next raise? [img]/w3timages/icons/laugh.gif[/img]
    Legare Coleman

  14. #14
    Star Lounger
    Join Date
    Feb 2001
    Posts
    60
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Name That Tab!

    LegareColeman,
    I don't think the 3% will stretch that far, so how about a Crimbo card and a big mention for you and Woody's at work :-)

  15. #15
    2 Star Lounger
    Join Date
    Dec 2000
    Posts
    188
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Name That Tab!

    Actually if you don't need to worry about the time use Date instead of Now. Now is the current system date and time, Date is the current system date.

    FWIW

Page 1 of 2 12 LastLast

Posting Permissions

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