Pop Up Excel Calendar VBA CODE
 

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

Pop Up Excel Calendar VBA CODE
 

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" wrote:

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

Pop Up Excel Calendar VBA CODE
 

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" wrote:

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

Pop Up Excel Calendar VBA CODE
 

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

"Pete" wrote:

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

Pop Up Excel Calendar VBA CODE
 

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

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

Pop Up Excel Calendar VBA CODE
 

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.

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"

Pop Up Excel Calendar VBA CODE
 

"Rick Rothstein \(MVP - VB\)" wrote
in :

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

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


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

Pop Up Excel Calendar VBA CODE
 

?B?TWlrZSBI?= wrote in
:

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

"Pete" wrote:

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


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