excel input mask for textbox

[DaveF]

can anyone help me on this textbox issue:

Private Sub IBAN1_AfterUpdate()

IBAN1.Text = Format(IBAN1.Value, "#### #### #### #### #### ####
#### #### ##")

End Sub


when the user enters numbers, the textbox is change the input.

example 1255 2222 3333 3666 5555 6666 99999 88888 88

1255 2222 3333 3666 0000 0000 0000 0000 00

any ideas

 

[Dave Peterson]

I'd use:

Option Explicit
Private Sub IBAN1_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
With Me.IBAN1
If IsNumeric(.Value) Then
.Value = Format(CDec(.Value), _
"#### #### #### #### #### #### #### #### ##")
Else
Beep
Cancel = True 'don't let them leave the textbox.
End If
End With
End Sub

I switched to _BeforeUpdate, too.

If you have a Cancel button on your userform, make sure its .takefocusonclick is
false.

 

[DaveF]

Thanks Dave,

One more question.

If I want to allow letters in place of number what would I do?

Example

GB23 4445 6666 7777 LD94 4994 44

Thanks in advance

 

[Dave Peterson]

I'd just parse it out using a bunch of left's, mid's, right's.
& mid(.value,5,4) & " " _
& mid(.value,9,4) & " " _
'etc...