Calendar Code by LJM

[zplugger]

Had some help on this project and not sure how to fix.The commandbutton will install date in cell with text,this is perfect.I only want one date to show,so if a date is there and button is pushed it will be replaced with new date.

Thanks for any input.

[Richard Buttrey]

Hi,

This is one way

Please Login or Register  to view this content.

[mehmetcik]

In the sheet ClsCmdButton

replace

Please Login or Register  to view this content.

with

Please Login or Register  to view this content.

The existing code changes the active cell

I am changing C16

[zplugger]

Thanks mehmetcik that does the trick. As soon as I open the Calendar the date is deleted,is there a way to
leave the date until I click another date? So the date is not deleted until another is selected.

[zplugger]

Thanks Richard Buttrey, tried you code with Perfect sucess. Not sure if possible to make date all the way to right in cell?

[mehmetcik]

That is not what I get.

Try this:-

[zplugger]

still getting more then one date with book2.xlsm mehmetcik. Richard's code seems to work, just need to work out some more options. Like
click on cell in colum O and have the calendar popup and insert date like commandbutton,if there is a date in colum O already you can change it if needed.

[zplugger]

Ok I got the popup to work in column O OK, but why does my cell size change after installing date? I need the cell to stay the same.

[Richard Buttrey]

Ok I got the popup to work in column O OK, but why does my cell size change after installing date? I need the cell to stay the same.

..because there's an .EntireColumn.AutoFit instruction in your code. Just delete it of you don't need it.

[zplugger]

Thanks Richard for the tip, will look at code to see that. Ran across another calendar by LJM,it is exactly what I need. Can't get it to leave text I type in C16 when I install date.

[Richard Buttrey]

Hi,

See attached. The cell will get the format you want with the previous text and any previous date removes but for consistency all the date buttons on the calendar control should have their

Please Login or Register  to view this content.

code changed to

Please Login or Register  to view this content.

so that the Calendar also displays the date selected in the right format. It's a pity the setting of the label caption wasn't in a procedure of its own then you'd only need to change that rather than all 49 buttons on the calendar.

Note changing the caption is optional. The changes I've made are independent of this.

[zplugger]

This is very close to what I need, I need a commandbutton to control the calendar in cell C16. I have to type text in that cell first then open the calendar to
insert the date after text. The other range for pop up calendar is OK because no text in those cells. Is it possible to use a commandbutton to run the code in C16?

[zplugger]

Not sure why but sometime it deletes the text in C16,other times it works?.Sure it is how I have the CommandButton code.

[zplugger]

Been playing with this for hours and no luck.If I type in 234 elm st and open calendar to insert date it deletes the address. What I'm finding is if a number is in
the text it will delete the text? If I type in text without a number it seems to work perfect. Need to find a way to ignore the numbers in the text in C16.

[Richard Buttrey]

Try the revised attached.

[zplugger]

Hello Richard
Still having same problem,if you have 1234 Elm in C16 and try to install date it will delete the text?

[Richard Buttrey]

Yes, that's because the macro looks for the first number. The working assumption was that there was alpha text followed by a date consisting of numerocs. Hence the macro finds the first number and deletes all that follows.

If the text too contains a digit then you'll have to work out some rule so that the macro can decide which is text and which is a date.

[zplugger]

Ouch, not sure where to begin,maybe have macro ignore the first 10 character?

[Richard Buttrey]

Hi,

I'm in your hands. Only you can say. Will there only ever be one date in the cell and will it always occupy at most the last 10 characters. i.e. dd-mm-yyyy?

[zplugger]

yes only one date in cell

[Richard Buttrey]

Hi,

Try this

[zplugger]

I'm beginning to think this is impossible, still deletes some of the text.Think I created a monster on this one,with my old code it would install today's date without any problems. To have the calendar popup to pick date would be nice but its not working.I guess I need to go back to drawing board and try another approach.I never know the size of address until I type it in,need to find a way the code looks after the last text letter for a date.

[Richard Buttrey]

Of course it's not impossible. It seems to be working as I understand the requirement. I've tested it with various lengths of text strings

Give me a few examples of what is in a cell before you hit the button to add the date and what you see afterwards. The bit of code that works out the result text is very simple.

Please Login or Register  to view this content.

