Select Sheets For Specific Date By Clicking On A Calendar

[peter renton]

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

[Excel Fox]

I've added a calendar. However, I wouldn't be recommending this! Better have some sort of automatic index / home page creator.

[peter renton]

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

[Excel Fox]

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

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

Private Sub Workbook_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)


    Call CalendarCellButton
    
End Sub

[Excel Fox]

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.

[snb]

Even the instruction 'call' isn't necessary any more since 1997:

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.

[peter renton]

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

[peter renton]

Hi snb

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

Peter

[peter renton]

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

[snb]

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.

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.