Help with an idea

[konskrypt]

Hi all.

I have a spreadsheet that i use to keep track of my sales accounts.
On that sheet I have dates that actions need to be done.
I,m trying to find out if there is a way of either creating some sort of notification, that would appear when i open the sheet, or that could transfer the information to another sheet that i can then use as a to do sheet.
Lets say A1 is the name A2 is the task and A3 is the date its due, is there a way(s) of doing this.

Thanks in advance

 

[MartyS]

I,m trying to find out if there is a way of either creating some sort of notification, that would appear when i open the sheet,

There is an event that is called when a workbook opens.

Code can be placed in here to do various tasks.

Private Sub Workbook_Open()


End Sub

To use this, you would need to put code in the WORKBOOK (and not a MODULE) from the VBA editor.

or that could transfer the information to another sheet that i can then use as a to do sheet.
Lets say A1 is the name A2 is the task and A3 is the date its due, is there a way(s) of doing this.

It is possible to copy or transfer information from one sheet to another sheet - more information would be required...


If this is the Source Sheet:


[TABLE="width: 500"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]Name[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]Task[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]Due Date[/TD]
[TD="align: center"][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Does this data repeat downwards?

e.g.

Name
Task
Due Date
Name2
Task2
Due Date2
Name3
Task3
Due Date3

and so on...?


Is there a criteria for copying / transferring the data over (based on Due Date, or the Name maybe?)

Do you want it copied into a different sheet in the same workbook, or into a different workbook?

 

[My Aswer Is This]

Yes this surely could be done.
You may want to be more specific what you want to happen if a task is due.
If you have 20 tasks in column A that are due do you want 20 message boxes to popup?
Or do you want these 20 rows of data copied to another sheet?
Or have the word "Due" put into column.


Now you said:

Lets say A1 is the name A2 is the task and A3 is the date its due, is there a way(s) of doing this.​

I would think you would have A1 is the name. B1 is the task and C1 is the due date.

Why do you want all your data in Column "A"

What's in column B and C and D ?

​

 

[konskrypt]

Sorry, The Data Is A1 for Name B1 for task and C1 for date due.
What I would like is to have a notification saying that something is due and then that information to open on a second sheet but remain on the original.

 

[My Aswer Is This]

Try this and see if this will do what you want.
You get a message and a new sheet added to your workbook named todays date with the due items.
Put all your current data on a sheet named "Master" or change the sheet name shown in the script. Marked in Red.

Put this script in a button and run it by pressing the button. Later if needed we can fix it to run automatically.

Sub Check_Dates()
Application.ScreenUpdating = False
'Date Modified 2-20-18 5:05 PM EST
Dim i As Long
Dim ans As String
Dim r As Long
Dim mss As String
Dim Sn As String
Sn = "[COLOR=#ff0000]Master[/COLOR]"
Sheets(Sn).Activate
Dim Lastrow As Long
mss = "These Task are overdue"
i = 0
Lastrow = Sheets(Sn).Cells(Rows.Count, "A").End(xlUp).Row
Sheets.Add(After:=Sheets(Sheets.Count)).Name = Format(Date, "MMM-DD-YYYY")
    For i = 2 To Lastrow
        If Sheets(Sn).Cells(i, 3).Value < Date Then
            ans = ans & Sheets(Sn).Cells(i, 1).Value & "   " & Sheets(Sn).Cells(i, 2).Value & "  " & Sheets(Sn).Cells(i, 3).Value & vbNewLine
            r = r + 1
            Sheets(Sn).Rows(i).Copy Sheets(Sheets.Count).Rows(r)
        End If
    Next
MsgBox mss & vbNewLine & vbNewLine & ans
Application.ScreenUpdating = True
End Sub

 

[konskrypt]

Thats great, thank you.
Ive put the code into a vba module and then run it.
The notification comes up and the second sheet opens but there is no information in it?
Can i send you a sanitised version of my sheet to look at?

Thanks

 

[My Aswer Is This]

Were there dates shown in the Message Box showing overdue task?
The script looks for dates less then today in column "C" of sheet named "Master"

 

[konskrypt]

No dates showed. The dates on my sheet are in column "G".

Sorry thought I could ask for help and then change the numbers etc to suit

 

[Fluff]

Cross posted https://www.excelforum.com/excel-programming-vba-macros/1221076-newb-help.html

Cross-Posting
While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

 

[My Aswer Is This]

No dates showed. The dates on my sheet are in column "G".

Sorry thought I could ask for help and then change the numbers etc to suit

Why in post 4 would you say:

The Data Is A1 for Name B1 for task and C1 for date due.​

If the date is in "G"

So I'm not sure how to help you now.

Maybe A is not really A but C etc.

And now not sure what cell values to put in Message.