(VBA) Replicate "custom date" feature in userform

0
votes

As shown in the attached pictures below, I am trying to replicate the custom number format feature where the user can add text strings to dates i.e. yyyy" x "mm" y "dd" z " resulting in a cell displaying 2017 x 10 y 26 z but it still behaves just like a normal date would (meaning the function box still shows 2017-10-26).

I have a user form with six textboxes, three for the date format and three for text strings (if any) (not a pretty UI, I know). I also have a seventh textbox that displays the resulting date with text as it should be displayed in the cell (by combining the content of the other textboxes).

The first problem is that I could not find any solution on the web where text string can be inserted in-between a date format. IsDate() / CDate() won't recognize the mix of numbers and text as a date. Numberformat = format(...) won't allow referencing textboxe.value or string as the origin of the format yyyy,mm,dd.

On top, I want to give the user the freedom to e.g. write just "17" instead of 2017 in the first textbox and the userform code to understand it as "yy", or, as another example, to leave the last text box empty and no text to be added at the end of the date.

Any ideas?

I can use something like this

Dim xDate As Date
xDate = IsDate(TextBox1.Value & "-" & TextBox3.Value & "-" &  TextBox5.Value)

This can then be used for

ActiveCell.NumberFormat = Formats(xDate, ....)

But then I have to feed it the format and somehow also the strings.

Feature

enter image description here

Example user form

enter image description here

3
excelvbadate
What have you tried so far? Please post your code. – Kostas K.
Hmm, I tried ActiveCell.Value = CDate(TextBox7.Text) and (TextBox7.Value) ; as well as several different trial-and-error versions of ActiveCell.numberformat = format(Date[today for test purposes], TextBox1.Value [I put the string = "yyyy" instead of "2017"] & "TextBox2.Value" [string = "x"] & TextBox3.Value [string = "mm" instead of "10"] & ....) ... in the end, I got frustrated and deleted all my attempts and now I have no code on the "Apply" button : ( not super good at this VBA things haha – Michu1
Please post your code in the post, not in comments. It's easier to read. Have you tried the DateValue function? techonthenet.com/excel/formulas/datevalue.php – Kostas K.
The date data type will not accept the format you're trying to pass. You need to store it as string and write a function to parse it as date if you need it for calculations. – Kostas K.
(deleted previous comment due to incoherence). I tried to think of a way to use DateValue but it didn't lead my thoughts to any solution as to how to add strings to the format. – Michu1

3 Answers

0
votes

I'd suggest using the DateSerial function which receives three unambiguous numbers as arguments. Assuming you named your controls accordingly:

DateSerial(tbxYear.Value,tbxMonth.Value,tbxDay.Value)

You could add a check to your year textbox:

If Len(tbxYear.Value) < 4 Then
    tbxYear.Value = tbxYear.Value + 2000
End If
0
votes

You can write a function passing the characters from the textbox controls which will return the custom format:

Function CustomFormat(chars As Variant) As String

    Dim s As String
        s = "yyyy ""{0}""mm ""{1}""dd ""{2}"""

    Dim i As Long
    For i = 0 To UBound(chars)
        s = Replace(s, "{" & i & "}", chars(i))
    Next i

    CustomFormat = s
End Function

To call it:

Sub T()
    Dim v As Variant
        v = Array("x", "y", "z") 'Change to textbox values
    ActiveCell.NumberFormat = CustomFormat(v)
End Sub

'Format: yyyy "x"mm "y"dd "z"
0
votes

Thank you Kostas K. and jkpieterse, 

ActiveCell.Value = DateSerial(TextBox1.Value, TextBox3.Value, TextBox5=.Value)
ActiveCell.NumberFormat = "yyyy """ & TextBox2.Value & """mm """ & TextBox4.Value & """dd """ & TextBox6.Value & """"

This works and I will manage to figure out the rest from here!