Easy Pop-Up calendar for specified cells - for reference only

[sykes]

I guess that this is a regularly searched theme, so thought that the "Questions" forum's the best place to put it. I've used several good keywords in the title so that it may help others when they search these terms.
I find this a very useful, simple little tool, so though it would be good to share it with others. Hope the moderators agree. I'm sure I'll hear about it if they don't !!!!
************************************************************************************************************************

Ever had a workbook in which you're constantly having to input dates? It can be very time consuming - especially if you have a particular format specified.
I was in the same situation, so decided to build a pop-up calendar which only appears if you click into a specified range of cells (those looooong date columns in your spreadsheet).
You simply click into the target cell, a calendar pops up, you pick the required date, the calendar disappears and the date's formatted to your requirements and entered into the cell. The code can work on ALL OPEN WORKBOOKS.
Please feel free to use this, but bear the following points in mind:
1. MAKE A COPY OF YOUR WORKBOOK, AND TEST IT ON THERE FIRST! NEITHER "Mr Excel" nor I can take any responsibility for ruining your data. I'm not an expert by any means!
2. You'll probably need just a basic understanding of VBA, userforms and controls to build this yourself. Should only take about 10 - 15 minutes for an average user.
3. I'm running Office 2007, so watch out for differences, but it should work on all versions of Excel within reason.
4. You'll need to have your Personal.xlsb (Personal.xls for earlier versions) available. This seems to be an issue with 2007 for some users, so if you can't find it in the VBA editor, it may have been disabled (possibly due to some error). If you can't see Personal.xlsb, either begin to record a macro, and when prompted
save it to "Personal macro workbook" (Excel may create a new .xlsb for you) or if you think one exists already (should be in C:/Users/Your username/AppData/Roaming/Microsoft/Excel/XLSTART)
then try and enable it by starting from the workbook, "Office" button, "Excel Options" at the very bottom, "Add-Ins from the left-hand menu, "Manage" at the bottom - select "Disabled Items" then click the "Go" button.If your .xslb is there you should be able to select and enable it.

The calendar and simple lines of code reside in the Personal.xlsb workbook, then get called by a few lines of code in the workbooks in which you wish to use the functionality.

In a module within Personal.xlsb (I'm in 2007 - obviously "Personal.xls" for earlier versions).
At the top of the module declare the variable to take the date from the calendar control, after it's been clicked:

Public dt As Date

Insert a new userform (userForm1 in my case) in Personal.xlsb
Place a calendar control onto the userform (I used Calendar control 9.0 - if it's not in the VBA editor toolbox, right-click the toolbox and select "Additional controls."
Set the calendar's date to the current one, using the userform's _initialize event:

Private Sub UserForm_Initialize()
Me.Calendar1.Value = Now()
End Sub

Write a function into the module (thanks to Mr Excel MVP mikerickson for his help here):

Function global_cal() As Date
UserForm1.Show
global_cal = dt
End Function

Finally, in the calendar's _click event....

Private Sub Calendar1_Click()
dt = Me.Calendar1.Value
Unload UserForm1
End Sub

Now all that's needed is to call the function from any other open workbook, so in any workbook's sheet selection_change event, write: (thanks to MrExcel MVP RichardSchollar for help with calling the function from another workbook) :http://www.mrexcel.com/forum/showthread.php?t=409690

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    With Target
          If .Column = 1 And .Row > 2 And .Row < 20 Then
              .HorizontalAlignment = xlCenter
              .NumberFormat = "dd-mmm-yyyy" 
                .Value = Application.Run("personal.xlsb!global_cal")
          End If
    End With
End Sub

.......of course changing the column and row references to suit your needs.
You could also make it more dynamic by perhaps using a named range for the worksheet_selectionchange target range instead etc.

It works well for me, but I'm no pro, and there are of course many (probably much better) alternatives and permutations. mikerickson suggested making the workbook an Add-In, for example: http://www.mrexcel.com/forum/showthread.php?t=409906

Hope it's of help to you.........

Sykes

 

[tusharm]

Since one cannot be sure that the calendar control is available on every machine, I chose to "roll my own." For shareware solutions see
Developer deployment calendar
http://www.tushar-mehta.com/excel/software/vba_calendar/
and
TM Calendar
http://www.tushar-mehta.com/excel/software/tm_calendar/index.html