TextBox Formatting

[Vin90]

I've created this code to format my textbox, which is working well

Private Sub UserForm_Initialize()
    TextBox1.Value = Format(Date, "mm/dd/yyyy")
    TextBox6.Value = Format(Val(TextBox6.Value), "#%")
    TextBox8.Value = Format(Val(TextBox8.Value), "#%")
Ens Sub

However I noticed that once the data is being input, the %sign on TextBox6 & 8 disappear..

Private Sub cmdAdd_Click()
    
If MsgBox("Do you want to input the data?", vbYesNo) = vbYes Then
    
    'Copy input values to sheet.
    Dim lRow As Long
    Dim ws As Worksheet
    Set ws = Worksheets("Data")
    lRow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
With ws
        .Cells(lRow, 11).Value = Me.TextBox6.Value
        .Cells(lRow, 12).Value = Me.TextBox7.Value
        .Cells(lRow, 13).Value = Me.TextBox8.Value
End With
    Me.TextBox6.Value = ""
    Me.TextBox7.Value = ""
    Me.TextBox8.Value = ""



Else
End If
End Sub

It may have something to do with the Me.TextBox6.Value = "" code, is there any suggestion to keep the formatting while clearing up the value inside?

Regards,
vin

 

[Rick Rothstein]

TextBoxes do not work like cells... you can pre-format them and expect the format to remain. What you need to do when assigning a value that should have a format is to apply the Format function to the value as you assign it to the TextBox (just like you did in the Initialize event procedure).

 

[Vin90]

Thanks for the swift reply

Tried to tweak the code to be like the initialize (if this is what you meant)..

    Me.TextBox6.Value = Format(Val(TextBox6.Value), "#%")
    Me.TextBox7.Value = ""
    Me.TextBox8.Value = Format(Val(TextBox8.Value), "#%")
    Me.TextBox9.Value = ""

Open the form, I only have %, which is great. Then, I key in 1% (for example), and after data input i get 100% instead of %.
Any way to make it %?

I'm very new to this coding thing, so need extra guidance =(

 

[Rick Rothstein]

Thanks for the swift reply

Tried to tweak the code to be like the initialize (if this is what you meant)..

    Me.TextBox6.Value = Format(Val(TextBox6.Value), "#%")
    Me.TextBox7.Value = ""
    Me.TextBox8.Value = Format(Val(TextBox8.Value), "#%")
    Me.TextBox9.Value = ""

Open the form, I only have %, which is great. Then, I key in 1% (for example), and after data input i get 100% instead of %.
Any way to make it %?

I'm very new to this coding thing, so need extra guidance =(

The problem is not in the coding... it is in understanding what a percentage is. The number 1 as a percentage is 100%... 1/100 as a percentage is 1%. If you want to input the whole number "percentage" and still have a percent sign attached to it, you can do it in one of two ways; either like this...

Me.TextBox6.Value = Me.TextBox6.Value & "%"

or like this...

Me.TextBox6.Value = Format(Me.TextBox6.Value, "0\%")

 

[Vin90]

Hi Rick, thanks for the swift reply.. I will give a try to the given coding..

I'm just curious what is the difference between having and not having the Format code there?

Cheers

 

[Rick Rothstein]

Hi Rick, thanks for the swift reply.. I will give a try to the given coding..

I'm just curious what is the difference between having and not having the Format code there?

Without the Format, the code line takes whatever you type and concatenates a percent sign onto the end of it. So if you type 12, the code changes what is in the TextBox to 12%. With Format, the number you type, let's say 12, is converted to a real percentage value (because of the % sign in the second argument)... in order to convert a number to a real percentage, that number get multiplied by 100 first. Let's look at it backwards to see the problem. Type 12% into a cell... Excel shows you 12%... that is a real percent... change the cell format to General and you will see the number in the cell is 0.12. Now think of that in reverse... type 0.12 into a cell and then change its cell format to Percent... you get 12%. That conversion is what Format does when you use the % sign in the second argument.

 

[Vin90]

Hi @Rick Rothstein

I understood the concept now, and I think the 2nd option is much a better option..

I tried to divide the textbox6.value by 100 but I get a type mismatch error.

[COLOR=#333333]Me.TextBox6.Value = Format(Me.TextBox6.Value/100, "percentage")[/COLOR]

Why is that?

Cheers

 

[Rick Rothstein]

I tried to divide the textbox6.value by 100 but I get a type mismatch error.

[COLOR=#333333]Me.TextBox6.Value = Format(Me.TextBox6.Value/100, "percentage")[/COLOR]

Why is that?

I cannot reproduce that error message, so I am not sure why you are getting it. However, while it won't produce that error, you do have a syntax problem for the Format function... "percentage" is not a valid "pattern" to use as an argument. While "General" is, no other word (that I can think of) is... what you have to do is figure out how many decimal places you want and whether you want to force a leading zero for fractions less than 1 and whether you want trailing zeroes after the decimal point if the fraction is to short to fill all the decimal places you provide for. Let's say you want the leading and two trailing zeroes when needed, then you would write your code line this way for output as a percent...

TextBox6.Value = Format(TextBox6.Value / 100, "0.00%")