Textbox to accept 12 digits

[janjan_376]

I need help ont the below code. I need to change this code to accept 12 digits. Please help.

Private Sub TextBox1_KeyPress(ByVal KeyAscii As
MSForms.ReturnInteger)
****Range("A65536").End(xlUp).Offset(1, 0).Value = Chr(KeyAscii)
****TextBox1.Value = ""
End Sub

[royUK]

I'm not sure what your code is doing. Where is the textbox?

[janjan_376]

This code is put into the TEXTBOX. TEXTBOX has to be created then the code is put into the code section

[Leith Ross]

Hello JanJan,

If you want to restrict the TextBox to accept a maximum of 12 characters then set the MaxLength property to 12 then you won't need the event code.

Sincerely,
Leith Ross

[janjan_376]

The event is used to make to jump to another cell. If I remove the event and just put MaxLength property to 12, it only accepts 12 digits but does not input it automatically to a cell.

[royUK]

I know the code is in the textbox, but what type of textbox

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    With Me.TextBox1
        If Len(.Value) > 12 Then
            .Value = Empty
            .SetFocus
            Exit Sub
            End If
        End With
End Sub

[janjan_376]

Thank you for the respond and for the code. I tried the code but no luck. I mean I could enter 12 digit number but that's it, it does not go anywhere.

Type of textbox ? -- This textbox was created from Control Toolbox.

The code I mentioned before is really really good but I just don't know how to make it to accept 12 digit.

Would you like me to send you an attachment so that you have a good look of what I have been trying to accomplish ?

Thank you again.

[royUK]

What do you mean that it doesn't go anywhere?

Attach a small example workbook.

[janjan_376]

Please see attached file.

[royUK]

In my first post I asked where the textbox is, you never answered. Eventually from the attachment I see it is a Forms menu control on the worksheet.I've wasted so much time trying to help you because you didn't answer that request!

[janjan_376]

I did respond to your question about the textbox, I put "?" as I thought would indicate of "not too sure" and I also mentioned that it was created from Control Toolbox. I really appreciated your time and I am very sorry for the time you wasted figuring it out, because of my unfamiliarity of some of Excel functions or terminology. I did ask you if I could send you an attachment to resolve some confusion.

[royUK]

I think yopu want thisa

Private Sub TextBox1_LostFocus()
If Len(Me.TextBox1.Value) = 12 Then
Range("A65536").End(xlUp).Offset(1, 0).Value = Me.TextBox1.Value
TextBox1.Value = ""
End If

End Sub

[janjan_376]

Hi Roy,

I replaced the old code with the code you provided and it did accept 12 digit however it only registers it to the cell once I click on that cell. It does not register it when it reaches the 12th digit or even when I press enter key (which I do not have to). As you may notice on the attached file I sent, when a number is entered, it automatically registers it to the next empty cell below it. I really appreciated you time & effort on helping me figuring this out. I do believe that you can help me with this. Thank you.

[royUK]

Try using the Change event

Private Sub TextBox1_Change()
    If Len(Me.TextBox1.Value) = 12 Then
        Range("A65536").End(xlUp).Offset(1, 0).Value = val
        Me.TextBox1.Value = ""
    End If
End Sub