Can a Message box be brought up in the middle of code asking for criteria?

[duugg]

Hello all,

I have a worksheet with several columns (30+). One of these columns is "Date Opened". This "date opened" column can have dates as early as 01/01/89 and as recent as today.

Can a macro be made that does this

1. Data/Sorts the whole worksheet by the "Date opened" column
2. Brings up a message box in xx/xx/xxxx where format saying "Please enter the earliest date opened you would like to use, all other rows will be deleted"
3. Do just that, delete all rows that have a date earlier than the date entered in the message box.

So if the date of 01/01/2009 were entered into the message box, the macro would delete all dates in the "Date Opened" column that were older than that date.

And then, continue on with the rest of the sub, if neccessary.


thanks,

duugg

[protonLeah]

Please Login or Register  to view this content.

[duugg]

proton,

Sorry, but Data/Sort didn't work nor did a message box come up. Also, I could be wrong but it appears that the code points to column A. the "Date Opened" column could be in any column letter, not just limited to column A.

thanks

[protonLeah]

My mistake, you didn't specify a column for the "Date Opened" so I just used column A with some random date values entered. So:

duuggzdates.xls

[duugg]

ProtonLeah,

Sorry, the message box did pop up but that was it. I entered the date and nothing happened. All dates older didn't delete, data wasn't sorted either. Also, I intentionally moved the "date opened" column to another column and got the error

"Run-Time Error 1004" Sort method of range class failed"

Also, this macro should run until it finds the first blank cell, once it does, it can stop.


thanks

[protonLeah]

This one works but you will have to specify the rows and columns in the macro.

Please Login or Register  to view this content.

duuggzdates.xls

[duugg]

Hello protonleah,

Hmm, I tried it, putting all the dates starting at column A, row 2. Column name was named "date opened".

After running the macro, (putting a date in the input box that was sure to be in between the oldest and newest date) it deleted ALL the dates, leaving only the column header, that's it.

I'm not sure if this matters, but the cell format for this date field is the following (designated by the export of the report) is like this....



(This is from right-clicking on any cell with the date in it, and select "format cells")

from the Number Column..

Category is - Date
Type is - *3/14/2001
Sample is - 07/20/2006


So, dates in January appear as "1/01/2009"
dates in October appear as "12/01/2009"


Can the input box be "smart" enough to know that if a person enters in
"01/01/09", the macro knows it really means "01/01/2009"?

Can the input box be "smart" enough to know that if a person enters in
"1/1/09", the macro knows it really means "01/01/2009"?

Can the input box be "smart" enough to know that if a person enters in
"12/1/2009", the macro knows it really means "12/01/2009"?

thanks much

[protonLeah]

O.k. my fault (again ). The format of the cell really doesn't matter in this case since XL stores the dates as integers (current dates are around 38 - 40K).

I had thought that you wanted to delete ALL dates not matching the date entered into the msgbox.

So, if you entered a date that was not in the list, then all dates would be deleted.

To delete only the dates older than the msgbox date change the following code:
Delete or comment out

Please Login or Register  to view this content.

Replace with:

Please Login or Register  to view this content.

That should delete only dates older than the test date even if that particular date is not in the list.

[duugg]

protonleah,

Yes! this works great! Thanks! You've gotten Kudos from me!

Is there any way at all change this code to look for the column named "Date Opened" rather than forcing it to be in column A?

If not, I can try and work around it.

Thanks again!

[protonLeah]

Are you saying that the words "Date Opened" can be in row one of any column??

I still don't know the layout of your sheet (that code was written blindly); however, it is easily mod'd to accommodate your actual sheet ( I think). Just go to the line:

Please Login or Register  to view this content.

and replace the "A" with your actual working column. DateCol = "Q", or whatever.

[duugg]

Hi protonleah,

What I'm saying is that every time I run the report, I NEVER know what column letter the "Date Opened" column will be in. So, I would like the macro to search for the column named "Date Opened" rather than a fixed column position.

thanks,

duugg

[protonLeah]

Ok. try this one:

Please Login or Register  to view this content.

[duugg]

What else can be said but

You've have made it happen...perfectly!


Thanks much!!