title: Setting combobox values on initialisation and change of other comboboxes
body: <p>(EDIT: To clarify, I'm running Excel 2013, so Microsoft's date picker isn't available.)</p>
  
  <p>I'm trying to code a simple date picker - it'll be tidier when it's done, it's just big for simplicity while I build it - and everything populates as it should:</p>
  
  <p><a href="https://i.stack.imgur.com/UsnSt.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/UsnSt.png" alt="Date picker form"></a></p>
  
  <pre><code>Me.Combo_Year.List = wsLU.Range("Date_Years").Value
  Me.Combo_Month.List = wsLU.Range("Date_Months").Value
  Me.Combo_Day.List = wsLU.Range("Date_Days31").Value
  </code></pre>
  
  <p>However, there are two instances where I'd like to set default values for the year, month and day comboboxes. For the times I'm using spin buttons, where a simple <code>.Value</code> statement sets them to 12 noon in the <code>_Initialize</code>. But neither <code>.Value</code> nor <code>.Text</code> works for the comboboxes:</p>
  
  <pre><code>Me.Combo_Year.Text = Year(Now())  ' Doesn't work
  Me.Combo_Month.Text = Month(Now())  ' Doesn't work
  Me.Combo_Day.Text = Day(Now())  ' Doesn't work
  Me.Spin_Hour.Value = 12  ' Works fine
  Me.Spin_Minute.Value = 0  ' Works fine
  </code></pre>
  
  <p><a href="https://i.stack.imgur.com/H7JPM.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/H7JPM.png" alt="Error on initialising"></a></p>
  
  <p>Similarly, when I try to set the date to a lower value when a month with fewer days is selected (to avoid returning the 31st of February, for instance), both <code>.Value</code> and <code>.Text</code> prove unhelpful again:</p>
  
  <p><a href="https://i.stack.imgur.com/yasfJ.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/yasfJ.png" alt="Debug line after changing month"></a></p>
  
  <p>Is there any way to reliably set a default value and later change the value of a combobox in code? Am I missing something hugely obvious?</p>
  
  <p>EDIT: For reference, the full code for the relevant parts of the form (<code>UpdatePreview</code> just updates the preview date above the OK button) as requested:</p>
  
  <pre><code>--------------------------------------------------------------------------------
  
  Option Explicit
  
  --------------------------------------------------------------------------------
  
  Private Sub UserForm_Initialize()
  Dim wsLU As Worksheet, wbV As Workbook
  Set wbV = ActiveWorkbook
  Set wsLU = wbV.Worksheets("General Lookups")
  
  Me.Combo_Year.List = wsLU.Range("Date_Years").Value
  Me.Combo_Month.List = wsLU.Range("Date_Months").Value
  Me.Combo_Day.List = wsLU.Range("Date_Days31").Value
  Me.Combo_Minute.AddItem 0
  Me.Combo_Minute.AddItem 30
  
  ' Tried putting the date numbers via variables instead of direct, with various data types
  Dim TestYear As String, TestMonth As String, TestDay As String
  TestYear = Year(Now())
  TestMonth = Month(Now())
  TestDay = Day(Now())
  Lab_T_Year.Caption = TestYear
  Lab_T_Month.Caption = TestMonth
  Lab_T_Day.Caption = TestDay
  'Me.Combo_Year.Text = TestYear    ' If these lines are commented out the form will load, though without the comboboxes prepopulated
  'Me.Combo_Month.Text = TestMonth  ' If these lines are commented out the form will load, though without the comboboxes prepopulated
  'Me.Combo_Day.Text = TestDay      ' If these lines are commented out the form will load, though without the comboboxes prepopulated
  
  ' Original code; tried this both with and without various Format types.
  'Me.Combo_Year.Value = Format(Year(Now()), "0000")
  'Me.Combo_Month.Value = Format(Month(Now()), "00")
  'Me.Combo_Day.Value = Format(Day(Now()), "00")
  Me.Spin_Hour.Value = 12
  Me.Combo_Minute.Value = 0  ' Switched the minute spinner to a combobox as the client wanted to just pick half hours (00/30) instead of minutes
  
  UpdatePreview  ' Updates date and time preview, works fine.
  End Sub
  
  --------------------------------------------------------------------------------
  
  Private Sub Combo_Year_Change()  ' Combo_Month_Change has an equivalent sub that essentially mirrors this one
  Dim wsLU As Worksheet, wbV As Workbook
  Set wbV = ActiveWorkbook
  Set wsLU = wbV.Worksheets("General Lookups")
  
  Dim iMonthNo As Integer, iYearNo As Long, iMaxDate As Integer
  
  ' Set number of days based on month
  iMonthNo = Me.Combo_Month.ListIndex + 1
  iYearNo = Me.Combo_Year.Value
  If iMonthNo = 1 Or iMonthNo = 3 Or iMonthNo = 5 Or iMonthNo = 7 Or iMonthNo = 8 Or iMonthNo = 10 Or iMonthNo = 12 Then
      Me.Combo_Day.List = wsLU.Range("Date_Days31").Value
      iMaxDate = 31
  ElseIf iMonthNo = 4 Or iMonthNo = 6 Or iMonthNo = 9 Or iMonthNo = 11 Then
      Me.Combo_Day.List = wsLU.Range("Date_Days30").Value
      iMaxDate = 30
  ElseIf iMonthNo = 2 Then
      Me.Combo_Day.List = wsLU.Range("Date_Days28").Value
      iMaxDate = 28
      ' Leap year div by 4
          If iYearNo / 4 = Int(iYearNo / 4) And Not (iYearNo / 100 = Int(iYearNo / 100)) Then Me.Combo_Day.List = wsLU.Range("Date_Days29").Value
          If iYearNo / 4 = Int(iYearNo / 4) And Not (iYearNo / 100 = Int(iYearNo / 100)) Then iMaxDate = 29
      ' Leap year div by 400
          If iYearNo / 4 = Int(iYearNo / 4) And iYearNo / 400 = Int(iYearNo / 400) Then Me.Combo_Day.List = wsLU.Range("Date_Days29").Value
          If iYearNo / 4 = Int(iYearNo / 4) And iYearNo / 400 = Int(iYearNo / 400) Then iMaxDate = 29
  End If
  
  ' Code to attempt to change the date down if Month is switched to one with fewer days. It doesn't work.
  If Me.Combo_Day.Value &gt; iMaxDate And iMonthNo &gt; 0 And Not Me.Combo_Day.Value = "" Then Me.Combo_Day.Value = iMaxDate
  
  UpdatePreview  ' Updates date and time preview, works fine.
  End Sub
  </code></pre>
  
  <p><strong>THINGS WOT HAVEN'T WORKED:</strong></p>
  
  <ul>
  <li>Adding Microsoft's own date picker (not available in Excel 2013)</li>
  <li>Installing the supplemental date picker as suggested by Microsoft (can't assume it'll be available on users' computers)</li>
  <li>Attempts to directly set the comboboxes' <code>.Text</code> or <code>.Value</code> properties through VBA, regardless of data type used.</li>
  <li>Attempts with dates directly (<code>=Month(Now())</code>), through variables (<code>=sNowMonth</code>), or by list index (<code>Me.Combo_Month.Text=Me.Combo_Month.List(Month(Now())-1)</code>).</li>
  </ul>
  
  <p>I've been hunting around for a solution for this since last week. Every possibility I've found has been for older versions of Office. Can anyone help?</p>