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

**lynn206** · 03-20-2019, 02:50 AM

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

**Keebellah** · 03-20-2019, 03:42 AM

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 :

Dim tmpDate as date
tmpdate = dateserial(year(CDate(TextBox1.Text)), month(CDate(TextBox1.Text)), day(CDate(TextBox1.Text)))
in the Up you say tmpDate=tmpDate + 30
in the down tmpdate = tmpdate-30
and then fille the Textbox1.Text= format(tmpdate, "mmm yy")

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.

**lynn206** · 03-20-2019, 05:22 AM

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....

**Keebellah** · 03-20-2019, 05:32 AM

Please read the forum rules when including code and USE CODE TAGS (I did so you saw how it looks)

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
if year(tmpDate) < 2065 Then 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
if year(tmpDate)> 2018 Then TextBox1.Text = Format(tmpDate, "mmm yy")
End Sub

You must also check that the linked cell is updated too

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

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

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

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

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

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

Thread Information

Users Browsing this Thread

Similar Threads

Couln't link up with textbox to spin button in userform

ActiveX Control Spin Button Copy Button and Change Cell reference?

Keep month and date - change year

[SOLVED] Year to date figure to change every month end

[SOLVED] Enable Disable spin button based on a textbox

Keep month and date but change year to current year and reoccur when new year rolls over.

Use the spin button to change value in textbox?

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions