How do you combine code that requires different Sub Worksheet actions?

Hello again all. I have 3 different Sub actions I need to combine. I am using:

Please Login or Register  to view this content.

in ThisWorkbook. This allows me to select the next blank row in the designated sheet which is populated with various dropdowns and cell validations using the following code in the specific sheet:

Please Login or Register  to view this content.

I am trying to add in a pop-up calendar from the forum using the following code:

Please Login or Register  to view this content.

The problem is I can not merge the calendar and sheet code. If I add in the calendar code under the

Please Login or Register  to view this content.

, it does not work. If I change the

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

, the original sheet code does not work. I tried to add the calendar code in a seperate block after the original sheet code, but that did not work either.

I also tried variations in ThisWorkbook.

Ideally, I want the calendar to pop-up in every date field on several sheets so ThisWorkbook may be the best place to add it if possible.

Any help would be appreciated!!!

Thanks,
Andrew

Use the Workbook_SheetSelectionChange event in the ThisWorkbook module. (not really clear on why you were trying to merge the other codes together though?)

This could probably be cut down.

Please Login or Register  to view this content.

to this

Please Login or Register  to view this content.

You don't need all that other code just to select a cell, except for the enable events if you don't want the Selection_Change to activate right away.

I can't do that unless I know what the problem is. They are separate events after all.

I can, but it will probably not be before Monday as it's late Friday afternoon now...

You can place this in the ThisWorkbook module to handle all the Calendar popups on all sheets. The way this works is it examines the numberformat of each cell as you select them. I've already identified 3 different DATE numberformats in your sheet and added code to popup for those date formats. If you have any other date formats, just add them as needed.

Please Login or Register  to view this content.

Be sure to remove all the code in the sheet modules that do calendar popups.

Here's another version that uses the common string "d/y" which is in all your date formats so far and it seems to work, too.

Please Login or Register  to view this content.

1) I don't see the merit in opening sheets and making selections, does that accomplish something specific for you? In general, selecting is something you edit out of VBA.

2) The code you're using...with all these sheet-event macros, you might want to add this to your wb_open macros

Please Login or Register  to view this content.

Thanks JBeaucaire. I was using that code in the ThisWorkbook module since it seemed I needed it to allow the dropdowns to be populated by the sheet code. I removed it and used your code and the dropdowns are still populating, but the dates are not. I have attached my latest workbook

Andrew

Ok...reformatted all the date columns on both sheets and now the dates seem to be working on both sheets!!!

Another problem I noticed is if you enter text in a column that is not validated in any way, the cell automatically gets re-selected instead of moving to the next cell, which in my case should be the cell in the next column. Any thoughts why this is occuring and how to correct it?

I also saw where you created a Modeless calendar and provided for the Esc key to close it in post: http://www.excelforum.com/excel-prog...ast-dates.html and I would like to do the same thing if possible. Been trying to work in your code from that post but no luck so far.

Thanks again,
Andrew

The modeless calendar uses a hidden control that is hidden on the form, but hidden behind the calendar itself. Toggle through the controls and you'll see one that appears to be behind the calendar, check the properties on that control.

Give me the link to the post where you got that and I can help, I remember doing it just not in my brain exactly how.

Use a button and set its Cancel property to True. Then the button click code just needs to hide the form.

Do you have a button called cmdClose and is its Cancel property set to True? Both are necessary.

In case it's not clear, this is what is required along with the code you already found:

If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.

Take out this line of code in your ws_change macro:

Please Login or Register  to view this content.

I would rewrite the whole column I data validation like so:

Please Login or Register  to view this content.

That line is missing an 'Is Nothing' - it should be:

Please Login or Register  to view this content.

Change this:

Please Login or Register  to view this content.

...to this:

Please Login or Register  to view this content.

...or this:

Please Login or Register  to view this content.

FYI, your code really ought to be redone using loops in case more than one cell is changed at once. If you copy a row (or a few cells from row to row), it will cause an error.

RS is right, something like this:

Please Login or Register  to view this content.