Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: Select Sheets For Specific Date By Clicking On A Calendar

  1. #1
    Member
    Join Date
    May 2013
    Posts
    84
    Rep Power
    12

    Select Sheets For Specific Date By Clicking On A Calendar

    Hi All

    I have attached a booking sheet to this post.

    This will be used to pre book vehicles in for mot, at the moment there is only a couple of sheets as a test but when it goes live there will be a sheet from monday and wednsday every week for at least one year but maybe more.

    I have indexed the pages with hyperlinks but this will get messy as the sheets grow,
    My question is:-

    If i name the sheet tabs with a date and year can an embedded calender be used to select a tab i.e. if i open the calender and select 5th may 2013 it will open the 5th of may 2013 sheet??

    Thanks for any imput

    Peter
    Attached Files Attached Files

  2. #2
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    I've added a calendar. However, I wouldn't be recommending this! Better have some sort of automatic index / home page creator.
    Attached Files Attached Files
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  3. #3
    Member
    Join Date
    May 2013
    Posts
    84
    Rep Power
    12
    Hi Thanks for that it seem to work fine,
    why would you not advise this ? would it be prone to fail?

    Also how do i get it to find any new sheet i add ?

    Can the calender be recalled from the page i am on instead of having to return to the index page?

    Do you have any examples of a better way to carry out this function?
    Regards

    Peter

  4. #4
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    It will find any new sheet you add. You just need to ensure you maintain the date format in the sheet tab name. And yes, you can call it from any sheet. Need to have a small modification in the code.

    Remove

    Code:
    Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
    
    
        Call CalendarCellButton
        
    End Sub
    from the index sheet module, and copy the following in the Workbook code module

    Code:
    Private Sub Workbook_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
    
    
        Call CalendarCellButton
        
    End Sub
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  5. #5
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    To your previous question, no it's not more prone to error than any other solution one can think of, but it's just that it is easy for a user to identify and relate to a button on an index sheet, rather than a date on a calendar. But of course that depends on who the user is. So if the above works for you, I'm fine with that.
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  6. #6
    Senior Member
    Join Date
    Jun 2012
    Posts
    337
    Rep Power
    13
    Even the instruction 'call' isn't necessary any more since 1997:

    Code:
    Private Sub Workbook_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
        CalendarCellButton
    End Sub
    I made an alternative: see the attachment.
    Attached Files Attached Files
    Last edited by snb; 05-16-2013 at 02:56 PM.

  7. #7
    Member
    Join Date
    May 2013
    Posts
    84
    Rep Power
    12
    Hi

    Can a calender button be placed on each page to recall it ? ant the moment you have to right click then select from the menu

    If i create a new sheet and name it the calender finds it but if i click and hold the mouse button on the tab and drag it to copy all data in sheet across it wont find it, even if i rename it

    Is there a easy way to add multiple sheets with all data/formula so that the calender can see it

    Regards


    Peter

  8. #8
    Member
    Join Date
    May 2013
    Posts
    84
    Rep Power
    12
    Hi snb

    How do you get new sheet to show in the list ?

    Peter

  9. #9
    Member
    Join Date
    May 2013
    Posts
    84
    Rep Power
    12
    No problem just closed it and reopened and it found them.

    The only problem with this is as the sheets grow you need to scroll down which is why i thought the calender
    approach looked and worked better..

    Thank you

    Have you any thoughts on the early questions for the calender type ????


    Peter

  10. #10
    Senior Member
    Join Date
    Jun 2012
    Posts
    337
    Rep Power
    13
    You don't have to scroll down if you sort the list with the latest sheet on top.
    You could also consider to select the item that matches today's date, because I assume that sheet will be needed most.

    Code:
    Private Sub Workbook_Open()
        c00 = [text(today(),"'ddd, mmm dd, yyyy'")&"!A1"]
        If Not Evaluate("isref(" & c00 & ")") Then Sheets.Add.Name = [text(today(),"ddd, mmm dd, yyyy")]
        
        For Each sh In Sheets
            If InStr(sh.Name, ",") > 0 Then c01 = c01 & vbLf & sh.Name
        Next
        Sheets("index").ListBox1.List = Split(Mid(c01, 2), vbLf)
        
        Sheets("index").ListBox1.Value = [text(today(),"ddd, mmm dd, yyyy")]
    End Sub
    The advantage of this approach compared to the use of a datepicker is that only available sheets will be shown in the listbox. So it's not possible to select a non-existing sheet. The datepicker approach would require all sorts of checks & messages if a sheet doesn't exist.
    Last edited by snb; 05-16-2013 at 05:56 PM.

Similar Threads

  1. Replies: 5
    Last Post: 06-15-2013, 12:40 PM
  2. Replies: 3
    Last Post: 06-10-2013, 06:12 PM
  3. Replies: 4
    Last Post: 06-10-2013, 01:27 PM
  4. Replies: 3
    Last Post: 05-17-2013, 01:22 PM
  5. Replies: 0
    Last Post: 02-27-2012, 01:07 AM

Posting Permissions

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