Visit Planner

[rakeshtalwar]

Hello People,

I am trying to make an visit planner.

I have 60 clients whom I have to meet after every 6 days. So, i have created a list of my customers and have created dates column for next three months. Now when ever i meet my client i tick mark on the date row against their name, now whenever i tick mark the clients whom i met excel should automatically tick mark the next 6th day of my meeting hence i would know i have to meet these clients on that particular date.

Please help me with this.

Regards,
Rakesh Talwar

[#2 · Accepted solution]

Hi @rakeshtalwar 

Please see the attached file, when you double click on the cell It will tick mark after 6 days. One slight change I have made instead of entering the tick icon I have replace the value with "a" and the font remains the same "Webdings"

Below code can be used in the worksheet module:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
'Coded By : Faraz Shaikh - www.ExcelExciting.com | 24.02.2020
Dim rowNo, colNo As Integer
    rowNo = ActiveCell.Row
    colNo = ActiveCell.Column
    i = ActiveCell.Value
    
   Do Until IsEmpty(Cells(2, colNo))
    Cells(rowNo, colNo).Select
        ActiveCell.Value = i
        colNo = colNo + 6
   Loop

    MsgBox "Task Completed", vbInformation, "Faraz Shaikh | ExcelExciting.com"
    
End Sub

Hope this resolves your query & don't forget mark as Best Response if your query is resolved.

Regards, Faraz Shaikh | MCT, MIE, MOS Master, Excel Expert | www.ExcelExciting.com

[#3 · Accepted solution]

Regards, Faraz Shaikh | MCT, MIE, MOS Master, Excel Expert | www.ExcelExciting.com

[#4]

@rakeshtalwar 

Alternatively, if you can live with a solution that does not require VBA, just enter in cell N3

=H3

and copy it down and across. Just place your tick mark when you visited your customer and whichever cell comes 6 steps to the right will also get a tick mark to indicate your next visit. Should you have missed or rescheduled a visit just remove the tick and place it where you did visit the client. After your 3 month period is over, do the same again.

[#5]

Thanks a ton @Faraz Shaikh. however, i am not able to use it. when ever i double click any cell it goes till the end and says Task Completed but there arent any tick mark happening.... 

While opening the file you attached it gives me couple errors. snapshopts attach with this reply. Please help

However, Thank you for your effort and help appreciated!

[#6]

i tried what you said but seems dont work, is there anything i missing to do.

[#7]

@rakeshtalwar Have a look at the attached workbook. The only problem I have with it that I get strange symbols. I'm on a Mac, so that could be the cause of it.

[#8]

Gottcha! @Riny_van_Eekelen 

Thanks a ton this was that simple.... he he ;)

Regards,

Rakesh 

[#9]

hi @rakeshtalwar,

After seeing the screenshot look like your macro is disable you need to enable to run the code. Try enabling the macro and then double click on the cell

Regards, Faraz Shaikh | MCT, MIE, MOS Master, Excel Expert | www.ExcelExciting.com

[#10]

Yes Faraz, I did that in spite it give me the trouble. let me tell you that i have office 2007. 

Anyway thanks for your quick reply.

Rakesh