Free Microsoft Excel 2013 Quick Reference

VBA Userform textbox numbers formating

I have a userform with several textbox (tb1, tb2, tb3). When the form opens, the textbox are populated from values in a worksheet:


	VB:
	
 UserForm_Initialize() 
    [INDENT]tb1.Text = Format(Worksheets("Input").Cells(1, 1), "$#,##0")[/INDENT][INDENT]tb2.Text = Format(Worksheets("Input").Cells(2, 1), "0%")[/INDENT]End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
The code works, but since the textbox are being populated with numbers I'm assuming the code needs to be changed to remove the "text" part. I tried entering "number" instead of text but got an error message. (Note- "N/A" is a possible option for the values)

Is there a code for the textbox to keep its formatting when a user enters in a new number? (If they enter 12345 into tb1, the textbox goes to $12,345 when the user clicks outside of the textbox)

For tb3, I need a code that will add tb1 and tb2. If the user changes either tb1 or tb2, tb3 will automatically update

Thanks!


Hi,

I'm very new to vba and am making some simple userforms to enter data.

My problem is that in the quantity textbox of the userform, I want to change the number format depending on the item.

For example, if the unit is kg., I want the number format to be 0.000, while if it is nos., I want it to be 0.

With thanks in advance for all answers - and no answer is too simple.

Debkumar

Hi

I need to set the number format of a textbox (in a userform) to show a
percentage. How do go about this?

Good Evening: Can someone please tell me what the format would be for 3 numbers in a userform textbox. No decimals, no symbals, just the 3 numbers.
Thank You
abbeville

Hi,

As a minor problem - just wondering following:

In VBA Userform:

1. Indicating value A from sheet x in a Textbox1
2. Indicating value B from sheet x in a Textbox2

...and the problem:

3. Indicating value B/A [cell formula = %] from sheet x in a Textbox 3:

-> gives a problem: Textbox will handle the sheet value as a text - meaning that I can not get exact % value like 87 [%]...but something like 8734523525252354?

So - any ideas to deal with calculated % values in VBA userform appreciated,

Br, ToBe

I am having trouble getting a textbox to format user input in currency
When I use the following code

Private Sub TextBoxgencost_Change()
TextBoxgencost.Text = Format(TextBoxgencost.Text, "Currency")
End Sub

Or
Private Sub TextBoxgencost_Change()
TextBoxgencost.Text = Format(TextBoxgencost.Text, "$###,###,##")
End Sub

It will only allow me to enter 4 numbers and then behaves as a counter
and increases the last digit by one .

Thanks for the help

------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

Is there a simple way to format Textbox number display?

Hello

I am unable to get a userform to display a cell value as time. All it does is display it as a decimal. Can anyone suggest a solution please?

Thanks

Hi Folks,
I've been trying to get the text in my VBA Userform textboxes to wrap onto the next line when it runs into the border of the box.
How do you set it up so that it does this. I've tried the properties window and set the word wrap to true but the text still stays on one line and goes out of sight.
Any help is appreciated.

EDIT: Thanks Marvin, it worked perfectly!

This is my first posting. I have searched the threads but have not found a solution to this problem.

I am new to excel VBA and have developed a userform to input data into a spreadsheet. Some of the data that I input is numeric and I format it with the following code:

	VB:
	
 MSForms.ReturnBoolean) 
    tbstockpurchprice = Format(tbstockpurchprice, "$#,##0.00") 
End Sub 
Private Sub tbstockpurchprice_Change() 
    OnlyNumbers 
End Sub 
 
Private Sub OnlyNumbers() 
    If TypeName(Me.ActiveControl) = "TextBox" Then 
        With Me.ActiveControl 
            If Not IsNumeric(.Value) And .Value  vbNullString Then 
                MsgBox "Sorry, only numbers allowed" 
                .Value = vbNullString 
            End If 
        End With 
    End If 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Then the information is transferred to my spreadsheet with the following code in the Private Sub CommandButton1_Click routine:

	VB:
	
.Cells(6, 15).Value = Val(Me.tbstockpurchprice) 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
This does not seem to work when I format the numbers as above. The data does not transfer to the cell specified or anywhere else for that matter. If the numbers entered into the userform are not formatted as above they transfer just fine into the spreadsheet as numeric values and can be used in calculations within the spreadsheet. There seems to be a conflict between the text formatting and the Val() function. If I don't use the Val() function the text is transferred into the spreadsheet but then cannot be used in calculations.

I would like to be able to have an attractively formatted userform but I can't seem to make it work.

I would appreciate any suggestions.

I'm building a UserForm that has several TextBoxes to gather basic project information to fill in the header of a report...

