Feb 24 2020 04:44 AM
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
Feb 24 2020 06:23 AM
Solution
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
Feb 24 2020 07:54 AM - edited Feb 24 2020 08:00 AM
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.
Feb 25 2020 06:12 AM
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!
Feb 25 2020 06:13 AM
Feb 25 2020 06:18 AM
@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.
Feb 25 2020 07:14 AM
Feb 25 2020 11:53 AM
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
Feb 25 2020 08:38 PM
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
Feb 24 2020 06:23 AM
Solution
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