Problem with a Excel Calendar Datepicker in a Floating VBA Userform

[butterscotch]

Windows 8.1 64-bit
Excel 2013 32-bit

I created the above using the following instructions:-

https://www.youtube.com/watch?v=EDZjSqukMIM

I am now experiencing a small problem with the pop-up calendar.

When I open it, it does not go to the current date/month and, for some reason, it has a specific date greyed out (I am assuming this is because that is the date I created the calendar)

Photo attached.

T

[skywriter]

Without the worksheet we can't see the code you are using, nor can we see what properties you have set.

[butterscotch]

Thanks skywriter

I put the calendar in my "Personal" workbook, so that it would be available in all new workbooks. I assume therefore that there is no point of sending it to you.

I attach the code as I entered it (from the instructional Youtube video mentioned earlier)

Please let me know if you need anything else.

I'm not a coder, so I will send what you need, if you send me instructions.

The calendar opens fine but there are 2 issues:-

1) It does not go to current date
2) For some reason, 25/02/2015 is highlighted in grey.

T

[butterscotch]

Thanks skywriter. Your direction has led me to the conclusion that it was, indeed, a duplicate that was causing the problem.

Following your advice, I backed up both and in the end, decided to delete both and start again from scratch.

So, here is where I am at the present. I now have a "clean" created calendar.

The current date is selected, so it appears as if we have resolved that issue. I would like to wait until tomorrow though, to confirm if current date is again selected.

Perhaps, in the meantime, we could spend some time trying to resolve the second issue. I believe I now understand why the date was highlighted in grey. Please see attached as an explanation of what I did and what the result was.

T

[skywriter]

Hi T,

No I doubt that it's a bug, this is just what you get. You should keep experimenting with those properties and see what they do. As far as waiting until tomorrow to see if the date issue is fixed you don't have to do that, you can check it today.

Date is a function and what that means is it is returning a number to the calendar, hopefully the number is always today's date. The way you can check it is to simply add or subtract a number from it and see if it changes the date. So if you simply type + 1 after the word date and re initialize the form it should be on tomorrow's date and then you can remove the +1.

This would also be how you could set the calendar to always have a default date of 1 month from today +30. You might be using it for writing invoices and you want it to always be on or close to the day you are going to enter for your invoice and you don't want to have to change it to the next month.

Thanks for the rep points.
Good Luck!!!
Bruce

[skywriter]

Click on the calender to select it, then go to the view menu and then properties. Look for the name of the calendar. You should probably just select the name and copy it. Go to the code for the form and in the drop down list at the top on the right choose initialize.
You should get some code that looks like what's below without the date time picker stuff in it. Add your name then type the period and you should get intellisense giving you some choices, choose value and then type = date. You are recreating what you see below, but you have to use the actual name of your calendar.

Edit: Okay I didn't scroll down far enough in your PDF. It looks like your calendar is named calendar.
So if your type Calendar. you should get the intellisense list to pick value from. If you immediately get the list after typing the period we are on the right track. Also type date with a small d and after you hit enter it should turn to a capital D another sign things are working right.

Private Sub UserForm_Initialize()

DTPicker1.Value = Date

End Sub

[butterscotch]

Thanks Skywriter

OK - this is where my lack of coding is a problem.

Referring back to my attached document, I go to Forms, right-click on Calendar and select "View Code"

I get a window with a heading PERSONAL.XLSB - Calendar (Code)

This is what I see:-

Private Sub CommandButton1_Click()

Unload Me

End Sub

Private Sub MonthView1_DateClick(ByVal DateClicked As Date)

ActiveCell.Value = DateClicked

End Sub

Can you please take it from there?

Thanks,
T

[skywriter]

You know I'm looking at your PDF and I just noticed something. If you changed the name of your userform to Calendar and you named the Calendar calendar, you really don't want to do that. It causes confusion.
You put the calendar onto a user form correct?
Click onto the userform, not the calendar, not any buttons, just onto a blank part of the userform and right click and then choose view code. Select initialize from the drop down box as noted below.
If you like the calendar name, you should name the form something like frmCalendar and the calendar itself something like calCalendar or ctrlCalendar that way it's obvious what object you are talking about. Naming the form and the actual calendar on the form with the name Calendar is not a good idea.


In your PDF on the left side the second code box down says
PERSONAL.XLSB - UserForm1(Code)
Then below it there's a drop down list on the left that says
UserForm and on the right there's a box that says click.
When the left one says userform you choose the right one and there's a choice of Initialize. Choose that one and the first and last lines of code that I showed you will automatically be entered into the window. You type the middle line as discussed in my post #4. Save everything and you should be good to go.

[butterscotch]

Thanks MarvinP

skywriter

