Calendar Control help

lor83

New member
Joined
Jun 13, 2011
Messages
1
Reaction score
0
Points
0
Hi All

I'm completely new to VBA and just need guidance on what I'm doing wrong....

I have a very simple spreadsheet called general to which I want to add a calendar control designed completely using vba coding to it.

However when I simply pull the object, form and module across to my spreadsheet i encounter all kinds of issues/compile errors/missing library/components etc....and I'm not sure how to get the code across properly...

I have also attached a second version where I seem to have pulled the code across, however the calendar doesn't work!

What I would like it to do is activate the calendar control when I click in cell A2:A5000, choose the date and then have it insert it into the cell I selected in the above range.

The calendar control has been designed by someone else and looking through various posts in other forums it seems to work fine....so it must be me and my lack of skills....

Thanks for any help...
 

Attachments

  • General with code transferred.xls
    83 KB · Views: 35
  • general.xls
    14 KB · Views: 30
  • Copy of Calendar10.2.xls
    117 KB · Views: 48

Ken Puls

Administrator
Staff member
Joined
Mar 13, 2011
Messages
2,520
Reaction score
5
Points
38
Location
Nanaimo, BC, Canada
Website
www.excelguru.ca
Excel Version(s)
Excel Office 365 Insider
Can you provide a link to the other forums where you see the code working correctly? It may help us diagnose the issues. :)
 

Jan Karel Pieterse

Super Moderator
Staff member
Joined
Mar 22, 2011
Messages
17
Reaction score
0
Points
0
You have to change the selection change event like so:

Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    'The 1st method is coded to launch when a curtain cell is  selected
    If Not Intersect(Target, Range("A2:A5000")) Is Nothing Then
        CalendarFrm.Show
    End If
    'another method is to have it show if a cell that is formatted as a date is selected...see below
    If Target.NumberFormat = "m/d/yy;@" Then
        CalendarFrm.Show
    End If
    'another method is using Select Case...see below
    Select Case Target.NumberFormat
        Case Is = "m/d/yy", "m/d/yyyy", "m/d/yy", "mm/dd/yy", "yyyy-mmm-dd"
        CalendarFrm.Show
        'Case Else
        '    MsgBox "Not a valid date format!"
    End Select
End Sub
 

psawyer

New member
Joined
Feb 14, 2012
Messages
3
Reaction score
0
Points
0
Location
Berlin, Germany
Alternative to calendar control

Here an alternative to calendar control.
 

Attachments

  • PickDateForm.xls
    132.5 KB · Views: 56
Top