Microsoft Excel - Code for drop down calendar in Excel 07

Asked By pete r. on 26-Nov-12 07:40 AM
Hello
Be glad if someone can help on this - I am very basic knowledge of coding etc. in Excel and someone has given me this code to provide a drop down calender in a spreadsheet when a certain cell is clicked on - but also with the option to manually enter the date.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Column = 4 And Target.Row <> 1 Then
      Sheet1.SheetPick.Visible = True
      Sheet1.SheetPick.Top = Sheet1.Cells(Target.Row, Target.Column).Top
      Sheet1.SheetPick.Left = Sheet1.Cells(Target.Row, Target.Column).Left
      If Sheet1.SheetPick.Width < Sheet1.Cells(Target.Row, Target.Column).Width Then
        Sheet1.SheetPick.Width = Sheet1.Cells(Target.Row, Target.Column).Width
      Else
        Sheet1.SheetPick.Width = 66
      End If
      Sheet1.SheetPick.Height = Sheet1.Cells(Target.Row, Target.Column).Height
      If Sheet1.Cells(Target.Row, Target.Column) = "" Then
        Sheet1.SheetPick.Value = DateTime.DateValue(Now)
        Sheet1.Cells(Target.Row, Target.Column) = DateTime.DateValue(Now)
      Else
        If Not IsDate(Sheet1.Cells(Target.Row, Target.Column)) Then
          Sheet1.SheetPick.Value = DateTime.DateValue(Now)
          Sheet1.Cells(Target.Row, Target.Column) = DateTime.DateValue(Now)
        Else
          Sheet1.SheetPick.Value = Sheet1.Cells(Target.Row, Target.Column)
        End If
      End If
      R1 = Target.Row
      C1 = Target.Column
    Else
      Sheet1.SheetPick.Visible = False
    End If
End Sub


This works fine in the spreadsheet I have copied this from but if I try and enter it into my own spreadsheet - amending the cell references as necessary it won't work - highlighting .sheetpick as the issue with an error box saying 'Compile error, Method or data member not found'

I can't find any info on .sheetpick and am not sure how to change this - would be glad of any suggestions or advice on what I need to change/ add or check to make it work for me, I actually want to apply it to cells D10-34 and F10-34 on the same spreadsheet in my workbook.

Hope this is clear - look forward to hearing from you.
Many thanks
Peter
John D replied to pete r. on 26-Nov-12 01:37 PM
Hi
"SheetPick" is not a vba command that I know, but I'm using XL2003 and you never know.
I believe you're missing more code. Maybe a user defined function (UDF) or an ADD-In or just more code
I read your code and the macro is setting the calendar to the cell size and it seem to allways give todays date
Could you select a date from that calendar or just todays date?
Here is a link to a sample file : http://cjoint.com/?3KAtLmWVOiD
I don't know if that will work for you.


Harry Boughen replied to pete r. on 26-Nov-12 11:38 PM
Hello pete,

Try this file.

Calendar_a.zip

The calendar activates when you select B10 and shows today's date on the calendar.  When you click on the calendar the relevant date is entered in the cell.  You can change the date format in the code and also nominate multiple cells by using a range of cells.

Hope this helps

Harry
pete r. replied to Harry Boughen on 27-Nov-12 01:13 AM
Thanks John D & Harry
John D - sorry I could not seem to access your file and Harry I'll try that calendar out.
https://www.dropbox.com/s/isyr25vnffmgyiv/Main%20Tracker%20copy.xlsm
Here is the file which I got the code from - as you can see when you click on any cell in column D it gives you the option to select the drop down calendar and select a date, I think this works well and would be good for my spreadsheet but I can't seem to transfer it to another workbook - as detailed above...
Be glad of any suggestions for A) improvement on this or B) why it has an error in my spreadsheet.
Many thanks
Peter
Harry Boughen replied to pete r. on 27-Nov-12 01:32 AM
Hello pete,

That file looks like the one that you have modified.

Regards

Harry
pete r. replied to Harry Boughen on 27-Nov-12 02:41 AM
Hello Harry
That file is the one I was given with the code in - as you can see it all works fine, until I try and use it in my spreadsheet.  Just copy and pasting the code doesn't seem to work...
Many thanks
Peter
Harry Boughen replied to pete r. on 27-Nov-12 03:10 AM
Hello pete,

The reason I thought it had been modified is that it does not work for me - giving the same error that you are getting.

Harry
Harry Boughen replied to pete r. on 27-Nov-12 06:04 AM
Hello again pete,

