vbacalendarformattingyearmonth

How to get years and months data from Spin Buttons to specific cells?


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?

It looks something like this

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

Solution

  • 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:

    1. 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
      
    2. 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 :)