The first TextBox is the Job #... I'm trying to implement code for an "Auto Fill" button (based on working VBA code from another workbook) that will fill the other TextBoxes in the UserForm...
The working VBA code from the other workbook does "Auto Fill" for Worksheet Ranges, not UserForm TextBoxes...

How should I reference the UserForm Textboxes in the code to specify their value?

Sample of experimental code, not working;

	VB:
	
Workbooks("Template Generator.xls").Forms("ProjectInfo").Activate 
If TextBox("InptJobNum").Text = "" Then 
    MsgBox "Please Insert Job Number" 
    TextBox("InptJobNum").Select 
    Exit Sub 
Else 
    Jnum = TextBox("InptJobNum") 'Specify Job Number
    Workbooks("Job Log.xls").Sheets("Job Log").Activate 
End If 
If WorksheetFunction.CountIf(Range("A2:A1000"), Jnum) = 0 Then 
    Workbooks("Template Generator.xls").Forms("ProjectInfo").Activate 
    MsgBox "Invalid Job Number, or cannot be located... Please correct and try again" 
    TextBox("InptJobNum").Select 
    Exit Sub 
End If 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


Hi, I've spent quite a while trying to limit the user's input to a userform textbox to no avail. For example one textbox on the form should only be numbers and I therefore want to restrict the user to typing in a two digit code like 02 or 72 (not for calculations). Another textbox I only want to allow the user to input 6 characters in the format letter, four numbers and a letter. If the user inputs the wrong stuff a message box pops up and the focus is reset (I can do that bit!).

Any help would be appreciated.

Validating UserForm TextBox to Only Accept Numbers If you are fairly comfortable with Excel VBA you will most likely want to design a UserForm to ensure correct use of your Spreadsheet. However, just placing some TextBoxes on a UserForm for users to enter data is not enough. For example, the TextBoxes may be intended to take only numeric data and not Text strings.

I want to create a userform in which there is a textbox in which the user will enters a number.

I would like figure a way to the number format turn into "#,##0.00" while the user inputs the number.

So if the user inputs a fourth digit to the number, it show a "," to him (1535 --> 1,535) and he still is able to keep inserting the number. And if he wants to insert a decimal value, he inserts a "." and inserts the number.

I tried two ways to doing that, but both have problems.

First:

Private Sub textbox1_Change()
textbox1 = Format(valor, "#,###")
End Sub
That works to formatting the number, but I am not able to insert the decimal part

Second:

Private
Sub textbox1_Change()
textbox1 = Format(valor, "#,##0.00")
End Sub
After I insert the first digit it formats the number, but do not allow me to keep inserting the other digits.

Does anyone know how to do this...

Thanks

Hi guys.

I have just created a UserForm with some textboxes in it.

In the UserForm_Activate I have written some code that fills in the textboxes
with data from my sheet.

Like so.

What I was wondering is if it is possible for the textbox to show the value with
some number formatting. Particularly I want no decimals and I want to use 1000 separator.

Anyone have any ideas?

Friends - a simple problem has defeated me most of the day

My Userform1 contains a text box which inherits Data inputs from ControlSource. Control Source takes its input from Sheet1: A1
When the text box is linked to the control source cell, it ignores the format code in Sheet1;A1.
How can I specify the number format of a text box that is linked to a control source cell?

Example: TextBox22 inherits input from Sheet1: Cell A1 via Control Source. In this case Sheet1:A1 contains a percentage 5.03% (example)
I want the Textbox to display 5.03% when the userform opens.
I thought the following would do it..but it renders 5.03232545554544 etc...

Private Sub
TextBox22_Initialize()
TextBox22.Value = Format(TextBox22.Value, Evaluate(TextBox22.ControlSource).NumberFormat)
End Sub
Thanks Conor

Hi,

I am working with a number of different userforms. I need to be able to pass the form name as a parameter between Subs, and then use this form name to show the form and to change / extract the values of objects on the userform (textboxes, comboboxes etc). Having looked around, and check the Microsoft Website, the VBA.Userform seems to hold the functionality I need. Is there a document describing what can be done with this, or does anyone know how to access objects on a userform that has been opened by using a variable that holds the form name.

Hope that makes sense.

Many thanks

C.

Hi All

I'm working with forms in VBA.
On my forms I have a textbox that shows (for eg) annual profits. This figure can become quite big. Can I display this output using a number format that uses a comma to separate multiples of 1000 (eg, 1,000,000,000 instead of 1000000000)

Freddy

This a new question that arised from my earlier posting in http://www.ozgrid.com/forum/showthread.php?t=86050

I have the following working code below, but am having trouble finding coding examples to select specific cells from the selected row (that was found by selecting a ComboBox value)and update TextBoxes with those individual values after the UserForm has been initialized (the bold "GREEN" comment in the code below). I have been able to find plenty of references to update TextBox values to Cells, but that doesn't do me much good in this application since the User needs to verify the old data in these cells before updating them using the UserForm TextBoxes.

