Good on ya
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:
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.VB:Private Sub UserForm_Initialize() Set clsCal = New clsCalendar Set clsCal.Form(Me.TextBoxDate) = Me End Sub
I developed this in 64-bit Excel 2010 beta, on 64-bit Windows 7.
well done... looks good. Thanks for sharing.
Check out our new reputation system. Click on the "star" under the post!
There are 10 types of people in the world. Those that understand Binary and those that dont.
Why are Halloween and Christmas the same? Because Oct 31 = Dec 25...
The BEST Lookup function of all time
Dynamic Named Ranges are your bestest friend
I like what I see and I much prefer this to solutions where I need to have the PC have certain files resident in the Programs folder. Using your way, (I assume) I can create a spreadsheet, send it to someone else, and it will work on their PC without them having to anything with their PC to enable it?
I may be asking too much here, and if this forum is only for the seriously advanced user, then I guess I am out of luck, but if you are willing, do you have step-by-step instructions for how to implement this solution?
I got the sample file and like the look/feel. I would want to have the calendar pop-up whenever I selected, say, a Cell with a date formatted as d-mmm-yy (for example).
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: Sample2.xlsm
That is what I am looking for
And by embedding it in the Sample Worksheet, you showed it can be sent to someone who can use it without setting up their system first - just enable macros.
I'll look into this more on the weekend (I think it will take a while for me to grasp this) so thank you very much
I was able to create a new Workbook and bring all your code/forms over to make this work
thank you very much
One question / ask :
I see that the code in the "Microsoft Excel Object" is in the worksheet, not the workbook. This means I copy/paste the code for each worksheet I add to the workbook. I tried copying the code into the Workbook holder, but it did not work. Is there a simple change I could make to the code so I could put it into the Workbook holder and thereby have it available for any worksheet in the workbook?
Great work and thanks again.
Is there a way to adapt the code so that instead of double-clicking on (any) cell, the pop-up (user) form only displays when you single-click on a cell that has been pre-formatted with a specific (date) format like d-mmm-yy ??
There are currently 1 users browsing this thread. (0 members and 1 guests)