Forum Discussion
Visit Planner
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
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 SubHope this resolves your query & don't forget mark as Best Response if your query is resolved.
8 Replies
Alternatively, if you can live with a solution that does not require VBA, just enter in cell N3
=H3and 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.
- i tried what you said but seems dont work, is there anything i missing to do.
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.
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 SubHope this resolves your query & don't forget mark as Best Response if your query is resolved.
Thanks a ton ExcelExciting. 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!
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
