Popup window using VBA Excel 2010

[mdeza81090]

I am completely new to VBA and do not really know how to manipulate a code.
I was hoping someone would be willing to formulate the codes I need to input into the VBA box.

I have a sheet for appointment dates at a clinic.
They have dates for each patient as below:

Name First Visit Appt 1 2nd Visit Appt 2 3rd Visit Appt 3 .... 10th Visit
x x/x/x x/x/x x/x/x x/x/x x/x/x x/x/x x/x/x
x x/x/x x/x/x x/x/x x/x/x x/x/x x/x/x x/x/x
x x/x/x x/x/x x/x/x x/x/x x/x/x x/x/x x/x/x

I would like a reminder to pop up everytime the appointment date is past 7 days and the patient has not shown up.

example: if today's date were 10/9/15

Name First Visit Appt 1 2nd Visit Appt 2 3rd Visit Appt 3 .... 10th Visit
A 6/3/15 7/4/15 7/4/15 8/16/15
B 9/10/15 10/10/15
C 7/10/15 8/11/15 8/11/15 9/15/15 9/15/15 10/1/15

Patient A was last due on Aug 16, 2015 for her 2nd appt (3rd visit), but it has been close to two months since that appointment.
Patient B is OK, so no popup is needed
Patient C is also past 7 days from her latest appointment date.

I would like a popup window to show up for patient A and patient C with a text of "call to remind".

And if possible, disable specific popups (for example, I see reminder for patient A, and I remind the patient once, so there is no need for me to see that patient A needs a reminder multiple times)

What would be the best way of doing this?

I am aware that as the excel gets filled up with more patients, there will likely be more patients who need reminders, therefore more popup windows when the excel is first opened.

Any help would be much appreciated.
Thank you in advance, and please let me know if there is any other info i should provide.

 

[My Aswer Is This]

So I look in column "A" for the patients name. Then I look in the last column with data in that row and check that date. Would that be correct. If not the last column with data which column would I look.

I cannot check all the columns you mentioned earlier because some of those dates will be from previous visits already past.

That would be true correct? So how do I know what column to look in for a date which represents next scheduled date.

And I need a Sheet name where I can enter these names.

You will need to add this sheet.

 

[My Aswer Is This]

Assuming the answers to my questions in post #11 are yes try this script for a test.

You need patient names in column "A" on a sheet named "Master"

For now since I do not know the sheet name with all your data.

Now you need to create a sheet named "Overdue"

Run this script and see if it does what you need.

If it works I will tell you how to have this script run every time you open your "WorkBook".

Sub Patients()
Application.ScreenUpdating = False
Dim i As Long
Dim Lastrow As Long
Dim Lastrowa As Long
Dim lastColumn As Long
Sheets("Master").Activate
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
Lastrowa = Sheets("Overdue").Cells(Rows.Count, "A").End(xlUp).Row
Sheets("Overdue").Range("A1:B" & Lastrowa).ClearContents
Lastrowa = Sheets("Overdue").Cells(Rows.Count, "A").End(xlUp).Row
    For i = 1 To Lastrow
        lastColumn = Cells(i, Columns.Count).End(xlToLeft).Column
            If Cells(i, lastColumn).Value < Date - 7 Then
                Sheets("OverDue").Cells(Lastrowa, 1).Value = Cells(i, 1).Value
                Sheets("OverDue").Cells(Lastrowa, 2).Value = Cells(i, lastColumn).Value
                Lastrowa = Lastrowa + 1
            End If
            
    Next
    Sheets("Overdue").Activate
Application.ScreenUpdating = True
End Sub

OK

 

[mdeza81090]

Thank you very much for the response.

It must have been very difficult for you to make the code for me without the actual file...
Is there a way I can send you the actual file?

I tried inputting the code into the VB editor, and got some numbers in the newly created "overdue" sheet,
but i have no idea what those numbers mean...

as an extra piece of information, the patient names are in cloumn C, starting a C10 ending at C108 (for now).
A better way might be to have the patient names show up if the numbers in a certain column are greater than 7.
I have columns named "Days Past Appt Date". This compares the appt date and the date that the patient showed up.
If the patient showed up on the appt date, the number in the column is zero.

Columns I want to check: columns N, T, Z, AF, AL, AR, AX, BD, BJ, BP, BV, CB
This shows up incolumns N, T, Z, AF, AL, AR, AX, BD, BJ, BP, BV, CB
As for your comment below, there is no overlap within the above coumns, so there is no worry.
for example, If the patient A (name in cell C10) is overdue by more than 30 days, "30" shows up in cell N10.
After that, the other cells (ie. T10, Z10, AF10...) to the right are blank.

I cannot check all the columns you mentioned earlier because some of those dates will be from previous visits already past.
That would be true correct? So how do I know what column to look in for a date which represents next scheduled date.


Sheet name: 集計表
sorry it is in a diff language. You can keep it as "Master" and I will change it later if that is better for you.



I hope this helps?
Thank you again.

 

[My Aswer Is This]

See the problem we have trying to help people here is when you do not give specific details. From looking at you original image I assumed patient names were in column "A" now you say they are in Column "C"

The only way I can search for dates is to look in the last column with data in each row.

Try this script and see if it works.

It should enter the patients name and the appointment date in sheet named "Overdue"

Sub Patients_Two()
Application.ScreenUpdating = False
Dim i As Long
Dim Lastrow As Long
Dim Lastrowa As Long
Dim lastColumn As Long
Sheets("Master").Activate
Lastrow = Cells(Rows.Count, "C").End(xlUp).Row
Lastrowa = Sheets("Overdue").Cells(Rows.Count, "A").End(xlUp).Row
Sheets("Overdue").Range("A1:B" & Lastrowa).ClearContents
Lastrowa = Sheets("Overdue").Cells(Rows.Count, "A").End(xlUp).Row
    For i = 10 To Lastrow
        lastColumn = Cells(i, Columns.Count).End(xlToLeft).Column
            If Cells(i, lastColumn).Value < Date - 7 Then
                Sheets("OverDue").Cells(Lastrowa, 1).Value = Cells(i, 3).Value
                Sheets("OverDue").Cells(Lastrowa, 2).Value = Cells(i, lastColumn).Value
                Lastrowa = Lastrowa + 1
            End If
            
    Next
    Sheets("Overdue").Activate
Application.ScreenUpdating = True
End Sub

 

[mdeza81090]

Yes, I apologize for not giving you the info necessary.

I tried the code, and I dont think it worked.

On the "Overdue" sheet, i see that the patient's name was transferred and shown in Column A.
The thing is, I got ALL the names, and only 11 out of the 26 patients are overdue. ]

Also, on Column B, i got numbers and not dates. I dont really know what the numbers mean...
I tried formatting the cell to show date, but that did not make sense either.

 

[My Aswer Is This]

One thing remember you said overdue by more then 7 days?
The data in the last column of each row is the data the script looks at.

so if in Row (12) you have

Bob in column "C" and 12/15/16 in the last column in that row with data for example column "X" that is the column the script looks at for a date.

If what I'm doing is wrong let me know. The date we need should be in the last column with any data.