Pop Up Excel Calendar VBA CODE

  • Thread starter Thread starter Pete
  • Start date Start date
P

Pete

I had a problem with my users, they are scattered all over the place.
And I was creating some really good applications with excel.

But when I started programming using the Activex Calendar control
problems started occurring.

Sure it worked fine on my system but on others it did not.
Sometimes I could talk them into how to get it to work.
But then were times when they were on networks that did
not allow installing the control.

So I came up with the idea of making A VBA pop up calendar that
could be used universally. Because I got a lot of help from this group.
So I am releasing the code for all of you to use.

This should work with Excel 97 and above. There is not much to
the documentation at all. So if you need help with using it just ask.

Basically you can call it from a form button on your excel sheet
or you can even call it up from a selection sheet change.

If the date in the active cell is blank the calendar will pop up
with the current system date.

If there is a date in the active cell then the calendar will pop up
with the month and year of that cell.

If the data in the active cell is not a date then the calendar pops
up with the current system date.

Here is the link to the file.

http://www.mediafire.com/?29hymh2m3ew

BTW I am not an MVP or was I formerly trained using Excel.
I just learned it on my own. And I do not profess to be an expert
In any way.

Enjoy
 
Hi Pete,

Looks good. On my system the combo - box LB_Mth only populates 12 Januarys.
i.e not january to December.

Mike

Mike.
 
Pete,

It's this loop which evaluates to 1st to 12th jan for each of the passes so
with the format set each of those dates evaluates as january.

For I = 1 To 12
LB_Mth.AddItem Format((I) & "/1/" & (ThisYear), "mmmm")
z = Format((I) & "/1/" & (ThisYear), "ddmmyyyy")
Stop
Next

Mike
 
Pete,

Corrected line for UK date format but probably messes up systems formatted US.

Mike

For I = 1 To 12
LB_Mth.AddItem Format("1/" & (I) & "/" & (ThisYear), "mmmm")
Next
 
It's this loop which evaluates to 1st to 12th jan for each of the passes
so
with the format set each of those dates evaluates as january.

For I = 1 To 12
LB_Mth.AddItem Format((I) & "/1/" & (ThisYear), "mmmm")
z = Format((I) & "/1/" & (ThisYear), "ddmmyyyy")
Stop
Next
Click to expand...

Ah! Aren't international issues wonderful? Using these Format statements
instead of the above ones should make the code work correctly for all
regional settings...

Format(DateSerial(ThisYear,I,1), "mmmm")

Format(DateSerial(ThisYear,I,1), "ddmmyyyy")

Rick
 
Hi Pete,

excellent. A tool to remember.
BTW I am not an MVP or was I formerly trained using Excel.
I just learned it on my own.
And I do not profess to be an expert In any way.
Click to expand...

I bet, most of us did it without training,
schooling, courses, whatsoever.

If you are smart, you can make it.
Which doesn't make life any easier, regrettably.


--
Greetings from Bavaria, Germany

Helmut Weber, MVP WordVBA

Win XP, Office 2003
"red.sys" & Chr$(64) & "t-online.de"
 
Ah! Aren't international issues wonderful? Using these Format
statements instead of the above ones should make the code work
correctly for all regional settings...

Format(DateSerial(ThisYear,I,1), "mmmm")

Format(DateSerial(ThisYear,I,1), "ddmmyyyy")

Rick
Click to expand...

Thanks Rick, Forgot that someone from another country might use it.
 
Pete,

Corrected line for UK date format but probably messes up systems
formatted US.

Mike

For I = 1 To 12
LB_Mth.AddItem Format("1/" & (I) & "/" & (ThisYear), "mmmm")

Next
Click to expand...

Mike, did you see Rick's post?

Change the format to the following

Format(DateSerial(ThisYear,I,1), "ddmmyyyy")

That makes it work with your country and mine.

When you first pointed it out I could not figure out
what you were talking about. Then rick pointed out
the above.

So I changed wmy system settings to UK and saw
for my self what you were talking about.

Pete
 
Hi, when I click on any date in the calendar, I get the date instead of the month and viceversa. I have a Spanish date configuration (dd-mm-yyyy). Could anyone help me, please? What should I modify?

Thanks!
 
Can you tell me exactly where I place the code to rectify the 12 January's please I can't work it out
 
Back
Top