The current date is being selected correctly now.

Date of creation is still being highlighted in grey, but I assume that's a bug and something I can live with. Appreciate your support.

Tommy

[butterscotch]

Thanks skywriter.

OK - that did not work. If I can give further clarification.

Here is the code now:-

Private Sub MonthView1_DateClick(ByVal DateClicked As Date)

End Sub
________________________________________________________________________________

Private Sub UserForm_Click()

End Sub
________________________________________________________________________________

Private Sub UserForm_Initialize()

Calendar.Value = Date

End Sub

There are 3 parts with the description (in the top-left box) when I click into it.

The first one says MonthView1, whereas 2 and 3 say UserForm

T

[skywriter]

Read my post #6, it's really hard to do this if I can't see the actual workbook. I'm looking at your PDF that you sent. Look at the PDF. At the top on the left you have a box that says Calendar(UserForm) next to it is one that says UserForm1(UserForm). Down at the very bottom you have two property boxes that both say Calendar UserForm, now look at my post #6. I'm trying to figure out what you did. If you have a userform and you named it calendar and you have a calendar named calendar, this is not a good idea. Normally I just leave my userform named userform1 whatever it happens to be. Nobody sees this it's just a name, but with code you name multiple things calendar and then try to refer to them in code it gets confusing. So since you won't share the actual calendar with me I'm trying to figure out what you did by your pictures.

[butterscotch]

Sorry if I'm confusing things skywriter. I'd be happy to send the workbook, but I thought that as it is a "Personal" workbook where this is saved, then it is unique to me??? Am I wrong?

Can I send you just my Personal workbook and where will I find it?

T

[skywriter]

Okay let's just see if we can clear up a couple of confusions. In your PDF that you sent at the top you have two userforms. Do you have two userforms?
To make sure we get the code for the right userform, if you have more than one userform just click in a blank are of the userform, not the calendar or a button, just the form the part that has all the dots. Then right click and choose view code. When you do this the left box should say userform, don't touch it, leave it alone and go to the right button and choose initialize and add the code we talked about before. Save close everything down and reopen and see what happens.

If this doesn't work then if you go to save as like you are going save the worksheet as something else you should be able to see the path to where it is now. Click go advanced the button next to save when you are posting on this site and then pick the paperclip in the new toolbox and you get and then you can upload your sheet to this site.

[butterscotch]

Thanks for your continued support skywriter. Before I proceed, can I tidy up a bit.

I fear that I have duplicates and probably should clean up before I proceed. I have put the details in the attached and perhaps you can advise if I should delete one. Interestingly, one form (called Calendar) displays with both the behaviours that are faulty (not selecting the current date and with 25/02/2015 greyed out) while the other (called UserForm1) displays with only one of the faults (not selecting current date)

T

[skywriter]

Why don't you go to save as, then change the name of the file, save it to your desktop so you know where it is and upload it here, then I'll figure out what is wrong and we'll get you up and running. The original file can stay where it is. Don't worry about any of this, no need to apologize I really want to you help you and am more than happy to do it. If you have any sensitive information in your workbook just delete it. It should not be an issue since we are only concerned with getting the calendar(s) squared away.

[butterscotch]

Thanks skywriter

Here is a copy of my "Personal.xlsb" file. I assume this is what you want.

If there is a duplicate form, please remove.

I assume I'll need to save any updated file to the correct location later (or else follow a detailed list of instructions from you on my local "Personal" workbook).

T

[skywriter]

Okay the actual calendar on both forms is named, monthview1.
Use this code.

Private Sub UserForm_Initialize()

MonthView1.Value = Date

End Sub

[butterscotch]

Thanks skywriter. We're getting there!

As I said, I'm not a coder and don't have a good command of VBA, so I don't want to make a mess of this, so here is what I've done.

1) I opened Excel (Book1)
2) I unhid Personal workbook
3) I went to Visual Basic in the Personal.xlsb workbook

When I click on each of the forms and select view code, I get the following:-

There appears to already be some "Initialise" code with the Calendar.Value = Date in it

I would need instructions as to what to do and exactly where to add the code.

T

[mc84excel]

Hi butterscotch,

You want an excel calendar date picker in a floating VBA user form?

And the default selected date is todays date & month?

See attached. Is this what you were looking for?

HTH


(I didn't write this myself. The original code came from https://sites.google.com/site/e90e50...-control-class )

[MarvinP]

Hi, I don't want to confuse things here and haven't read all the above thread, but Excel 2013 has a new calendar tool that may help you avoid this whole problem.
See it at:
http://www.vertex42.com/apps/minicalendar.html
or read about Apps for Office where this Calendar tool is included at:
http://theapprefinery.com/2013/04/20...-project-2013/