TextBox format (1 Viewer)

[goncalo]

Hello everyone,im new to coding vba and im in need of assistance.
I want to make a code to be used in excel that makes 2 of my 4 text boxes have a data format like this dd/mm/yyyy and also a character limit
Like i said made a code to implement 4 textboxes on the worksheet but i really have no idea on how to go around and get data format and character limit part done.
The code below is the one i made and is present in module1,all that's missing is the data format and character limitation part.

Sub AddLockedTextBox()
    Dim ws As Worksheet
    Dim textBox As Shape
    Dim cb As comboBox
    
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    
    ' Remove any existing textboxes in the range J19:K19
    For Each textBox In ws.Shapes
        If Not Intersect(textBox.TopLeftCell, ws.Range("J19:K19")) Is Nothing Then
            textBox.delete
        End If
    Next textBox
    
    ' Add a locked textbox in range J19:K19
    Set textBox = ws.Shapes.AddTextBox(msoTextOrientationHorizontal, ws.Range("J19").Left, ws.Range("J19").Top, ws.Range("K19").Left + ws.Range("K19").width - ws.Range("J19").Left, ws.Range("J19").height)
    
    With textBox
        .TextFrame.Characters.Text = ""
        .LockAspectRatio = msoTrue
        .Placement = xlMoveAndSize
        .Locked = msoFalse ' Lock the textbox shape
    End With
    
    ' Remove any existing textboxes in the range F19:G19
    For Each textBox In ws.Shapes
        If Not Intersect(textBox.TopLeftCell, ws.Range("F19:G19")) Is Nothing Then
            textBox.delete
        End If
    Next textBox
    
    ' Add a locked textbox in range F19:G19
    Set textBox = ws.Shapes.AddTextBox(msoTextOrientationHorizontal, ws.Range("F19").Left, ws.Range("F19").Top, ws.Range("G19").Left + ws.Range("G19").width - ws.Range("F19").Left, ws.Range("F19").height)
    
    With textBox
        .TextFrame.Characters.Text = ""
        .LockAspectRatio = msoTrue
        .Placement = xlMoveAndSize
        .Locked = msoFalse ' Lock the textbox shape
    End With
    
    ' Remove any existing textboxes in the range J12:K12
    For Each textBox In ws.Shapes
        If Not Intersect(textBox.TopLeftCell, ws.Range("J12:K12")) Is Nothing Then
            textBox.delete
        End If
    Next textBox
    
    ' Add a locked textbox in range J12:K12
    Set textBox = ws.Shapes.AddTextBox(msoTextOrientationHorizontal, ws.Range("J12").Left, ws.Range("J12").Top, ws.Range("K12").Left + ws.Range("K12").width - ws.Range("J12").Left, ws.Range("J12").height)
    
    With textBox
        .TextFrame.Characters.Text = ""
        .LockAspectRatio = msoTrue
        .Placement = xlMoveAndSize
        .Locked = msoFalse ' Lock the textbox shape
    End With
    
    ' Remove any existing textboxes in the range N19:O19
    For Each textBox In ws.Shapes
        If Not Intersect(textBox.TopLeftCell, ws.Range("N19:O19")) Is Nothing Then
            textBox.delete
        End If
    Next textBox
    
    ' Add a locked textbox in range N19:O19
    Set textBox = ws.Shapes.AddTextBox(msoTextOrientationHorizontal, ws.Range("N19").Left, ws.Range("N19").Top, ws.Range("O19").Left + ws.Range("O19").width - ws.Range("N19").Left, ws.Range("N19").height)
    
    With textBox
        .TextFrame.Characters.Text = ""
        .LockAspectRatio = msoTrue
        .Placement = xlMoveAndSize
        .Locked = msoFalse ' Lock the textbox shape
    End With
    
    ' Protect the sheet while allowing selection in comboboxes
'    ws.Protect DrawingObjects:=True, Contents:=True, UserInterfaceOnly:=True
End Sub

I would appreciate any kind of help,thank you for reading!

 

[CJ_London]

Have you tried recording a macro of what you want to format? You can then adapt the code it generates

Not sure about character limits, in access you can apply an input mask or in the control before update event a bit of vba code along the lines of

if len(mycontrol.text) >10 then do something

 

[goncalo]

i tried it just now and this is the result i got

Sub d()
'
' d Macro
'

'
    Selection.ShapeRange(1).TextFrame2.TextRange.Characters.Text = "15/02/1999"
    Selection.ShapeRange(1).TextFrame2.TextRange.Characters(1, 10).ParagraphFormat. _
        FirstLineIndent = 0
    With Selection.ShapeRange(1).TextFrame2.TextRange.Characters(1, 10).Font
        .NameComplexScript = "+mn-cs"
        .NameFarEast = "+mn-ea"
        .Fill.Visible = msoTrue
        .Fill.ForeColor.ObjectThemeColor = msoThemeColorDark1
        .Fill.ForeColor.TintAndShade = 0
        .Fill.ForeColor.Brightness = 0
        .Fill.Transparency = 0
        .Fill.Solid
        .Size = 11
        .Name = "+mn-lt"
    End With
End Sub

i basically just wrote a random date into the text box with the format i wanted

 

[CJ_London]

You need to set the format property - see on the ribbon, you can choose number and date formats