If you look under Name Manager you will find that there is a name with a broken link.  It appears to be an OLE object and this might have something to do with why it doesn't work.  Perhaps the link is to another sheet or to a personal macro workbook.

Regards

Harry

pete r. replied to Harry Boughen on 27-Nov-12 08:33 AM
Hello Harry

I just noticed there is a named range in that file which is probably the link you are refering to.  However I can't understand why it still works on the file I placed on Dropbox (although it seems to refer to an error #REF!) but not on my spreadsheet - I even replicated the module and the named range in my spreadsheet but still no luck.

Pete
Harry Boughen replied to pete r. on 27-Nov-12 02:40 PM
Hello pete,

But the one from DropBox doesn't work for me.  What version of Excel are you using?  I gather that there are some incompatibilities between Excell2010 and earlier versions to do with calendar functions.

Harry
pete r. replied to Harry Boughen on 27-Nov-12 04:06 PM
Strange - that file works ok for me.  I am using Excel 2007.

Pete
Harry Boughen replied to pete r. on 27-Nov-12 04:57 PM
Hello pete,

Well I am using 2010 and it is failing the same way that you describe for your case and I only have access to an old Excel97 otherwise.  I have tried to install sort of a retro-fit of the calendar functionality on my laptop but for some reason it wont let me. 

Perhaps if you try zipping the file that works and posting through here, I'll have another look.

Regards

Harry
pete r. replied to Harry Boughen on 28-Nov-12 12:48 AM
Harry - no problem, maybe this will work.

(zipped) Folder.zip

Regards
Pete
Harry Boughen replied to pete r. on 28-Nov-12 03:35 AM
Hi Peter,

No different I'm afraid.

Can you go to your developer tab and select controls/extra controls and see if there is anything called something like calendar in the list that comes up and whether it is selected or not - could even have 'missing' against it.  Just trying to determine whether you have the calendar functionality installed or not.

The fact that you say that the file that you posted works of itself but doesn't when you copy to another workbook suggests that there is something missing.  Have you checked for macros associated with the workbook itself, the worksheet and any forms etc?  Or even in personal.xlsb?

EDIT:  I suspect that there might be a Class module somehwere.

Harry
pete r. replied to Harry Boughen on 29-Nov-12 01:23 AM
Hello Harry
Strange..? - yes there are various calendars installed, 'Calendar Control 12.0', 'Calendar Adapter Class', 'CalendarSynk Class', ' Microsoft Date and Time Picker Control 6.0 (SP4)' are the ones listed there.
Yes there is one macro as you can see that sorts the worksheet by Date - I didn't think this effected the date selection though.
I was hoping I could create this calendar function from scratch in the new workbook (using the code above) but this doesn't seem to be as straight forward as I first thought !
Many thanks
Peter
Harry Boughen replied to pete r. on 29-Nov-12 01:50 AM
Hello Peter,

In the one that I have down loaded, there is no data sorting macro.  The button does not seem to even be active in any way.

I was hoping that in the version of the file that you reckon works, that there would be a Class Module which would contain the programming for that method or whatever that the macro is failing on.  Sheetpick is not standard VBA and would have to have been defined in a Class Module or something.  If that Module is missing the macro cannot work.

The mystery for me is why there seems to be such a difference between what you have been uploading and what you are claiming works/ is present in the working spreadsheet on your computer.

Regards

Harry
pete r. replied to Harry Boughen on 29-Nov-12 02:12 AM
Harry - ok it may be that the code for that date-sort button got removed.  try this one which will hopefully work?  I think the date function relates to a different column on this one but will hopefully work for you.
New Compressed (zipped) Folder.zip
Regards
Peter
Harry Boughen replied to pete r. on 29-Nov-12 03:43 AM
Hello Peter,

Still no joy.  The code for the sort button is there but it is still dead.  The problem might be that the spreadsheet sees trying to click on it as a selection change that triggers the selection change macro and that fails because it does not recognise the Sheetpick option during compile so the macro never really gets going.

There is also a mysterious ?textbox with a red cross on it that i suspect might have something to do with the anticipated calendar function.  The problem is I don't know how much of my problem is related to the fact that I am using Excel 2010.  However I would have thought that if there was a Class Module in your 'working' spreadsheet that it would survive uploading.

The only other possibility is that it is somehow contained in an Add-in.  Would you be able to check what Add-ins you have installed as well.  However as Add-ins are specific to the Excel set-up I would have thought that it should be available to any workbook that you generate on your system.  I think we really have to find a class module hidden somewhere.

Regards

Harry