+ Reply to Thread
Results 1 to 4 of 4

How to change date (by month & year only) using textbox & spin button.

  1. #1
    Registered User
    Join Date
    03-08-2019
    Location
    Kuala Lumpur, Malaysia
    MS-Off Ver
    2013
    Posts
    2

    How to change date (by month & year only) using textbox & spin button.

    Hello everyone,

    I am new to the group.

    Need some expert help here.

    I am having problem with changing the date using textbox and spin button.
    The code that I use for the spin button is as follows:

    Private Sub SpinButton1_SpinUp()
    TextBox1.Text = Format(CDate(TextBox1.Text) + 30, "dd mmm yy")
    End Sub

    Private Sub SpinButton1_SpinDown()
    TextBox1.Text = Format(CDate(TextBox1.Text) - 30, "dd mmm yy")
    End Sub

    This works well, only that the result is displayed as day, month, year instead of month, year only.
    When I changed the code as below, the decrease is limited to Dec 2018 while the increase to Jan 2020. The spin button is linked to a cell with a data entry "Jan-19".

    Private Sub SpinButton1_SpinUp()
    TextBox1.Text = Format(CDate(TextBox1.Text) + 30, "mmm yy")
    End Sub

    Private Sub SpinButton1_SpinDown()
    TextBox1.Text = Format(CDate(TextBox1.Text) - 30, "mmm yy")
    End Sub


    I have tried but to no avail to get the results that I want, which is:
    1) result should be displayed in month, year format
    2) the minimum and maximum value of the date for the spin button are Jan 2018 and Dec 2065 respectively.

    Thank you in advance for your helps on this problem.

    Lynn

  2. #2
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: How to change date (by month & year only) using textbox & spin button.

    Hi, welcome to the forum.
    It's probably clear for you but ...
    The linked cell date entry is "Jan-19", that is what you SEE but the actual value is probably the actual date, correct?
    Do NOT mistake the date format with the date value.
    Suggestion:
    Add a temporary date variable that fills the date based on the textbox in the format :

    Please Login or Register  to view this content.
    Something like that, play around with it

    You can also do an extra check to check if the new Year(tmpDate) is between 2018 and 2065 before comitting

    This just typed, not checked for syntax errors, but just to give you an idea how it could be done,, there are probably other ways but this is my idea for doing it.
    ---
    Hans
    "IT" Always crosses your path!
    May the (vba) code be with you... if it isn't; start debugging!
    If you like my answer, Click the * below to say thank-you

  3. #3
    Registered User
    Join Date
    03-08-2019
    Location
    Kuala Lumpur, Malaysia
    MS-Off Ver
    2013
    Posts
    2

    Re: How to change date (by month & year only) using textbox & spin button.

    Hi, many thanks for your welcoming wish and reply.

    I have updated the code with the suggested solution as follows:

    Private Sub SpinButton1_SpinUp()
    Dim tmpDate As Date

    tmpDate = DateSerial(Year(CDate(TextBox1.Text)), Month(CDate(TextBox1.Text)), Day(CDate(TextBox1.Text)))
    tmpDate = tmpDate + 30

    TextBox1.Text = Format(tmpDate, "mmm yy")

    End Sub

    Private Sub SpinButton1_SpinDown()
    Dim tmpDate As Date

    tmpDate = DateSerial(Year(CDate(TextBox1.Text)), Month(CDate(TextBox1.Text)), Day(CDate(TextBox1.Text)))
    tmpDate = tmpDate - 30

    TextBox1.Text = Format(tmpDate, "mmm yy")

    End Sub

    In the cell that is linked to the spin button, I entered the date as, for example, Jan 19. When I click on the spin button (up or down), it works but the date when decreased is still limited to Dec 2018 while when increased, it doesn't go beyond Jan 2020.

    I have also tried changing the min & max value in the spin button properties by the date value for Jan 2018 and Dec 2065, but nothing change. The same problem remains...

    I do not have any basic in VBA. I usually get the VBA code via google search and try to customize it to suit my work purposes.
    Have been cracking my head for 2 days to solve this....

  4. #4
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: How to change date (by month & year only) using textbox & spin button.

    Please read the forum rules when including code and USE CODE TAGS (I did so you saw how it looks)
    Please Login or Register  to view this content.
    You must also check that the linked cell is updated too

+ 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. Couln't link up with textbox to spin button in userform
    By ajaypal.sp in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 09-18-2016, 08:14 AM
  2. ActiveX Control Spin Button Copy Button and Change Cell reference?
    By chrstphrbllngs in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-22-2016, 04:01 PM
  3. Keep month and date - change year
    By lokeshrao in forum Excel Formulas & Functions
    Replies: 15
    Last Post: 11-21-2015, 06:51 AM
  4. [SOLVED] Year to date figure to change every month end
    By floxxie in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 09-09-2015, 09:12 AM
  5. [SOLVED] Enable Disable spin button based on a textbox
    By forrestgump1980 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-21-2015, 07:21 AM
  6. Replies: 3
    Last Post: 09-04-2013, 10:49 AM
  7. Use the spin button to change value in textbox?
    By proepert in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-26-2010, 12:22 AM

Tags for this Thread

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