The first line removes the last 10 characters of whatever is in the string (and we'd agree that the date would always be at the end)
The second line adds the calendar date variable sDate which is formatted as "m-d-yyyy" to the st Variable.

[zplugger]

Thanks Richard
If I was going on hunting trip you are the type of guide I would want, no matter what you hang in to the end.You don't raise you voice or get frustrated. Here is a couple of examples I might type in C16,then I need to input date from calendar behind them.[/LIST]
10056 Hunters Creek Drive
R6738 Saratoga Dr
R Basement Twin Pines
F 5678 Waterforn Hill Drive
63222 Kingston Ct

[zplugger]

Getting real close,I change this to 0 st =

Please Login or Register  to view this content.

All the address stays in place,but if I try and change the date it will add it as new date after first date.It does not delete the first date,if I delete the date it will input ok.

[zplugger]

Maybe another button on the sheet that could delete the date only in C16? If this is possible then I could add the code to new button for new date,I could call the button change date.

[zplugger]

Decided to use Two buttons to make this work,seems to do the job. Thanks for all your help,could not of done it without your help.

[Richard Buttrey]

Hi,

I think I may see where we observe different things. The macro assumes that there is already a date at the end of C16 and it always deletes 10 characters. Hence if there's not you lose some of the text. This simple change to the AddDate macro will detect whether the last character in C16 is numeric (e.g. the 6 in say 2016) and deletes the 10 character if so.

Please Login or Register  to view this content.

[zplugger]

Thanks Richard
This new code is Perfect,forum's lucky to half a person like you.

[LJMetzger]

Nice job Richard.

I'm glad you got it working Larry.

If I may add my two cents here:
a. Module mGlobals is no longer needed. It is related to the original Calendar form that you are no longer using.
a. I had nothing to do with the first Calendar Form that uses the Classes.
b. The 2nd Calendar form that you finally used I modified slightly from an original from Phil Johnson at Ozgrid. Thank you Phil.

See the file associated with post #3 of the following thread which contains an updated copy of the Calendar Form which contains the following enhancements:
a. US Date/European Date independence (original was good in US only).
b. Added arrows to increment/decrement by days or with 'Ctrl key pressed, by months.
c. Added Query_Close code to return 'None' when 'x' is selected (top right corner of UserForm).
d. Allows the use of any date as a 'Seed' date. The original could only use the current date as the seed date.

NOTE: The HelpLabel.Caption should remain as is, or European date formats will probably not work.
http://www.excelforum.com/excel-gene...-combobox.html

Lewis

[zplugger]

Thanks for update LJMetzger
Not sure why but Calendar does not work past 2030, when you select a day in year 2031 it goes back to 1900's

[LJMetzger]

Not sure why but Calendar does not work past 2030, when you select a day in year 2031 it goes back to 1900's

See the attached file for the following changes:
a. Expanded the arbitrary years allowed from +/-20 years from current date to +/-40 years from current date.
b. Somewhere in the future, years would be incorrect by 100 years (2035 would be displayed as 1935), because the original form used 2 digit years in a couple of places.
c. Removed unused module mGlobals


In the CalendarFrm UserForm code module (changes in red):

Please Login or Register  to view this content.

The attached file was deleted on January 8, 2016 due to an unwanted feature caused by increasing the number of available years. See post #36 for an updated file.

[Richard Buttrey]

Thanks LJ you've saved me some head scratching.

I'd been trying to understand the calendar code and was thinking the problem was associated with the way the calendar is built by the macro. The calendar layout is a 7 x 6 square, presumably designed like that so that all 28/30/31 days of the month selected can be shown but be bounded on all sides by the relevant dates from the previous or next month. In any case the build macro loops 42 times to create these days.

I'd started thinking about the calendar initialise procedure loop from -20 to + 20 which creates year values in the drop down and initially thought that just changing this loop to say -20 to say +50 would solve the problem. It didn't.

I'd then started pondering the calendar build macro that is used to create both the 42 days for the calendar and was wondering if the difference between the loop counter of 42 and a year list in excess of 42 years was causing the problem.

Both red herrings as it transpires

Well done for finding the problem area

[LJMetzger]

Thanks Richard. I figured I should be the one to do the changes, since I knew where the bodies were buried.

My head scratching was done several months ago when I updated and simplified some of the code so it could be used without UserForm modification in both the US and in Europe. Post #3 in the following link (same as the link in post #30) is that code, which I currently use for myself: http://www.excelforum.com/excel-gene...-combobox.html

Lewis

[zplugger]

Nice work guy's on this coding.

[LJMetzger]

A side effect of the change to the range of dates done in post #32 was brought to my attention that made the arrows underneath the Year inoperative. The following CalendarFrm UserForm code module code had to be changed (changes in red):


Sub SB_Year_Change() was deleted and replaced with:

Please Login or Register  to view this content.

See the attached updated file.

I apologize for the inconvenience.

Lewis

[zplugger]

(I apologize for the inconvenience) Are you kidding LJMetzger. We can't Thank You enough for your time on this forum.

zplugger