If you've ever had to create a data entry spreadsheet you know how important standardizing is.... That is, if you plan on having to do ANY analysis without having a huge clean-up job first.
Today's post is about dates and calendar controls.
For the longest time, I labeled my column with a header that looked something like this:
Date
(mm/dd/yyyy)
I was trying to indicate to the user that they should simply enter the date in the format that I had shown. YEAH, RIGHT! Apparently, that is asking too much. So what was I to do? Well, I looked online for solutions to my problem. Unfortunately, the solution often came as an add-in that would need to be locally installed/registered on every machine that would be using the calendar control. To begin with, I can't download and install files on my machine at work due to company policies. To add to the problem, my files are shared across our network and have been used by upwards of 100 different people. So, this was not going to work.
Therefore, I decided to create my own in VBA! Rough though it may appear, this little control does the trick nicely.
How it works:
I first built a calendar on the "CalendarControl" spreadsheet. This has a list of years and Months and drop-downs for each. The calendar days on the spreadsheet look at the year and month selected to determine what day the first date should appear on. The rest then follow suit. So, the calendar control is really just a prettied up Graphic User Interface (GUI) of the control on the spreadsheet.
Here is a pic of the calendar in action:
I have set up the calendar to initiate when the user double clicks in a cell that is in a date column (In this case, it is column E). The following code was added to the worksheet to get the calendar to only appear when the user double-clicks in colums E, L, and M:
Public Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Select Case ActiveCell.Column
Case Is = 5
FrmCalendar.Show
Case Is = 12
FrmCalendar.Show
Case Is = 13
FrmCalendar.Show
Case Else
End Select
End Sub
The link below will allow you to download the Calendar Control file. I have successfully tested it in Excel Versions 2007, 2010, and 2013.
https://drive.google.com/file/d/0B_cZb61zmYEfY0NpbkFILUdZcUE/edit?usp=sharing
**NOTE: This is a "Bare Bones" copy of the tool ready for you to implement how you wish. The downloadable file is currently set up to add a date to whatever cell is active - This is why I added the worksheet code to only allow the calendar to launch when double-clicking in predetermined columns.
Feel free to change the code/form in any way you like. If you like these tools and/or posts, please refer you friends and colleagues!
As always, we're all working to be better at what we do, feel free to share comments and thoughts (But keep them professional, please!).
Today's post is about dates and calendar controls.
For the longest time, I labeled my column with a header that looked something like this:
Date
(mm/dd/yyyy)
I was trying to indicate to the user that they should simply enter the date in the format that I had shown. YEAH, RIGHT! Apparently, that is asking too much. So what was I to do? Well, I looked online for solutions to my problem. Unfortunately, the solution often came as an add-in that would need to be locally installed/registered on every machine that would be using the calendar control. To begin with, I can't download and install files on my machine at work due to company policies. To add to the problem, my files are shared across our network and have been used by upwards of 100 different people. So, this was not going to work.
Therefore, I decided to create my own in VBA! Rough though it may appear, this little control does the trick nicely.
How it works:
I first built a calendar on the "CalendarControl" spreadsheet. This has a list of years and Months and drop-downs for each. The calendar days on the spreadsheet look at the year and month selected to determine what day the first date should appear on. The rest then follow suit. So, the calendar control is really just a prettied up Graphic User Interface (GUI) of the control on the spreadsheet.
Here is a pic of the calendar in action:
I have set up the calendar to initiate when the user double clicks in a cell that is in a date column (In this case, it is column E). The following code was added to the worksheet to get the calendar to only appear when the user double-clicks in colums E, L, and M:
Public Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Select Case ActiveCell.Column
Case Is = 5
FrmCalendar.Show
Case Is = 12
FrmCalendar.Show
Case Is = 13
FrmCalendar.Show
Case Else
End Select
End Sub
All the End User has to do is choose the Month/Year and click on the day. The control will automatically add the pre-formatted date to the active cell and then disappear to allow the user to continue entering data.
The link below will allow you to download the Calendar Control file. I have successfully tested it in Excel Versions 2007, 2010, and 2013.
https://drive.google.com/file/d/0B_cZb61zmYEfY0NpbkFILUdZcUE/edit?usp=sharing
**NOTE: This is a "Bare Bones" copy of the tool ready for you to implement how you wish. The downloadable file is currently set up to add a date to whatever cell is active - This is why I added the worksheet code to only allow the calendar to launch when double-clicking in predetermined columns.
Feel free to change the code/form in any way you like. If you like these tools and/or posts, please refer you friends and colleagues!
No comments:
Post a Comment