Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2
Default excel input mask for textbox

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
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 35,218
Default excel input mask for textbox

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 wrote:

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
  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2
Default excel input mask for textbox



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





On Feb 15, 1:31*pm, Dave Peterson wrote:
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 wrote:

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- Hide quoted text -

- Show quoted text -


  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 35,218
Default excel input mask for textbox

I'd just parse it out using a bunch of left's, mid's, right's.

With Me.IBAN1
If len(.Value) = 26 Then
.Value = left(.value,4) & " " _

& mid(.value,5,4) & " " _
& mid(.value,9,4) & " " _
'etc...




DaveF wrote:

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

On Feb 15, 1:31 pm, Dave Peterson wrote:
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 wrote:

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- Hide quoted text -

- Show quoted text -


--

Dave Peterson
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Need A date Mask format for a Textbox on UserForm [email protected] Excel Discussion (Misc queries) 2 November 4th 07 03:27 AM
Input Mask Steven Excel Worksheet Functions 1 January 17th 06 12:27 AM
input mask in excel Osama Mira Excel Worksheet Functions 6 December 6th 05 11:00 PM
input mask in excel sheet Osama Mira Excel Worksheet Functions 1 December 4th 05 06:59 PM
Input Mask Jimmy Clay New Users to Excel 3 December 1st 04 02:16 AM


All times are GMT +1. The time now is 03:34 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"