Posts by Baodad

    Re: Count Specific Day Name Between 2 Dates

    I arrived here after Googling for how to find how many Mondays there are in a month. My solution is below. No UDFs, no array formulas. Mine only counts the number of Mondays in a month. I haven't tested it thoroughly, but it works for the 7 months I did test it on. I'm posting it here because others may be able to adapt it to their needs.


    Re: Non-activex Datepicker Calendar Control

    Single-clicking a cell is the same thing as selecting or entering the cell. You mean you want the pop-up to show automatically whenever the user enters a cell with a specific number format? (Whether by click or by the keyboard)

    I would handle that by adding code to the worksheet itself (right click on the worksheet tab at the bottom and choose "View Code"), using the worksheet event: Worksheet_SelectionChange. Basically, every time the user changes the selection on the worksheet, the code checks to see if it's the cell you want to show the pop-up on. The code could check the selection's .NumberFormat property to see if it's "d-mmm-yy" like you wanted, then display the pop-up.

    Re: Non-activex Datepicker Calendar Control


    Wow! Some great improvements you've made! I've tested it on my XP machine with Excel 2002; I'd like to see how it works on my Windows 7/Excel 2010 at work.
    I'm delighted to see how this project has evolved.

    Many cheers to you all,


    Re: Non-activex Datepicker Calendar Control

    Yes, the code was designed to accept an initial date as part of the input. If you look at the "LoadView" sub in the calendar class module, you'll see that it accepts an optional date for an input parameter.

    LoadView is called from the code in the FormPicker form, here:

    Private Sub UserForm_Initialize()
        Set clsCal.BoundForm = Me
    End Sub

    The question is how to pass a (optional) date parameter to this code. Like,

    clsCal.LoadView <myDate>

    Off the top of my head, the first thing I'd try is adding another global variable (of Date type, since that's what LoadView accepts) to Module1. This would need to be checked and set (or cleared) each time the workbook event code was fired. Then that variable could be accessed and used in the LoadView call shown above.

    LoadView checks the parameter in this line:

    If dtDate = 0 Then dtDate = dtToday

    so if the date is set to zero LoadView will just start by loading the current month.

    Re: Non-activex Datepicker Calendar Control

    Great work!

    I can confirm that your workbook works in my Excel XP (2002) at home, even after file conversion from the .xlsm format.

    One regression in the code from the very first file I uploaded, is that the "v" character to the right of the year in the calendar should actually be rendered in the "Marlett" font, where it will be displayed as an up and down arrow. If you click the top half of the character, it increases the year, and vice versa.

    You can fix this by changing this property in the calendar class module:

    Re: Non-activex Datepicker Calendar Control

    The only reason to have code in a worksheet is to take advantage of the events that are available there. Same goes for workbooks. I'm attaching a screen shot that shows how you can browse through the events available to you depending on what object you've clicked on in "Microsoft Excel Object," shown in the tree in the left pane.


    Once you click on an available event, the VBA editor will automatically insert the starting code you need. If there's an event that looks like it will work for you at the workbook level rather than the worksheet level, try it out. You can play with these events by putting simple code like

    msgbox "Event fired!"

    in one of the event subs it generates for you. Then go back to Excel and play around to see if you can make the code fire. (A message box should appear).

    If you find yourself needing to put duplicate code in many worksheets, the best practice is to remove as much common code as you can and put it in a code module. Then call that code from the worksheet event code.

    Re: Non-activex Datepicker Calendar Control


    This control depends on the functionality of VBA's userforms, so it really needs to be placed in a userform.

    In pre-Office 2007 versions of Excel, we could use a lot of tricks and APIs to remove a userform's title and borders, and make it look almost like it wasn't a userform, but it still needs to be a userform.

    I'm attaching a sample Office 2007-2010 macro-enabled workbook that demonstrates the behavior you desired. But it turns out to be a little complicated to implement, as you will see when you look through the code. You need 1) code in the worksheet itself to bring up the date picker, 2) a userform with the date picker controls on it, 3) the clsCalendar class module code, and 4) another normal module just to hold a public variable so that all the other code modules can access it.

    Unfortunately, this is a fairly advanced solution and it takes some understanding to suit it to your purposes. But I was once a beginner too, and I remember all the hours I spent trying to understand someone else's code. It was so helpful to me, I hope I can pass on the favor.

    Here's the sample workbook:

    All of you have been so helpful to me here over the years that I have been learning Excel and VBA. I finally feel like I can give something back to the community.

    Attached is a Non-ActiveX, Non MSCOMCT2.OCX dependent date picker calendar control for VBA userforms. To incorporate it into your project, you would need to copy the clsCalendar class module, and also add all the calendar control objects inside the FrameCalendar frame on the userform (including the frame itself) onto your form. You would also need another control on the form somewhere to be bound to the class, which would receive the user's selected date. Mine is called "TextBoxDate" and it is set in the UserForm code like this:

    Private Sub UserForm_Initialize()
        Set clsCal = New clsCalendar
        Set clsCal.Form(Me.TextBoxDate) = Me
    End Sub

    Anyway, I hope it works for you, or at least it can give you ideas. I hope it's appropriate but I'm sharing this under terms of the "New BSD license," which is really flexible. I don't really mind what you do as long as no one takes this and sells it and pretends they came up with it.

    I developed this in 64-bit Excel 2010 beta, on 64-bit Windows 7.