Textbox Formatting Issue

CheekyDevil2386

New Member
Joined
Oct 19, 2020
Messages
4
Office Version
  1. 2013
Platform
  1. Windows
Hi all,

Having an issue with the below code. It works fine except with numbers starting with less than 12 for some reason.

For context, it is meant to change a string of numbers entered in a UserForm Textbox into "account format" eg, 123456789012300 would become 012-3456-7890123-00 (I have it set to enter the leading "0" automatically as that is how our system formats account numbers)

Any number greater than 1 to begin with works fine, but when entering a number such as 123224 for example will change the number in the textbox to something random (I entered "123456" and it changed to "56-8654--")

Really can't understand why it is changing the digits entered, and only for those beginning with 1.

Any assistance would be greatly appreciated.

VBA Code:
Private Sub TextBox4_Change()
 Dim Account As String
 Dim Length As Integer
 Account = TextBox4.Text
 Length = Len(Account)
 Select Case Length
 
 Case 2
 TextBox4.Text = Format(TextBox4, "0##") & "-"
 Case 8
 TextBox4.Text = Format(TextBox4, "##-####") & "-"
 Case 16
 TextBox4.Text = Format(TextBox4, "##-####-####") & "-"
 End Select
 
 ActiveSheet.Range("B2").Value = TextBox4.Value
End Sub
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
try this
VBA Code:
Private Sub TextBox4_Change()
 Dim Account As String
 Dim Length As Integer
 

 Account = TextBox4.Text
 Length = Len(Account)
 Select Case Length
    Case 2
    TextBox4.Text = Format(TextBox4, "0##")
    Case 3, 7, 16
    TextBox4.Text = TextBox4 & "-"
    Case Is > 19
    TextBox4.Text = Left(TextBox4.Text, 19)
 End Select
 
 ActiveSheet.Range("B2").Value = TextBox4.Value
End Sub
 
Solution

Watch MrExcel Video

Forum statistics

Threads
1,129,970
Messages
5,639,317
Members
417,081
Latest member
Jeyaraj2021

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top