Format Dynamically added textBox in Excel

Question

I am adding TextBoxes dynamically in a userform and want to make them a date format to ensure correct date entry. I am unable to find any examples.

Here is my code for the userform activation:

Private Sub TextBox3_Exit(ByVal Cancel As MSForms.ReturnBoolean)

fltDays = TextBox3.Value If TextBox3.Value = 0 Then Exit Sub

For i = 1 To fltDays

n = i - 1

Dim TextBox As Control

Set theLbl = FloatDayFrm.Controls.add("Forms.Label.1", "lbl_" & i, True)
    With theLbl
      .Caption = "Day " & i
      .Left = 20
      .Width = 60
      .Top = n * 24 + 100
      .Font.Size = 10
    End With


Set TextBox = FloatDayFrm.Controls.add("Forms.TextBox.1", "TextBox_" & n, True)
    With TextBox
        .Top = 100 + (n * 24)
        .Left = 90
        .Height = 18
        .Width = 50
        .Name = "txtBox" & i
        .Font.Size = 10
        .TabIndex = n + 4
        .TabStop = True
    End With Next i

FloatDayFrm.Height = 150 + fltDays * 24 With btnOK .Top = 102 + fltDays * 24 .TabStop = True .TabIndex = n + 5 End With

With btnCancel .Top = 102 + fltDays * 24 '.TabStop = True .TabIndex = n + 6 End With

End Sub

This is my code for the Command button:

Private Sub btnOK_Click()

n = TextBox3.Value

For j = 1 To n

Set varFloatDay = FloatDayFrm.Controls("txtBox" & j)

Select Case varFloatDay
    Case ""
        MsgBox "Day " & j & " can't be blank", vbOKOnly, "Incorrect Value"
        Exit Sub
    Case Is > TextBox2.Value
         MsgBox "Date is after end date", vbOKOnly, "Incorrect Value"
        Exit Sub
    Case Is < TextBox1.Value
         MsgBox "Date is BEFORE end date", vbOKOnly, "Incorrect Value"
        Exit Sub
End Select

Next j

End Sub

Any help would be appreciated.

Mukul Varshney · Accepted Answer · 2017-03-29 02:39:15Z

1

You have to convert text to date format. You can use multiple approach.

Add a label beside textbox to display the format user has to specify the date. Parse the text specified by user as per the format. Do validation and conversion as shown in code below.
Use a calendar control instead of textbox as user input.
Have separate textboxes or cells for year, month and day. Do validation and conversion as shown in code below.
If you are sure date is in the specified format as per regional setting. Do validation and conversion as shown in code below.

Try below

Private Sub TestDate()
    Dim yr As Integer
    Dim mnth As Integer
    Dim day As Integer
    Dim dt As Date
    Dim strDate As String

    '''''3rd approach''''''
    yr = ActiveSheet.Range("A1")
    mnth = ActiveSheet.Range("B1")
    day = ActiveSheet.Range("C1")
    If IsNumeric(yr) And IsNumeric(mnth) And IsNumeric(day) Then
        If yr < 0 Or mnth < 0 Or day < 0 Then
            MsgBox "Year, Month and Day must be greater than 0."
            Exit Sub
        End If
    Else
        MsgBox "Year, Month and Day must be an integer."
        Exit Sub
    End If
    'convert to Date
    dt = DateSerial(yr, mnth, day)

    '''''4th approach''''''
    'Display a date according to your system's short date format
    'i.e. regional settings in control panel
    strDate = Format(ActiveSheet.Range("D1"), "Short Date")
    If Not IsDate(strDate) Then
        MsgBox "Incorrect Date Format"
        Exit Sub
    End If
    dt = CDate(strDate)
End Sub

edited Mar 29, 2017 at 2:39

answered Mar 29, 2017 at 2:30

Mukul Varshney

3,1511 gold badge14 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

John Wolfenstein Over a year ago

I am pretty confident on the regional settings and took that approach. My skills are intermediate and have never used a calendar control, but I may give that a try. @MukulVarshney

John Wolfenstein Over a year ago

@Variatus I used your isDate approach as I am confident in my regional settings. It works fine.

John Wolfenstein Over a year ago

@Variatus I am unable to find a calendar control in my toolbox. Is there a library that needs to be referenced?

Mukul Varshney Over a year ago

@JohnWolfenstein, refer "ablebits.com/office-addins-blog/2016/10/12/…" for date/calendar control

Variatus · Accepted Answer · 2017-03-29 01:40:52Z

1

Any input in a text box is a text string. If you want it to be a date you can use IsDate(TextBox1.Value) to determine if VBA is able to convert the string to a date (which is a number of Double type). VBA will not execute this test 100% correctly, however. For example, it may not recognised 3/2/17 as a date if your regional settings have the date separator as a period. It may convert 3.2.17 to March 2 if your regional settings are mm.dd.yy. While working on your own PC you may be able to control the regional settings. But if your project will be released into the wild it is better to use a calendar control to get a correct date.

answered Mar 29, 2017 at 1:40

Variatus

14.4k2 gold badges16 silver badges35 bronze badges

Collectives™ on Stack Overflow

Format Dynamically added textBox in Excel

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related