Excel 2016 ComboBox Date Display versus Number

Question

This seems to be a simplistic task to get the actual date to display in the combobox versus 43466 (a number). The code that everyone seems to be using is the following with

.Text:

     Private Sub ComboBox20_Change()
     ComboBox20.Text = format(ComboBox20.Text, "dd/mm/yyyy")
     End Sub

OR . Value

    Private Sub ComboBox20_Change()
    ComboBox20.Value = format(ComboBox20.Value, "dd/mm/yyyy")
    End Sub

I have even tried with .text and .value. Either one keeps giving me the following error:

Compile Error: Wrong number of arguments or invalid property assignment

Any thoughts on what I must be missing, having a brain meltdown on the most mundane and seemingly easy task.

Notice that `format` doesn't have its first letter capitalised. If that's the case in the actual code then check for missing references via Tools > References. Missing references tend to cause built-in functions to behave strangely — barrowc, Apr 14 '17 at 23:36
@barrowc sometimes it happens if you have been using it in low capitals since the beginning, it has nothing to do with the real execution,indeed, a good advice to check on references -but saddly this isn't the case since Format comes with Excel VBA reference itself-. — Sgdva, Apr 14 '17 at 23:41
@Sgdva see [this question](http://stackoverflow.com/q/13719687/2127508) for a built-in function being broken by a missing reference. I would have expected the specific "Missing project or library" error though. Note that this is a compile error so runtime effects like the specific value of the combobox should not be a factor — barrowc, Apr 14 '17 at 23:50

score 0 · Answer 1 · answered Apr 14 '17 at 23:36

Problem approach
The problem seems to be when you are attempting to change the value in the combobox, is there a value to begin with? If it is empty, it is taking the default version of the date.
Solution approach
Try to set the value before it goes into the Change event.
Sample code and demonstration

Private Sub ComboBox1_Change()
    ComboBox1.Value = Format(ComboBox1.Value, "dd/mm/yyyy")
End Sub
Private Sub UserForm_Activate()
Dim CounterDate As Long
Dim TxtDate As String
    For CounterDate = 1 To 2
    TxtDate = DateAdd("d", CounterDate, Now)
    ComboBox1.AddItem (TxtDate)
    ComboBox1.Value = TxtDate
    Next CounterDate
End Sub

Further comments
As you may see as soon as you change it, it enters again the "Change" event, you may turn off the events as soon as you enter the cycle and then turn them on again if it is causing erratic behaviour on your pc -sometimes it does-.

I have tried this in a form or within the actual worksheet and neither of these work. I am not understanding why there seems to be resolution on this subject, because everywhere I search there seems to be no concrete and plausible outcome. Thanks for all of your input. I had no missing references as well. — T-Rex, Apr 17 '17 at 21:28

Excel 2016 ComboBox Date Display versus Number

1 Answers1