CODEWITHSUNDEEP
excelvba
John Wolfenstein
John Wolfenstein

Reputation: 141

Format Dynamically added textBox in Excel

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.

Upvotes: 0

Views: 378

Answers (2)

Mukul Varshney
Mukul Varshney

Reputation: 3141

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

  1. 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.
  2. Use a calendar control instead of textbox as user input.
  3. Have separate textboxes or cells for year, month and day. Do validation and conversion as shown in code below.
  4. 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

Upvotes: 1

Variatus
Variatus

Reputation: 14373

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.

Upvotes: 1

Related Questions