Suggestions for the most elegant solution for inserting dates into a Userform (avoid Calendar Controls)

[Grizlore]

Hi All, (Happy New Year!)

I wish to totally avoid the use of Calendar controls, as I have experienced many issues with them as I need to provide a solution for Users who use several different versions of Excel.

I have a Userform which needs two dates. The first is normally the current date, the second is a date in the future. Previously I have used selectable Calendar controls, but need to avoid these due to the reasons above.

Any suggestions on how to best to do this?

The first date, I aim to add a simple text field which will automatically populate with todays date.

The second date field is a future date and I was wondering what that the most elegant way of getting a User to enter a date is. Previous I have found that if you get Users to type in a date, there is normally an issue with validation and date formats.

What code / validation would you suggest for the most pain free date entry field.

Thanks in anticipation

 

[Alan_P]

Hi Grizlore,

I had this very same problem a while back... I ended up using 3 ComboBoxes to select Day, Month & Year and a command button to enter the date into a textbox, with code similar to the below... Not the most elegant way of doing it im sure but it worked

Private Sub UserForm_Initialize()
With ComboBox1
    .AddItem ("Janurary")
    .AddItem ("February") '28
    .AddItem ("March")
    .AddItem ("April") '30
    .AddItem ("May")
    .AddItem ("June") '30
    .AddItem ("July")
    .AddItem ("August")
    .AddItem ("September") '30
    .AddItem ("October")
    .AddItem ("November") '30
    .AddItem ("December")
End With

For i = 1 To 31
    With ComboBox2
        .AddItem (i)
    End With
Next

With ComboBox3
    .AddItem ("2014")
    .AddItem ("2015")
    .AddItem ("2016")
    .AddItem ("2017")
    .AddItem ("2018")
    .AddItem ("2019")
    .AddItem ("2020")
End With

End Sub

Private Sub CommandButton1_Click()
If ComboBox1.Value = "April" Or ComboBox1.Value = "June" Or ComboBox1.Value = "September" Or ComboBox1.Value = "November" And ComboBox2 = "31" Then
    MsgBox (ComboBox1.Value & " does not have 31 days!!")
    
End If
If ComboBox1.Value = "February" And ComboBox2 > 28 Then
    MsgBox (ComboBox1.Value & " only has 28 days!!")
    
Else
    TextBox1.Value = Format(ComboBox2.Value & "/" & ComboBox1.Value & "/" & ComboBox3.Value, DDMMYYYY)
End If
End Sub

 

[Grizlore]

Thanks, I shall have a look at this

[it's been very frustrating trying to deal with the different versions following the loss of the old Calendar Control]

 

[Grizlore]

Working fine apart from the..

   TextBox1.Value = Format(ComboBox2.Value & "/" & ComboBox1.Value & "/" & ComboBox3.Value, DDMMYYYY)

Even if I change to "mm/yy/yyyy" etc

This is throwing a "Compile Error - Can't find project or library"

Any ideas ?

 

[Alan_P]

Hmmm... I'm not sure, what version of Excel are you using?

From googling the error message it seems it's worth checking your references --- Tools->References in the VBA window... I've got 4 ticked > Visual Basic For Applications, Microsoft Excel 14.0 Object Library, OLE Automation and Microsoft Office 14.0 Object Library

 

[snb_]

the most elegant:

A combobox (style 2-dropdownlist) or listbox that contains all valid data.
All data in the future from today until a year from today:

Private Sub UserForm_Initialize()
    ComboBox1.List = [transpose(text(today()+row(1:365),"dd-mm-yyyy"))]
    Listbox1.List=[transpose(text(today()+row(1:365),"dd-mm-yyyy"))]
End Sub

Private Sub UserForm_Initialize()
    ComboBox1.List = [index(text(today()+row(1:365),"dd-mm-yyyy"),)]
    ListBox1.List = [index(text(today()+row(1:365),"dd-mm-yyyy"),)]
End Sub

 

[GetHitFooL]

Okay I like this a lot. I see how it all works but how do I actually implement it. I know that might be the most dumb thing but I cant figure out what to actually do with the code? Should I create the three boxes and the button and then try to link it?

 

[Taul]

It works on on my Excel 2010.
Create a userform and place a combo box and a list box in it.
You don't need both but the code is demonstrating how it would work with a cobobox and a listbox, you can comment out the code of the one you don't want.
Also, there are two blocks of code to initialize the userform, you only need one of them, both work on my comp but I can't comment on the merits of either one.
If you are using the combobox, right click and select properties, you can change the style and the number of row displayed in the drop down.
hope this helps.
Paul.