Calendar pop up not traveling with workbook

[wilcox96]

I found a vba for adding a pop up calendar/date entry userform from this site a while ago. It works great on the workbook I pasted it to, when I open the workbook on my pc. I simply right click in a date cell, choose "Insert Date" from the list, and there's the pop up calendar for me to click the date...

However, when someone copies the workbook and pastes it for use on their laptop, the calendar function doesn't work. It says it is not installed on their system. Isn't this just a series of codes and a user form that is part of the workbook - and therefore should just copy along with everything else? All "other" macros and vba's work just fine.

Any ideas?

 

[Joe Was]

It requires a Reference in the VBA References to work, unless you "Late Bind" the control Object in the code!
Or, build it as an Add-In.

 

[tusharm]

If you are using the Microsoft created control, it is not guaranteed to be on every PC. That's the downside of using it, or other similar controls.

I found a vba for adding a pop up calendar/date entry userform from this site a while ago. It works great on the workbook I pasted it to, when I open the workbook on my pc. I simply right click in a date cell, choose "Insert Date" from the list, and there's the pop up calendar for me to click the date...

However, when someone copies the workbook and pastes it for use on their laptop, the calendar function doesn't work. It says it is not installed on their system. Isn't this just a series of codes and a user form that is part of the workbook - and therefore should just copy along with everything else? All "other" macros and vba's work just fine.

Any ideas?

 

[Datsmart]

A Calendar is supplied with Excel, but not available unless chosen during installation.
Here is a link to the calendar file if needed.
http://www.fontstuff.com/vba/vbatut07.htm
Scroll to the bottom of the page for the zip file and Add-In.

 

[wilcox96]

...and then the add in would need to be installed on the individual pc's and laptops of the users. hmm.

Thanks, Dat..I think that's where I got it from originally...I appreciate the link. I would still need to get that installed on every pc/laptop. I suppose I could get IT to push that through as an update...if that's how it could work.

How would I use the late bind with this code? Here is the first part:

Option Explicit

Sub OpenCalendar()
' Displays the UserForm and calendar
' Shortcuts should be made to this procedure
frmCalendar.Show
End Sub


and here is the form code:
Option Explicit

Private Sub cmdClose_Click()
' Close the UserForm
Unload Me
End Sub

Private Sub UserForm_Initialize()
' Check if active cell contains a date. If 'yes' show
' same date on calendar. If 'no' show today's date.
If IsDate(ActiveCell.Value) Then
Calendar1.Value = DateValue(ActiveCell.Value)
Else
Calendar1.Value = Date
End If
End Sub

Private Sub Calendar1_Click()
' Transfer date selected on calendar to active cell
' and close UserForm.
ActiveCell.Value = Calendar1.Value
Unload Me
End Sub



??

 

[Joe Was]

You late bind the control to the Active Sheet like this:

Sub myAddCal()
'Standard Macro code, like: Module1.
Dim objCal As Object

Set objCal = ActiveSheet.OLEObjects.Add(ClassType:="MSCAL.Calendar.7", Link:=True, _
DisplayAsIcon:=False, Left:=155, Top:=115, Width:=170, Height:=130)

objCal.Name = "myCal"
End Sub

Sub myDeleteCal()
'Standard Macro code, like: Module1.

ActiveSheet.Shapes("myCal").Select
Selection.Delete
End Sub

It will call the Calendar even if it is not activated in Office, but it must be in the Office Folder. It puts the Calendar set to ToDay on the sheet for view. You can select the Year and Month for view on the control. You cannot use it to select and place the day, you can only see the days.