Textbox character restriction and input 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

    
    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've heard that if you create a text box programaticaly you cannot really do the type of formatation i desire,if that is the case i do not mind inserting a textbox manually and coding it
any kind of help or tip is appreciated,thank you for reading!

 

[The_Doc_Man]

Take a look at this article. Since you are ready to manipulate other textbox properties with VBA, this might be helpful.

TextBox.InputMask property (Access)

Office VBA reference topic

learn.microsoft.com

 

[goncalo]

this might just be exactly what i needed!
ill give it a try and report back,thank you doc!