Adding new field to userform

1.  I need to add an additional column to the Original and RAW_DATA sheets...

Please let me know where (after which column) you would like the new column. I can tell you that the further left it is the more work will be involved to add it.

I also need to know where on the userform the 'Renewal Date' should go. I assume that you want it in the 'Product Information' frame, but where exactly?

Please let me know where (after which column) you would like the new column. I can tell you that the further left it is the more work will be involved to add it.

After the End Date would be great!, but I am flexible on the location just as long as it can be captured.

I also need to know where on the userform the 'Renewal Date' should go. I assume that you want it in the 'Product Information' frame, but where exactly?

under the End Date

In the worksheet code there are literally hundreds of references to specific column letters and numbers and to change them all due to the addition of the new column would be very error prone and so I'd like to add the new column after the 'Concatenation' column. Please let me know if that's okay.

I'd like to add the new column after the 'Concatenation' column. Please let me know if that's okay.

Sure that will be fine

For validations of the  Renewal Date in the userform I propose the following. Please let me know if you need something different.

• Must be a valid date
• Required if Original Term > 12

Is there any relation between Renewal Date and Start Date or End Date?
Is it MMDDYY or MMDD? If the former does it change every year?
You obviously want to see it in the Product Information frame, but do you want to see it in the Data listbox?

Is there any relation between Renewal Date and Start Date or End Date?

Actually there is a relationship but a bit confusing.  I briefly touched on this back in August i believe in one of the messenger messages.  The user form currently  allows for the The start and end date of a quote which could be 4/10/2019 - 3/15/2021 and it's broken down by year/12mos.  

However, for those quotes that have more than a 1 year contract, the renewal agreement will not necessarily fall at the end of  the year. It may pending purchase and negotiation have a different renewal date (i.e.,10/15/2021). I provided below how the user form is setup currently to capture start and end dates and how it should be when it has a renewal date. See below examples:

Currently:                                  Per the Renewal Date:
4/10/2019 - 12/31/2019                     4/10/2019 - 10/15/2019
01/01/2020 -12/31/2020                   10/16/2019 - 10/15/2020    
01/01/2021 - 3/15/2021                    10/16/2020 - 3/15/2021

Because we didn't factor this in initially, I thought it would be easier to just capture when the license is up for renewal and leave the breakdown as is since it will not necessarily have an impact on the dollars from an analysis perspective.

Is it MMDDYY or MMDD? If the former does it change every year?

it should be MMDDYYYY and it will only change if the terms and agreement are renegotiated.  

You obviously want to see it in the Product Information frame, but do you want to see it in the Data listbox?

No I do not think it needs to be added to the listbox

Thanks for the example and I think I understand what you want.

If the start and end date of a quote were 4/10/2019 - 3/15/2021, what date range is valid for renewal date? In other words I assume that the renewal date must be after 4/10/2019, but how much after? Could it be 4/11/2019 (one day after) or does it need to need to be on or after the end date? Or?

If the start and end date of a quote were 4/10/2019 - 3/15/2021, what date range is valid for renewal date? In other words I assume that the renewal date must be after 4/10/2019, but how much after? Could it be 4/11/2019 (one day after) or does it need to need to be on or after the end date? Or?

It will be difficult to determine the renewal date from the start and end date because its not always clear by looking at the period dates. Each Quote  will have the renewal date listed on the PDF  and  needs to be treated  for now separately from the start and end dates.  The user will just need to enter manually into the userform the renewal date and the email notification will trigger based on the start date and the renewal date, but not the end date

Are you saying that the renewal date could be before the start date?

No, it will never be before the start date.  It will more likely be closer toward the end date.

Please verify that at least for now that I can add a validation that forces the renewal date to be on or after the start date.

Please verify that at least for now that I can add a validation that forces the renewal date to be on or after the start date.

Yes, I believe for now that validation is applicable.

Give this a try. I tested with quote Q-00000000.
29164003.xlsm

I updated the workbook that's in my previous post.

The update looks to be working great!  Only issue is the sample quote I entered to run the test does not show up in the first or second search boxes.  However, it does show up in the list frame.

Should I open another question for the below?

Based on the renewal date I would like to be able to to  send from excel via outlook a notification  40 days prior to the renewal date expiring.
3. The notification email is only needed for licenses with more than 12 months showing  as stated in the original term column.

Only issue is the sample quote I entered to run the test does not show up in the first or second search boxes.

Do you mean this?

