autocalculating due dates based on frequency (weeks)

[MDisbrow]

Hello Excel Gurus!
First time poster here
I am trying to set up a spreadsheet for a psychiatric provider to use to track when clients need to be seen. Ideally, I would like it to be the last name, first name, frequency, last seen date, needs to be seen date. I know how to do the basic last seen to next seen (B2+30) however, that would require the provider to change the formula if a client needs to be seen every 2 weeks versus 4. She struggles with technology so I was hoping there is a way she could just update her "last seen date" and choose a frequency of 1 week, 2 week, 3 week, or 4 week to auto-populate the new "due date" for the client to be seen next. I have attached a screen shot of the spreadsheet so far but need help please!

*Note--pretend I know nothing about excel in your response as I know very little*

Thank YOU!

 

[mumps]

Click here to download your file. Enter the data starting in column A. In column C, enter only the number of weeks, so for three weeks, enter a 3. When you click in column D, a calendar will pop up for you to select a "Last Seen" date. After the date is selected, the due date will populate automatically. To view the code, do the following: right click the tab name for your sheet and click 'View Code'. Close the code window to return to your sheet.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    If Target.Column <> 4 Then Exit Sub
    Target.Offset(, 1) = DateAdd("ww", Target.Offset(, -1), Target)
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Intersect(Target, Range("D:D")) Is Nothing Then Exit Sub
    CalendarFrm.Show
    Application.ScreenUpdating = True
End Sub

 

[MDisbrow]

Set up your sheet to look like this:

Book1
	A	B	C	D	E
1	Last	First	Frequency in Weeks	Last Seen	Due
2
3
4
5
6
7
Sheet1

Copy and paste this code into the worksheet code module. Do the following: right click the tab name for your sheet and click 'View Code'. Paste the code into the empty code window that opens up. Close the code window to return to your sheet. Enter the data starting in column A. In column C, enter only the number of weeks, so for three weeks, enter a 3. When you click in column D, a calendar will pop up for you to select a "Last Seen" date. After the date is selected, the due date will populate automatically.

Which code and I copy and pasting?

 

[mumps]

Sorry. I revised my post. Please check again.

 

[MDisbrow]

Sorry. I revised my post. Please check again.

Thank you! It worked perfectly!

 

[mumps]

You are very welcome.

 

[MDisbrow]

You are very welcome.

One more question.. how do I insert rows? When I try it just pops up the calendar window.

 

[mumps]

Try replacing the existing part of code with this:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    If Intersect(Target, Range("D:D")) Is Nothing Then Exit Sub
    CalendarFrm.Show
    Application.ScreenUpdating = True
End Sub

 

[MDisbrow]

Try replacing the existing part of code with this:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    If Intersect(Target, Range("D:D")) Is Nothing Then Exit Sub
    CalendarFrm.Show
    Application.ScreenUpdating = True
End Sub

Which part of the code do I replace:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.CountLarge > 1 Then Exit Sub
If Target.Column <> 4 Then Exit Sub
Target.Offset(, 1) = DateAdd("ww", Target.Offset(, -1), Target)
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Intersect(Target, Range("D:D")) Is Nothing Then Exit Sub
CalendarFrm.Show
Application.ScreenUpdating = True
End Sub

 

[mumps]

The second macro which has the calendar.