Warning pop up for expired dates

Hello

I have a workbook with many sheets and many userforms . I call all the userforms from the first sheet calling "Application"

In sheet 13, starting in column BU i have a "table" of data.

What i need is when i open my workbook a pop up message should appears showing to me all the data of that table that date of column BX, will expired in next 7 days and another one that will show the data that will expire in next 15 days.

I found this that does what i need but unforunately i am not able to modify as per my needs.

http://www.excelforum.com/excel-prog...te-pop-up.html

Thanks in advance for any assistance here.

See if the attached is any help.

Thanks to both of you.

Mr Norie. This does exactly as i want the job.

Unfortunately in my real workbook(which is EXACTLY as my sample) gives me an Error

"Run-time Error "9"
subcript out of range.

http://screencast.com/t/8kOdFIGqo

even if i try to open it with a button OR pressing F5 from the userform module.

Any idea why??

kvsrinivasamurthy

Thank you but Mr Norie's way is the one that i want to go. Do you have any suggestion for a way like that?

I created a table from the data on sheet '13' and that's used in the code.

I think the problem is because you don't have that table.

I used a table to make things a little easier but the code can easily be changed to use the data as it is.

I just noticed some data in columns A:C, in sheet 13. Is this what you mean?

But even the answer is yes, i am not able to understand how this works.

..but the code can easily be changed to use the data as it is.

Have you the time to work with this pls?

Also is there a possibility to don't work because i get the data in sheet 13 using an advanced filter?

I didn't add any data.

I removed the filter and converted the data to a table, just to make it easier to code.

It should be straightforward to adjust the code to work without table, but if the data is going to be filtered then that's going to cause problems.

Any filter would need to be removed so all the data is shown.

Maria.

You had some typed data in your sample workbooK(See your first attachment).

What Norie means is that he created a table with your data in sheet 13.

See in the link below how you can create a table.

http://www.excel-easy.com/data-analysis/tables.html

I don't know if you have any other data in sheet 13 and if you'll have any other problem(for example because you get your data using an advanced filter) but Norie's solution works great if you make a table there..

Thanks Mr Fotis.

Ok i am an idiot!! I hadn't understand what Mr Norie told about Table)...

Yes now it works(Thank you Mr Norie!!) in my real workbook too BUT looks that Table does not works together with advanced filter(as you mentioned!!) so that table does not include any new data that i add.

So Mr Norie can you pls offer a solution without to use a Table?

I was expected something like that....

Let's hope that Norie or someone else will be able to offer a suggestion for this!

Do you need the filter?

Hoping that i understabd well your question.

No sir. I don't need an Auto filter in my range as my first attachment.

http://screencast.com/t/ypEae255oqv

I saw the autofilter in your attachment

http://screencast.com/t/aBiPB7h4b

and thought that is a part of your solution.

Only to remind that i get these data using an advanced filter. I hope to reply correct in your question.

Thank you for trying to help me.

Sorry I'm confused.

I didn't put any filter on the data, and the code I created doesn't either.

The filter was in the original workbook and I had to remove it to so I could access all the data.

What do you mean you get the data using an advanced filter?

I am comfused too so i upload a new sample workbook which is exactly as the original.

I use this code to get my results(i show to my sample too)

Please Login or Register  to view this content.

I hope everything to be ok now!

See the attached, it works without using table.

Thank you again.

In my real workbook i get only results for ALL when i open the userform but this includes range AU:BZ of my sheet.

http://screencast.com/t/bkKZgYtYeht

If i click on 7 or 15 days option i get an error..

http://screencast.com/t/1cqXV93R

i am sorry if i am a pain for you but i just say what happens...

How are you transferring things, ie the code and userform, over to the real workbook?

Can you upload an example of the real workbook, without any sensitive data, with the userform and code in it?

i copy your userform in my workbook as it is.

Here it is

Use userform 59_2 to get your data in BU column.

Userform 100 is your userform.

Try this, I've tested it on your last uploaded file and it seems to work OK.

We are near...! I am feeling.. Thank you so much!!

Works for the ALL option, but when i choose the other 2 options i get 1 result and not 2 as should be the correct result. Pict is from the orig workbook that i uploaded.

http://screencast.com/t/m8Zq2Rpxqr

Isn't it the due date you want to check?

Yes. Column BX.

I have 2 dates(30/03/2014 AND 31/03/2014) there that must appear.

14/3/2014 20691616-7 Eurobank 30/03/2014 c 1723
29/1/2014 00003423-1 Πειραιώς 31/03/2014 c 2704,62

and i see only the second one as i show in the picture.

Are you sure those are 'real' date values?

Usually date values, like numbers are aligned to the right not the left.

Remove this line from the code.

Please Login or Register  to view this content.

You are right BUT even if i have real dates(look at the formula bar) i get the same result.

http://screencast.com/t/dGXcRmEEjs

Originally Posted by Norie

Remove this line from the code.

Please Login or Register  to view this content.

Yes yes yes!! That is!!! Thank you so much!!!

When and if have the time 2 more questions.

If i want to create another user form -let's say using the data in range BB:BH, which part of the code must modify?

If i want when i open your userform to get as results the 7 days results and not the ALL, what i have to change there!!

Thanks again so much!!

What would this other userform do exactly?

To have the data filtered for 7 days when it opens add this to the bottom of the Initialize sub.

Please Login or Register  to view this content.

..What would this other userform do exactly?

EXACTLY the same job as the one that you offered me. The one that you offered is for the import checks and the one that i want to create is for the export checks.

In range BB:BH the due date column is the BE column.

I just arrived home so i am not able to test your suggestion for 7 days default but i am sure that will work!

Does it need to be another form?

Good morning(08:45 am here!)

Please Login or Register  to view this content.

Of course this works great!

..Does it need to be another form?

I believe so because this report is different from the other(in fact completely oppsite).

But if you have something else in your mind i am "open" to see it!

.and adding 1 more question, is it possible to see the headers in userform?

I thought you said this new form would do exactly the same as the existing one?

I was thinking of either having something on the form where the user could pick import/export and the listbox would be populated based on that choice.

As for headers, we could have them in the listbox but it would mean losing the main functionality of the form.

I do have an alternative option though - a small listbox located just above the main listbox.

This listbox would contain the headers.

..I thought you said this new form would do exactly the same as the existing one?

YES. But the results are different as we'll use another range.

Range BU:..is a report for the checks that our suppliers should pay to us.

The other one that i need to built getting the results from range BB:BH, will be a report for the checks that we have to pay to our suppliers.

EXACTLY same logic for different reason.

That's why my question is which part of your code should modify to get the results that i need in different userform and also because i want to learn how to do this alone. Perhaps a month later to want to create such a userform from a different range too...

I did a new listbox for the headers and works great. Thank you.

Maria

Change the range in this part of Norie's code and tell us what happens.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Fotis

Why AU?

The OP says the 2nd range is for columns BB:BH.

Originally Posted by Norie

Fotis

Why AU?

The OP says the 2nd range is for columns BB:BH.

Right Norie

I did some tests and i used range AU of yesterdays Maria's workbook and sees that works so i posted the suggestion for this range. Apologize.

The important is: Does is correct on NO? I ask you because everybody knows here that my VBA skills are limited!!

I'M sure using AU will work but the OP did specify BB:BH.

I suppose we'll just need to wait for the OP to post.

I really feel to be an idiot!!

What can i say..??

Mr Norie thanks for your solution and your patient!! I really appreciate this!!

Mr Foti. You are my angel!! Thank you very much for all your help that you offer to me!!

Mr Norie, the system does not allows me to give a rep! I'll ought it to you!!

I'm totally confused now - is the range for columns BB:BH or is it AU:AZ?

Mr Norie you are right.

I asked for range BB:BH.

I tried in range AU:AZ as Mr Fotis suggested and works great. So i believe that will work also foR range AU:AZ as i asked. I don't believe that will have a problem as i am not able to tst it just now. Do you believe that i'll have problem with this?

There appears to be a lot more data in columns AU:AZ than there is in BB:BH.

Also the 2 sets of data don't seem to match.

Since it's not my data I don't know if that's a problem.

Originally Posted by MariaPap

I really feel to be an idiot!!

What can i say..??

No worries my child!!

You are just too young!! Keep trying!! You do good job.

By the way:Norie's Code will work for range BB too. I have tested it!