Yes, the test quote does not show up in the search value box

I just fixed it and I'm looking at something else.

When we get to sending the emails, how should that happen? In other words do you want to press a button to do it or do you want to somehow do it automatically?

When we get to sending the emails, how should that happen? In other words do you want to press a button to do it or do you want to somehow do it automatically?

My preference would be for it to somehow send automatically, and good with having to press a button if not.  I think once we get to this point the renewal should be driven off of the original quote tab sheet.

If my correction fixes the search problem then please close this question.

For the emails I need to understand what "renewal date expiring" means. Let's say that a quote is received and entered today. We agreed that the renewal date could be the same as the entry date so I could make it November 18 2019. In this scenario when would it expire?

Thanks Martin.  Excellent work as always!

For the emails I need to understand what "renewal date expiring" means. Let's say that a quote is received and entered today. We agreed that the renewal date could be the same as the entry date so I could make it November 18 2019. In this scenario when would it expire?

1.  The license expires on November 18, 2019, which is also the same date the contract should be renewed by. Typically, a new contract should be in place a few days prior to the license expiration date to avoid any risks of no longer being able to utilize the tool.
2. Expiry Date would be 40 days prior to the renewal date, which would be 10/9/2019 in this case.
3.  Once the 10/9/2019 is reached an email should be triggered via (auto or button) to send to the owner of that license notifying them that there license (quote) is expiring in 40 days.

But in this scenario it's already past Once the 10/9/2019.

But in this scenario it's already past Once the 10/9/2019.

Ok, I think I see where the confusion is.

Disregard the "renewal date could be the same as the entry date" - Not likely to happen.  I can received the quote today and enter it a couple days later.    No Relationship

Start Date - 11/18/2019
End Date - 11/18/2021
Renewal Date  can be a couple months prior to the End Date or on the End Date.  So in other words if the Renewal Date is on 11/18/2021. and  as 10/10/2021 approaches an email should get triggered on that date.

This is the reason why a manual input is required and renewal date entered based on a separate line item on the quote

In a multi-year quote there may be a problem. Given the following:
Start date 12/1/2019
End date 11/30/2023
Renewal date 4/1/2020

1. I assume then that the email should go out on 02/21/2020. Should it also go out on 02/21/2021, 02/21/2022 and 02/21/2023?
2. Let's say you don't open or use the workbook on one of those days. Should the email go out on 02/22?

If the workbook is open on both 02/21/2020 and 02/22/2020, I think I'll need to create an 'Emails Sent' sheet that lists by quote number if the renewal email has been sent for the current year so that the email won't be sent on both those days. I also think we'll need to add a 'Business Unit Email' column to the 'List' sheet.

The renewal date will never be that far in advance of the End Date.  It will be either a 1 month or 2 prior to the end date or on the End Date.  The only other advance date is the notification email date.  

Once the license is renewed there are no additional communication until the next  renewal period which could be 2 to 3 years from the last renewal.  

I would like to say 9 out of 10 the End Date will be the date to renew.  So in using your multi- year quote

Start date 12/1/2019
End date 11/30/2023

Renewal date will either be 9/30, 10/30 or 11/30 but never anything outside of that time frame.  So, no matter which date is used to renew the license the new start date after the renewal has been completed will be 12/1/2024.

I assume then that the email should go out on 02/21/2020. Should it also go out on 02/21/2021, 02/21/2022 and 02/21/2023?
Let's say you don't open or use the workbook on one of those days. Should the email go out on 02/22?

No, the email will always go within at least 40 days prior to the end date because a renewal will never happen that far in advance.
Yes, the email should go out the following day or when the workbook is opened again.

If the workbook is open on both 02/21/2020 and 02/22/2020, I think I'll need to create an 'Emails Sent' sheet that lists by quote number if the renewal email has been sent for the current year so that the email won't be sent on both those days. I also think we'll need to add a 'Business Unit Email' column to the 'List' sheet.

Agree

The renewal date will never be that far in advance of the End Date.  It will be either a 1 month or 2 prior to the end date or on the End Date.

Okay. I'm a stickler for proper validation and so for the renewal date instead of the 'on or after the start date' that is in place now,  how about we change it to 'from 2 months prior to the end date to and including the end date?

Okay. I'm a stickler for proper validation and so for the renewal date instead of the 'on or after the start date' that is in place now,  how about we change it to 'from 2 months prior to the end date to and including the end date?

Agree to the validation