Thursday, 7 July 2016

Calendar (Date Picker) Control Alternative

When you want to add a calendar to a userform, you can try to make use of the Microsoft Date and Time Picker Control. Unfortunately this control is not 100% reliable and a number of people have decided to make their own.

In my case, I was asked by a client the other day to add a calendar. I decided to tweak a calendar I made way back in 2005. That calendar is based on a formula I saw on Daily Dose of Excel by John Walkenbach.

Anyway, I had a think about what I should try to imitate with regards to the date picker control. First off, it's compact. After all, you don't want a single control to overwhelm your main form.

So I made this.



You can change the week start date to either Sunday or Monday, as well as show/hide ISO week numbers. (I used to work in International Sales and I used the ISO Calendar to refer to week numbers for use in logistics with clients and overseas representatives. So I thought it would be a good idea to include this functionality. The ISO calculation uses a formula based on one by Evert van den Heuvel.)







The date you select becomes dtCalendar in the clsDateLabels module.


I also decided to add some shortcut keys:

Alt + Q: Show the next month
Alt + W: Show the previous month
Alt + E: Show the next year
Alt + R: Show the previous year
Alt + T: Show this month and year
Alt + O: Show/hide options
Alt + I: Show/hide ISO week numbers
Alt + S: Start the week from Sundays
Alt + M: Start the week from Mondays
Alt + C: Show the calendar (if not visible)
Alt + X: Close the calendar (if visible)

A reasonably useful calendar.

But then I thought that a lot of people don't need any options, so I made 2 more versions which are even more compact.

This one is a simple calendar.



You can change the week start date by going to the mCalendar module and changing vbSunday to vbMonday.



And this is one that shows ISO weeks.



Shortcut keys for both the simple and ISO calendars:

Alt + Q: Show the next month
Alt + W: Show the previous month
Alt + E: Show the next year
Alt + R: Show the previous year
Alt + T: Show this month and year
Alt + C: Show the calendar (if not visible)
Alt + X: Close the calendar (if visible)

Download them here.



Tuesday, 31 May 2016

VBA Code Indenter v1.5

I've made some changes to my VBA Code Indenter.

For a start, it's now a xlam file. (Sorry, Excel 2007 or higher from now on). I also changed the code to automatically open the Trust Center so you can tick "Trust access to the VBA project object model", if it's not ticked already.




Finally, I changed the code to refer to the Code Window command bar. Some people who downloaded the tool said it was not appearing in the Visual Basic Editor as expected. To this end, I made an alternative tool that shows in the Excel ribbon instead. Anyway, I think the new version should have that problem beat - send an email to the address on the left of this blog if you have issues.

Here's the download link. Happy Indenting!

Saturday, 28 May 2016

AET Find and Replace v1.3

I decided to add an extra feature to my Find and Replace tool.

When using the Find and Select mode, I thought it might be useful to have an option where you don't just select cells containing the text you wish to find, but also highlight that text to make it stand out.

So adapting the code I used in this post, I present to you AET Find and Replace v1.3.

Here it is in action. Click the color wheel.



Choose a color.



Click Find and Select Fields.



Download it here.

Saturday, 12 September 2015

Updates

I'm working on various updates. One thing I have done is make yet another change to my AET Data Checker. I changed it so that when you show the form, the sheet you have on your screen (the Active Sheet) will be the sheet constantly referred to, even if you navigate to other sheets or workbooks.

Previously, selecting other sheets had the effect of making them become become the Active Sheet (the sheet to be referred to). This probably confused a few people or may have been a distraction.

Download the new version here.

Coming soon, a new version of AET Excel Utilities.

Saturday, 8 August 2015

AET Data Checker - New Features

I've added some new stuff to my AET Data Checker addin.

First, I added some labels so you can see what the Main Column and Look At Column are referring to (not just the Column letter)



The assumption is you will have your data set up with column headers in the first row.

Next up, you can now select the Main Criteria from a combo box (dropdown list). Or you can still type it in like before. Plus you can use wildcards like * and ?.



With all combo boxes, you can use the up and down arrow keys to rapidly change columns and criteria.

You'll also notice some new checkboxes.



What do they do? This!



When used, the highlighted labels make it easy to see what values matter. Use the square button at the top to turn all highlighting off or the checkboxes to toggle it individually.

Download the new version here.

See some examples of it's use here.

See you next time.