Calculating multiple occurances in one cell

OK I have included the spreadsheet I am working with and I will try really hard to explain exactly what i want and where my problems lie, so please bear with me.

The work book is separated by months. The individual spreadsheets can have multiple entries in each cell, I have a code running to automatically pop up a comment text box for each entry into that cell to separate each occurrence.

Now I have a separate sheet that will be showing how many times each item has a "hit" or occurrence for the entire month. That's where I have the problem. I don't want to go and count every time i made an entry, excel should do that. The problem I am coming up with is that I can only get it to calculate the one occurrence per cell which does me no good if there were three separate occurrences in that cell.

Can anyone help. Thank you in advance.

Where in your workbook is the formula that is returning only the one entry (this will make it easier to determine which cells you are analysing)? Are you looking for a formula solution (I am presuming so)?

Ah OK so you're counting the number of entries in the comments in that line? That will take VBA to calculate (as I'm sure you know). Also, it probably isn't that easy to track how many times changed (or rather, it would be difficult to track whether a change was a genuine new entry, or just a modification of an existing one).

Do your comments always have the exact same structure ie User making the change in bold then on a new line the body of the comment (you wouldn't for example have a single entry which might span several lines - these lines created by pressing the Enter key in the comment, not just because when viewed it spans several lines)?

The entries are always in this way:

1. User enters how many hours need to be reduced and hits enter
2. Text box appear with users name in bold ready to type cause
*if the text box is dragging on for a long time, user will hit enter during that entry

If another occurrence happens the process starts over, the users have been instructed to always enter the hours first so that the text box opens again with the users name for the new entry, therefore separating the occurrences.

I was hoping that because the code is written to account for the separate occurrences that way that the code could be written to calculate how many times a new number is written but I understand what you are saying about "genuine new entry" because as soon as you change the number in the cell it's a new entry.

Also, let's say the default number is 24 and you reduce it to 23, the text box appears, you enter text and move along. Well then you decide that the entry is incorrect, so as soon as that number hits the default number, ie 24 the text will disappear, so i don't know if that would count as an additional occurrence or if it would decide that the occurrence was removed and calculate accordingly.

Hello ladydubs,

I have written a UDF to count the hits. If a cell has a comment, the counts the hits by the number of colons in the comment text. Rankings 1 and 2 have the UDF added on the hit sheet for each month. The other rankings need to be filled in. The hits are updated whenever you select the "Hits" sheet.

There is only one macro now to add or edit the comments. I rewrote it and placed in the Workbook_SheetChange event of the ThisWorkbook code module. Having the macro repeated in the Worksheet_Change event for each worksheet is unnecessary.

All the code shown has been added to the attached workbook.

Workbook SheetChange Event Code

Please Login or Register  to view this content.

Worksheet Change Event code for "Hits" Worksheet

Please Login or Register  to view this content.

Macro Code to Add and Edit Comments in Module1

Please Login or Register  to view this content.

Macro Code for the UDF in Module1

Please Login or Register  to view this content.

UDF Example

Please Login or Register  to view this content.