Auto populate textbox with date according to date in other textbox

Tcurtis

Board Regular
Joined
Aug 15, 2009
Messages
147
I have a form with a button that puts the beginning and ending date in two textboxes. The start date is named Start_Date_Corp and the stop date is named Stop_Date_Corp. I would like to have another textbox that looks at the Start date and populates with a date that is 24 months prior to that date. Below is the VBA I have but I cannot make it work correctly. When I push the 2013 the first two boxes are correct - Start_Date_Corp 1/1/2013 -- Stop_Date_Corp 12/21/2013. The start date textbox I have for 24 months prior displays 1/1/1898. How can I make this box come up with the correct date? Or maybe just have that textbox look at the start date and then update using a function on the form?


Below is my VBA

Function Set_Metrics_24_month_Corp()

Dim Start_Date_Corp As Date
Dim Stop_Date_Corp As Date

Corp_Metric_2Yr_Stop = DateSerial(Year(Stop_Date_Corp), Month(Stop_Date_Corp) + 1, 0) + TimeSerial(23, 59, 59)
Corp_Metric_2Yr_Start = DateSerial(Year(Start_Date_Corp), Month(Start_Date_Corp) - 23, 1)

Forms![frm_Main]![NavigationSubform].Form![Corp_Metric_2Yr_Start].Value = Corp_Metric_2Yr_Start
Forms![frm_Main]![NavigationSubform].Form![Corp_Metric_2Yr_Stop].Value = Corp_Metric_2Yr_Stop

End Function
 

Some videos you may like

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

kbrummert

New Member
Joined
Jan 7, 2018
Messages
27
Do you have both fields formatted as mm/dd/yyyy? I played with this and it looks like it may be reading the source box wrong due to different formatting. Otherwise, it worked fine.
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,219
Office Version
  1. 365
Platform
  1. Windows
Use DateAdd to add the required months forward/backwards.

For example to get the date 24 months prior to the date in Start_Date_Corp use this.
Code:
DateAdd("m", -24, Start_Date_Corp.Value)
 

Tcurtis

Board Regular
Joined
Aug 15, 2009
Messages
147
I changed the format to mm/dd/yyyyy and still came up with the year dates of 1898. The formatting was Short Date and that does not work either. Not sure why this does not work.
 

Tcurtis

Board Regular
Joined
Aug 15, 2009
Messages
147

ADVERTISEMENT

Not sure where to put the DateAdd at. I put it in the Textbox using Expression and it did not work.
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,219
Office Version
  1. 365
Platform
  1. Windows
I'm not sure where to put it either because I can't quite figure out what the posted code is meant to do.

When do you want the other textbox to be populated based on what's entered in Start_Date_Corp?
 

Tcurtis

Board Regular
Joined
Aug 15, 2009
Messages
147

ADVERTISEMENT

After the Start date box has been updated. I tried refreshing the page but it was not updating. I have buttons for each year that populate the start and stop dates. After the dates are populated I would like the 24 month box to populates
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,219
Office Version
  1. 365
Platform
  1. Windows
You should look into using the AfterUpdate event of the Start_Date_Corp textbox, or add the code to the buttons that populate the start/stop dates.
 

Micron

Well-known Member
Joined
Jun 3, 2015
Messages
2,019
Office Version
  1. 365
Platform
  1. Windows
One form is on a navigation form and the other is not? AFAIK, you can only load one form at a time from a navigation form, which ought to mean one of yours isn't open when this code runs. Not clear if more than one form is involved since you don't indicate what Corp_Metric_2Yr_Stop or Corp_Metric_2Yr_Start are. Variables? Form controls? If the former, are they declared somewhere and still available to this procedure? Or are you not using Option Explicit for all your modules? If controls, I'm surprised this would work as written without the form reference (Me. if code is on the same form as the control, or the complete Forms! reference if not, assuming the referenced form is open). You need to say where the code is located and provide more info about the number of forms involved and those variables (or whatever they are). Or step through the code and check that the values are present and correct. You might be subtracting/adding date values from some unexpected value. The only other thing I can think of is that your regional settings for Dates are coming into play.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,961
Messages
5,599,061
Members
414,281
Latest member
Engjamal2021

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top