Calendar pop up not traveling with workbook

wilcox96

Board Regular
Joined
Jan 5, 2005
Messages
169
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?
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

Joe Was

MrExcel MVP
Joined
Feb 19, 2002
Messages
7,539
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

MrExcel MVP
Joined
May 28, 2002
Messages
11,029
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?
 

wilcox96

Board Regular
Joined
Jan 5, 2005
Messages
169
...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

MrExcel MVP
Joined
Feb 19, 2002
Messages
7,539
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.
 

Forum statistics

Threads
1,181,418
Messages
5,929,793
Members
436,694
Latest member
dpatete

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top