I was toying around with several different variations of code (none of which worked properly), so I left it out for clarity of my working code. I'll post up this non-working code as needed, because I really wanted a fresh answer...not what I was trying to do. The attached file should be sufficient to see what's going on, but if not please ask questions.

thanks, ~Dan


	VB:
	
 UserForm_Initialize() 
     
     'Add list entries to the combo boxes. The value of each
     'box matches the existing SR Information spreadsheet entries
     
    With Sheets("SR Information") 
        .Range("A2", .Cells(Rows.Count, "A").End(xlUp)).Name = "MyRange" 
    End With 
     
    SRnumber.RowSource = "MyRange" 
     
     ' Set initial values to TextBoxes -not needed, just for testing-
     '    NBdate.Text = "Test NBD SR Number"
     '    ApartNumber.Text = "Test AP SR Number"
     '    UPdetails.Text = "Test UD SR Number"
     
End Sub 
 
 
Private Sub SRnumber_Change() 
     
    Dim ServiceRequestNumber As String 
    Dim c As Range 
    Dim rngG As Range 
    Sheets("SR Information").Select 
     
    With Selection 
         
         'Find value in ComboBox, and select row
         
        ServiceRequestNumber = SRnumber.Value 
         
        For Each c In Intersect(ActiveSheet.UsedRange, Columns("a")) 
            If c = ServiceRequestNumber Then 
                If rngG Is Nothing Then Set rngG = c.EntireRow 
                Set rngG = Union(rngG, c.EntireRow) 
            End If 
        Next c 
        rngG.Select 
         '
        [B][COLOR="Red"] ' >Insert Code Here?<  Update other TextBoxes with information contained in specific cells on the
selected row[/COLOR][/B]
         '
    End With 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


Hi Guys:

When I use the following code in a userform textbox:

TextBoxprobcost.Value = Format(TextBoxprobcost.Value, "Currency")
It locks the cell at 3 characters ($3.01)

When I use the following code:

TextBoxprobcost.Value = Format(TextBoxprobcost.Value, "$##,###.##")
It still locks the cell at 3 characters.

I cant find where I have restricted anything

Thanks for the help

I'm having a problem with this code:


	VB:
	
 TextBox11_Change() 
    On Error Resume Next 
    TextBox11.Text = Format(CCur("0" & TextBox11.Text), "$#,##0.00") 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I am trying to get the textbox to format numbers as currency with a comma and set this to work on any change event. The problem is whenever I print or print preview the sheet it reverts to a simple number format. Any ideas on what is wrong and how I can fix it?

Hi Folks,

I know I'm thick, but I just can't get the attached to work.

Its got a 2-column table, column A is named "Serial_No", and the whole table is named "Fix".

Click the button and a UserForm pops up with a ComboBox linked to Serial_No, and a TextBox in which I want to display the Tail Number associated with the Serial number picked from the ComboBox - "Piece of cake!" you all shout. You just set the TextBox.Value in the ComboBox Change() event, like this:

Private Sub cboSerialNo_Change()
txtTailNo.Value = WorksheetFunction.VLookup(cboSerialNo.Value, [Fix], 2, 0)
End Sub

But that gives an error every time, either "unable to set VLookup property of WorksheetFunction class" if I specify the range name complete with sheet! name, or "Argument not optional" if I don't!

How the Sam Hill can I fix this?

Chris

So right now my VBA code Concatenates numbers from a variable into a cell to look like

x / y / z

However, x y and z do not match the number formats I want, as they'll say 5 or 6.1... I want it to display 2 decimal places.. how do I alter this in my VBA code with respect to the variable that is used to "insert" these concatenations? It concatenates to the end every time if that matters.

I have a userform with several text boxes onit that display the contents of cells on the sheet. The cells contain a calculated value and I have formatted them to display ~ 0 ~ decimal places. They appear properly in the cells on the sheet.

In the userform textboxes however, they display decimal places as calculated; this is irritating as the textboxes are not sized to accomodate cases where the number calculates out to several decimal places.

Any suggestions?

Thanks

Hi All,

Hope you can help. I have created a userform containing labels (I use labels because I want it to be clear to the user these are givens, and not figures they can change, Next to it I have textboxes they have to fill out). The caption equals certain values from the worksheet. So far I am ok, however I don't get the format right. As it are all numbers, going into millions, I would like to use proper number format, using 1000 seperators. Can this be done? And how to do it?

This is what I have sofar:

Code:
Dim lbl198 As String

lbl198 = Sheet3.Range("B15")

UserForm4.Label198.Caption = lbl198
thanks,
Pauline