I have created,
SpinButton1 for years and SpinButton2 for months
TextBox1 from SpinButton1
and TextBox2 from SpinButton2
"E2" from SpinButton1 and "F2" from SpinButton2.
When I change the "E2" as yyyy format, it changes the value to 1905 from 2022 "F2" as mmm or mm format, it doesn't change any.
How to create "G2" as yyyy-mm or yyyy-mmm format?
Private Sub SpinButton1_Change()
SpinButton1.Max = 2030
SpinButton1.Min = 2021
SpinButton1.SmallChange = 1
TextBox1.Value = SpinButton1.Value
End Sub
Private Sub SpinButton2_Change()
Dim Mths As Variant
SpinButton2.Max = 11
SpinButton2.Min = 0
Mths = Array("Jan", "Feb", "Mar", "Apr", "May", "June", "July", "Aug", "Sept", "Oct", "Nov", "Dec")
TextBox2.Text = Mths(SpinButton2.Value)
End Sub
Private Sub TextBox1_Change()
ActiveSheet.Range("E2") = SpinButton1.Value
End Sub
Private Sub TextBox2_Change()
ActiveSheet.Range("F2") = TextBox2.Text
End Sub
When I change the "E2" as yyyy format, it changes the value to 1905 from 2022
Passing an integer value of "1" to a cell with "short date" format, will return 01.01.1900. That is so, becasue Excel interprets it as number of days elapsed starting from agreed start time 01.01.1900.
In your case, you pass value of 2022, so Excel counts: 01.01.1900 + 2022 days = 14.07.1905.
"F2" as mmm or mm format, it doesn't change any.
Similar thing here - you pass a string value, so Excel doesn't know if that should be a month of the year.
Solution is to construct the date in format "yyyy-mm-dd" and set the formatting of the output cell to date-like format.
Finally, to solve your problem:
I'd use additional procedure to construct the date:
Private Sub MakeDate(sp1Val as String, sp2Val As String)
Dim tempDate As Date
tempDate = DateValue(sp1Val & "-" & sp2Val + 1 & "-" & 1)
With Range("G2")
.NumberFormat = "yyyy-mmm"
.Value = tempDate
End With
End Sub
Then invoke this procedure in both of your SpinButtonX_Change() triggers like so:
Private Sub SpinButton2_Change()
Dim Mths As Variant
SpinButton2.Max = 11
SpinButton2.Min = 0
Mths = Array("Jan", "Feb", "Mar", "Apr", "May", "June", "July", "Aug", "Sept", "Oct", "Nov", "Dec")
Call MakeDate(SpinButton1.Value, SpinButton2.Value)
End Sub
Note, that you pass SpinButton2's value as an integer rather than a string. You start indexing from 0, so you have to put sp2Val + 1 while constructing the date (as I already did in the code).
Hope I helped you :)