Excel Macro Pop-Up Reminder

Let's try it again. The objective and data are much clearer in this post. Paste this into the ThisWorkbook code module. It will only execute on sheets with a single alpha character for a name, provided the name in the sheet name tab has no leading or trailing spaces. There is no need to change anything in the code for it to work on a user's worksheet. As soon as a user activates their sheet, the code runs.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

You can try adding the string in red font to the statement as shown above. If that does not work, then post a sample of what the data looks like in column BC. Entries in column BC must be date data types for the code to work. Since we cannot see your worksheet, and no file is upladed to work with, the code can only be crafted based on what the user tells us in their posts.

Note that there is also an additional parenthesis ')' after the word Value.

Please Login or Register  to view this content.

But according to your screen shot, the CDate function should not be needed. Excel should automtically make everything in that column a date data type. The code ran without error on a test setup where column BC used the same date format and it produced the desired results. So as a last resort try changing the statement to

Please Login or Register  to view this content.

It might me leading or trailing spaces causing the problem.

Throw all the other stuff away and try this.

Please Login or Register  to view this content.

This should remove the spaces and convert the dates form text to date data types. When working with dates, we have to be sure that they are date data type instead of text. While they look like a date to the human eyeball, Excel has some built in quirks that change them with respect to what the computer sees. For instance, a space in front or after the the date entry automatically converts it to a text string. So when you attempt to do a math transaction with it, you get the Error 13, type mismatch. Best practice is just type in the date and let it line up on its on without any leading or trailing spaces. If the boss want a prettier set of data, then create a separate report for the boss from the finished data. But the database you use to calculate and manipulate data should be as clean as possible with minimal formatting if you intend to use VBA to manage the data.

It is not seeing your date in column BC as a date and I cannot figure out why nor how to fix it without being able to access the worksheet. I am out of guesses so I will drop off the thread and maybe somebody else can figure it out and offer a solution.

MydnyteSyn,

If the Code isn't seeing BC as dates:
Select any cell in Col BC containing a "Date" and then click the Comma in the Numbers tab and see what happens.
If it IS a date, it will turn to a number (e.g. 44,189, which it today's date numerically).
If it is Text that LOOKS like a date, it will simply stay in the same "date" appearance, so you need to convert the text to a number, then reset the Format as whatever date system, you want.
If you want to convert using a formula, select a blank cell on the same row and type the formula =DATEVALUE (BC2), (assuming BC2 is the text data), and copy that down. Press Copy and Paste Special as a value, then replace Col BC with the new Column.
If you want to use a Macro, try this:

Please Login or Register  to view this content.

(And if it is text you need to check any other columns using dates)

Hope this helps

Ochimus

but when I looked at the ribbon bar, it said Accounting

What did it show before you clicked the comma?

Originally Posted by MydnyteSyn

It shows Date before clicking the comma.

Well, now I am stymied. If the data in BC is date data type then the code should not be throwing the Error 13. You need to upload your file so we can do a better analysis of the problem.

No problem here with the removed sheets, but I am having some diffficulty with column BC on sheet A. Excel says it is data type date, but it has the characteristics of a string when tryimng to use it in code. I will have to do some research to figure out how to cure the anomally. Will post back when I have the solution. I don't understand why it changes its characterists as a value derived by formula, but it does.

This was working on your file that was uploaded, but try it again before you try to use it on the actual file.

Please Login or Register  to view this content.

Delete the old code then copy and paste this one into the ThisWorkbook code module.

While Excel is saying that the range is formatted as Date data type, and the displayed valuie looks like a date, shen VBA tries to use that value, it does not automatically convert to a long integer value because it is a copy of a value from another sheet and the underlying value of the cell is a formula. So, VBA sees the value as a variant value or string. That means that the value has to be converted to something that vba will recognize as a valid date so it can do the math in checking the due dates against the current date. The DateSerial method provides the means to do that. CDate did not work in this case and I am still trying to understand why that did not work. But in any case, it shoiuld now do the math and produce your notices of courses due.

This also seems to work OK on your file. I thought I had tried this earlier, but apparently not. It is more streamlined than the other, but you can choose which one to use.

Please Login or Register  to view this content.

Are you still there? Did either or both in post #20 work?

Originally Posted by MydnyteSyn

I'm here So I tried that second code you provided in the above post and it produced the same Run-time error 13 mis-match in it. When I clicked on the Debug, it didn't like this part of the code:

Please Login or Register  to view this content.

So I removed all of the code and tried it with the first one you provided. Seems to be working perfectly! Thank you so much!

Working with dates can be a real pain in the neck at times because of the way Excel treats them in differing conditions. So when we use them we need to make sure that they are seen as Date type and not Variant or String type. This was one I had not encountered before and it took me a while to figure out what was Not happening. Glad one of them worked for you.
Regards, JLG