+ Reply to Thread
Results 1 to 14 of 14

text box and formula

  1. #1
    Registered User
    Join Date
    02-10-2018
    Location
    toronto ontario
    MS-Off Ver
    office 2010
    Posts
    66

    Lightbulb text box and formula

    If I have a textbox 1 which will be a date entry I need to add 18 years to the date and show new date in textbox 2

    this is on a user pop up form so need it in VBA

    EG 01/10/2000 + 18yr = 01/10/2018
    text 1 text 2

  2. #2
    Forum Expert
    Join Date
    11-22-2016
    Location
    Cornwall,UK
    MS-Off Ver
    office 365
    Posts
    4,240

    Re: text box and formula

    Hi mmikem

    Try
    Please Login or Register  to view this content.
    Click *Add Reputation to thank those who helped you. Ask if anything is not clear

  3. #3
    Registered User
    Join Date
    02-10-2018
    Location
    toronto ontario
    MS-Off Ver
    office 2010
    Posts
    66

    Re: text box and formula

    Hi Kev Thanks for the help but I get a error 13 when I run it txtDob is text box 1 and txtLyy is text box 2 mm/dd/yyyy I think it is the date formatting screw it up

    [Private Sub txtDob_Change()
    x = txtDob.Value
    txtLyy.Value = DateSerial(Year(x) + 18, Month(1), Day(1))
    End Sub

    Private Sub txtLyy_Change()


    End Sub ]

  4. #4
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: text box and formula

    Any chance you could upload a sample workbook?

    Click on GO ADVANCED, scroll down and click Manage Attachments.
    If posting code please use code tags, see here.

  5. #5
    Registered User
    Join Date
    02-10-2018
    Location
    toronto ontario
    MS-Off Ver
    office 2010
    Posts
    66

    Re: text box and formula

    I have tried clicked on paper clip but can not do anything will not let me attach file

  6. #6
    Registered User
    Join Date
    02-10-2018
    Location
    toronto ontario
    MS-Off Ver
    office 2010
    Posts
    66

    Thumbs up Re: text box and formula

    Her you go got it to work there is other code that I am working on
    Last edited by mmikem; 02-22-2018 at 05:03 PM. Reason: replace file

  7. #7
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: text box and formula

    Does this work?
    Please Login or Register  to view this content.

  8. #8
    Forum Guru
    Join Date
    02-27-2016
    Location
    Vietnam
    MS-Off Ver
    2016
    Posts
    5,891

    Re: text box and formula

    Like this?
    Please Login or Register  to view this content.

  9. #9
    Forum Expert
    Join Date
    11-22-2016
    Location
    Cornwall,UK
    MS-Off Ver
    office 365
    Posts
    4,240

    Re: text box and formula

    Hi Kev Thanks for the help but I get a error 13 when I run it txtDob is text box 1 and txtLyy is text box 2 mm/dd/yyyy I think it is the date formatting screw it up
    x = txtDob.Value
    txtLyy.Value = DateSerial(Year(x) + 18, Month(1), Day(1))
    This illustrates DATESERIAL, YEAR, MONTH, DAY functions:
    Please Login or Register  to view this content.
    so perhaps:
    Please Login or Register  to view this content.
    this should also work:
    Please Login or Register  to view this content.
    Last edited by kev_; 02-22-2018 at 02:09 AM.

  10. #10
    Forum Expert
    Join Date
    11-22-2016
    Location
    Cornwall,UK
    MS-Off Ver
    office 365
    Posts
    4,240

    Re: text box and formula

    I am unable to show your userform - VBA tells me it is looking for missing references

    So you must help me

    Put the code below in your userform
    - then open your form
    - use 25 December 2000 as your DOB (input it as you normally would)
    - what does each message box return?
    (some may return nothing)

    On Error Resume Next
    MsgBox "1 " & txtDob.Value
    MsgBox "2 " & CDate(txtDob.Value)
    MsgBox "3 " & Val(txtDob.Value)
    MsgBox "4 " & CStr(txtDob.Value)
    MsgBox "5 " & CInt(txtDob.Value)
    MsgBox "6 " & Int(CDbl(txtDob.Value))
    MsgBox "7 " & Format(txtDob.Value, "DD MMM YYYY")

    thanks

  11. #11
    Forum Expert
    Join Date
    11-22-2016
    Location
    Cornwall,UK
    MS-Off Ver
    office 365
    Posts
    4,240

    Re: text box and formula

    Quote Originally Posted by mmikem
    forgot to mention when I try to enter a date in txtDob it starts to error on first digit
    This is the reason why nothing is working - you have broken your textbox! Or else the data you are inputting is being treated as invalid.
    The final message box returns 1 January 1900. In case you are not aware, 1 January 1900 is the first date that Excel recognises. So, somehow your textbox is returning the value 1 (driven by the error no doubt)

    So that we are on exactly the same page, please attach your latest workbook to the thread. I will look at your textbox properties etc tomorrow.

    Q1 How are you wanting to input values into your textbox? How would you input 25 December 2007?

    Q2 As a matter of interest, when did the textbox start displaying thus error ?
    (I am guessing it was before you started this thread because there is nothing in this thread altering that textbox in any way.)

    Q3 It may be your click event that is triggering the error. When did you introduce that?
    (This could be the source of mismatch error possibly)

  12. #12
    Registered User
    Join Date
    02-10-2018
    Location
    toronto ontario
    MS-Off Ver
    office 2010
    Posts
    66

    Re: text box and formula

    Here is latest file
    Last edited by mmikem; 02-23-2018 at 10:34 AM.

  13. #13
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: text box and formula

    mmikem

    Are you using a 'non-standard' control, e.g DTPicker?

  14. #14
    Forum Expert
    Join Date
    11-22-2016
    Location
    Cornwall,UK
    MS-Off Ver
    office 365
    Posts
    4,240

    Re: text box and formula

    I think I understand the cause of your error
    - I have tested what I am suggesting below on your form and it functioned as expected.

    Step1 Delete Private Sub txtDob_Change
    - it is clashing with the input of date of birth
    - this is why you are getting your error

    Step 2 Replace it with this sub
    Please Login or Register  to view this content.
    Step 3 Test
    - run your form
    - enter date in DOB field
    - click inside Last Year Of Youth


    As an alternative you could try updating LastYearOfYouth when you exit DOB
    - try this after you have tested the above
    Please Login or Register  to view this content.
    Last edited by kev_; 02-23-2018 at 05:38 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 9
    Last Post: 11-08-2015, 08:04 PM
  2. Replies: 3
    Last Post: 03-04-2014, 01:47 PM
  3. [SOLVED] Find & Replace Formula for Locating Short Text within Larger Text
    By garrett.grillo in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-08-2014, 05:04 PM
  4. Formula to find and return text string within a cell full of text strings
    By LightingPop in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-03-2013, 11:45 PM
  5. [SOLVED] Extract text from a string of text (amend formula to include new criteria)
    By robertguy in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-10-2013, 04:53 PM
  6. Replies: 6
    Last Post: 06-08-2012, 06:54 PM
  7. Replies: 0
    Last Post: 06-03-2010, 01:11 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1