Free Microsoft Excel 2013 Quick Reference

Format textbox in userform for currency

I have a 2 textboxes in a userform. One (txtCPU1) will display the result of a lookup, and the other (txtCost1) will display the result of a calculation.. How can I format the textboxs to have the values be in currency (ie $24.45) with the dollar sign and 2 decimals.
Here is the code right now for the change:

Private Sub txtCPU1_Change()
    If txtQty1.Value = "" Then Exit Sub
    If txtCPU1.Value = "" Then Exit Sub
    txtCost1.Value = CDbl(txtQty1.Value) * CDbl(txtCPU1.Value)
End Sub
The txtQty1 box is already limited to to a number, and the lookup value is a currency amount from a worksheet. Thanks for your help.

Post your answer or comment

comments powered by Disqus
I thought that the textbox in a Userform would display the formatting of the Control Source cell.
My program looks up a date and pops it into a cell (ab2), which is the Source for my Userform textbox. The cell(ab2) is formatted to show the day of the week and the date (for example "Monday, 12 December 2005").

The textbox shows this as "12/12/2005"

I tried this and it doesn't work -

    txtdate = FormatDate(vblongDate) 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
My book doesn't address this and I searched the forum already without result.


I have a userform which contains textboxes with calculations. For
example, I have a statement that requires the user to input data to be
used to calculate inventory turns. Once the data is entered, a formula
will calculate the number of inventory turns and will display in a
separate textbox. Is there a way to format the textbox so the formula
is in "comma" style? Currently, the calculation returns 5 or 6 places
to the left of the decimal. Is there a better control other than a
"textbox" to perform this calculation or is there a way to format the
textbox to show only 2 places after the decimal?

Thank you


I have userform 1 with textboxe "name" and buttons. one of the buttoms is called "remove" when this button is clicked it brings up userforms 2. On this form i have the same textbox as userform 1 "name" but with a checkbox beside the textbox.

What i am attempting is for the user click remove button on userform 1, which will bring up userform2. now they will check the field "name" from userform 2 (as the data show in the textbox "name" will be linked to "name" in userform 1 therefore data will be the same) which they want removed and then click the button remove on userform 2 which will clear the textbox which is shown on both userform 1 and 2.

I want it to clear that textbox so when they click close, the data is no longer be in there.

So basically i need to know how to:
check checkbox to clear textbox in userform2
Link the textboxes in userform 1 and 2 so when data is removed from userform 2 it will also romove from same textbox -"name" in userform1

And finally: the textbox from userform1, enters data into a specified cell on worksheet called "details1" now i want it so when i click remove on userform2, it will remove the particular textbox data from the cell which is linked to the same box in userform1

I hope this makes some kind of sense...thanks

hi guys i wish u can help me

i have 200 learner and their details.
i have 6 column:
-access date

i did autofilter. i know i can use auto filter but end-user wants something looks nice so i have to use UserForm.

what i want to do is add textbox in UserForm and filter by username. end-user should be able to write username and username details should be filtered.

if u can help me guys i will apriciate


how to get the number of days between two date formatted fields in vba for


I am creating a pricing userform which takes a value from 'Sheet1'!A1 and that cell is formatted as currency. On UserForm1, I have dropped in a textbox with a ControlSource of 'Sheet1'!A1. I would like the value to be formatted as currency when it appears in the the userform textbox as it is in the cell on the worksheet. For some reason, there is no way of doing this in the properties window.

Thanks for help.

Paul, Southampton

Hello all,

First off, thanks for taking the time to read this. I'm using Excel 2007, and I'm stumped on a userform question (mostly because I'm not sure if it's possible).

I have a userform that has a textbox in which the excel user should enter the file location; to facilitate this action, I set that textbox to show the "Insert Hyperlink" dialog box when clicked.

End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I'm wondering if, and how, it's possible to show the Insert Hyperlink dialog box, then populate the LocationTextBox in the userform with the hyperlink chosen in the Insert Hyperlink dialog box (i.e., after pressing "OK" on Insert Hyperlink dialog box, LocationTextBox is populated with the file path as text).

I realize this seems a bit of overkill, as people can just type in the file path, but if it's possible to pull off the above it would really help to make the overall project easier to use for folks who are a bit Excel-unfriendly. Thanks in advance for any help.

Hi - I want to validate that the number entered in the textbox is >1. How can prevent the user from proceeding to the next textbox in the userform until they enter a + number? Thanks,

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)

If TextBox1.Value < 1 Then
MsgBox "error"
TextBox1.Value = Format(TextBox1, "#,### USF")
End If

End Sub

You want to use the Initialize event for the userform.
The code would be something like this:

Private Sub UserForm_Initialize()
TextBox1.Text = Worksheets("Sheet1").Range("A1").Value
End Sub

You will need to use the actual names for your textbox and
>-----Original Message-----
>Dear VBA Experts,
>I have a UserForm where I want to have a textbox
>with an amount in a cell. I have tried the ControlSource
>= the cell or the Value = the cell and am getting an
>message. Assuming I have a number in cell A1, what is
>code to have that info populate a textbox in a userform
>that when the userform is opened, the textbox already
>shows that amount? Thanks in advance.

The user fills in Textbox 40 in Userform7, then clicks a CMD button to
execute a macro, which brings up UserForm11 for the user to interact
with. UF7 stays up during this, because he will need to continue with
another function within that UserForm.

So, when the user is done with UF11, UF7 is still there for him to
continue with. Is there a way for Textbox40 to be cleared when it comes
back? I can't unload, then reload it, since there will be other
textboxes in UF7 with data that will need to be processed later on.

I have this change event code for the textbox now.
Private Sub TextBox40_Change()
EditPOVal = TextBox40.Value
End Sub

I've tried putting TextBox40.Text = "" in various places,
but it doesn't react correctly.

Any suggestions?


I have 2 textboxes in a userform that I use to create a spreadsheet.
For some reason, I have to define the variables they get twice, once in
the textbox code, and once in the macro. I haven't had to do that
before, and can't figure out why it's this way now. If I remark out
either set of definitions, the macro hangs up one way or another. The
code for the textboxes is:

Private Sub tbFacilName_Change()
sFacilName = tbFacilName.Text

End Sub

Private Sub tbFacilRows_Change()
lFacilRows = tbFacilRows.Value

End Sub

The macro is:

Sub CreateTribalSheetR1()
Dim sFacilName As String
Dim lNextRow As Long
Dim lBaseRow As Long
Dim lLimitRow As Long
Dim lFacilName As Long
Dim lPrevSumRow As Long
Dim ws As Worksheet

lNextRow = 2
lBaseRow = lNextRow
lPrevSumRow = 1

Set ws = ActiveSheet

' Enter column headers from Source sheet

' How to select current sheet?


If vbCancel = True Then Exit Sub
sFacilName = frmFacil.tbFacilName.Text

lFacilRows = frmFacil.tbFacilRows.Value

If sFacilName <> "" Then
lLimitRow = lBaseRow + lFacilRows
lPrevSumRow = lNextRow
Do Until lNextRow = lLimitRow
With ActiveSheet
.Cells(lNextRow, 2) = sFacilName

' insert formula
=IF(G2<>"",DATEDIF(G2,H2,"d")+1,"") with lPrevSumRow as the row
.Cells(lNextRow, "I").Formula = "=IF(G" & lNextRow &
"<>"""",DATEDIF(G" _
& lNextRow & ",H" & lNextRow & ",""d"")+1,"""")"

lNextRow = lNextRow + 1
End With

' Enter Totals row
With ActiveSheet
.Cells(lNextRow, "H") = "Totals"
.Cells(lNextRow, "I").Formula = "=Sum(i" & lPrevSumRow &
":i" _
& lNextRow - 1 & ")"
.Range("I" & lNextRow).Select
Selection.AutoFill Destination:=Range("I" & lNextRow & ":M"
& lNextRow), Type:=xlFillDefault

'Enter row totals
' =SUM(J4:M4)
.Cells(lPrevSumRow, "n").Formula = "=sum(j" & lPrevSumRow &
":m" & lPrevSumRow & ")"

.Range("n" & lPrevSumRow).Select
Selection.AutoFill Destination:=Range("n" & lPrevSumRow &
":n" _
& lNextRow), Type:=xlFillDefault

' Color totals row yellow
.Range("A" & lNextRow & ":" & "N" & lNextRow).Select
Selection.Interior.ColorIndex = 6

End With

lFacilName = MsgBox("Do you have more facility names?", vbYesNo)
If lFacilName = vbYes Then
GoTo EnterFacilNames

' Add monthly totals to bottom of sheet
' =SUMIF($H$2:$H$8,"totals",I2:I8)
With ActiveSheet
.Cells(lNextRow, "H") = "Monthly Totals"
.Cells(lNextRow, "I").Formula = "=SUMIF($h$2:$h$" & _
lNextRow - 1 & ",""totals"",i2:i" & lNextRow - 1 &
Range("I" & lNextRow).Select
Selection.AutoFill Destination:=Range("I" & lNextRow &
":n" _
& lNextRow), Type:=xlFillDefault
End With

Exit Sub

End If
End If

lNextRow = lNextRow + 1
lBaseRow = lNextRow

GoTo EnterFacilNames

End Sub

I'm not sure there's anything wrong with it, just seems strange having
to define sFacilName and lFacilRows twice. Any ideas on this?


I have created a userform with a listbox and a textbox. The listbox lists the names of all the ListObjects on the active worksheet. When one is selected, the listObject's contents are displayed in the textbox for editing. I need to be able to copy single columns of numbers from other workbooks and paste them into the textbox in my userform. This is difficult, as all other workbooks are locked out when the userform is displayed, and even if I copy the cells before displaying the userform, the cells seem to get dropped from the clipboard before I can hit the "paste" button that I added to the form. Any ideas?


I want to have to the instructions "Type Last Name Here" inside of a textbox in a userform. When info is added I want the instructions to disappear. The regular font is Arial 12pt. Is it possible to have the "Type Last Name Here" appear in anitalic and different font? Thank you, Chris.

I'm trying to calculate input from textboxes in userform with multipage and the result to be on another textbox.
Here is what I want to accomplish. Each is an individual textbox.
factor1*factor2+factor3/factor4 and the answer

I appreciate any help.

Hi all,

Simple question.........

is it possible to allow a user to paste data into a textbox in a userform either by right click>paste or edit>paste?


Maybe I have been staring at this way to long, because I cant make it work and I think I am starting to over complicate it.

I have a userform that I want to pull a specified number into a textbox.

The number comes from a sheet that is setup accordingly.
Row 1 Date # X y

I have been mostly recently working with a filter concept of:

Selection.AutoFilter Field:=4, Criteria1:="y"
Selection.AutoFilter Field:=3, Criteria1:="x"
The question-How do I denote the Range("B?????").Select, so that it corresponds.

I want # to appear in userform.textbox when x and y meet certain criteria, which are already entered in userform.

A column has built-in values in rows which I like to show upon entering new values in textboxes in userform

How can I have a textbox in a userform show the current value of a cell in the worksheet?


i have a textbox in a userform that is linked tot cell A1 of the spreadsheet.
Cell A1 is defined as %.
When the user starts the userform the textbox will show 0,04 corresponding to
4% in cell A1.
However, if the userform is empty and the user types in 4 in the textbox,
cell A1 will show 400%.

how can i make sure that the input in a textbox will be seen as a percentage
so that if the input is 4, cell A1 will show 4% ?


Message posted via

Dear Members,

I am using a worksheet, as a database table, and using a user form to fill the data into the worksheet. I am trying to save the data into the worksheet, by checking all the blank text boxes and presenting the user with choice of yes or no message box and let user decide the next action like the below scenario

I am using the following code, which checks the blank text boxes and shows ok message. It dont allow to save untill all the balnk text boxes will be filled. But i want the follwing scenario

[b]If I click the save button on the user form, if there is any blank textbox in the user form, a message box with “YES” “NO” buttons has to be pop up, with message like example “ Customer name was not entered Do you want to enter ”.

If I click “YES”, it has to go to the blank field. If I click “NO”, it has exit the message box and go to the next blank field if any. It has to check all blank fields, and then only save the data into the worksheet table.

I am sending the following code, which I am using.

I am not sure what is the exact vba code for the above scenario

Any help in this regard is greatly appreciated

I am attaching the excel file with user form, to check my code

I am using the following CODE Currently:

Private Sub cmdsave_Click()
Dim irow As Long
Dim ws As Worksheet
Set ws = Worksheets("customerDetails")

'find first empty row in database
irow = ws.Cells(Rows.Count, 3) _
.End(xlUp).Offset(1, 0).Row

'check for customer name
If Trim(Me.txtcustname.Value) = "" Then
MsgBox "Please enter the Customer Name", vbExclamation, "Customer Entry"
Exit Sub

End If

' code to check the blank textboxes 

'check for invoice address 1
If Trim(Me.txtinvad1.Value) = "" Then
MsgBox "Please enter the Invoice Address 1", vbExclamation, "Customer Entry"
Exit Sub
End If

'check for invoice address 2
If Trim(Me.txtinvad2.Value) = "" Then
MsgBox "Please enter the Invoice Address 2", vbExclamation, "Customer Entry"
Exit Sub
End If

'check for invoice address 3
If Trim(Me.txtinvad3.Value) = "" Then
MsgBox "Please enter the Invoice Address 3", vbExclamation, "Customer Entry"
Exit Sub
End If

'check for Delivery Address 1
If Trim(Me.txtdelyad1.Value) = "" Then
MsgBox "Please enter the Delivery Address 1", vbExclamation, "Customer Entry"
Exit Sub
End If

'check for Delivery Address 2
If Trim(Me.txtdelyad2.Value) = "" Then
MsgBox "Please enter the Delivery Address 2", vbExclamation, "Customer Entry"
Exit Sub
End If

'check for Delivery Address 3
If Trim(Me.txtdelyad3.Value) = "" Then
MsgBox "Please enter the Delivery Address 3", vbExclamation, "Customer Entry"
Exit Sub
End If

'check for YOUR CST / ST TIN NO / CST TIN
If Trim(Me.txtcstno.Value) = "" Then
MsgBox "Please enter the YOUR CST / ST TIN NO / CST TIN", vbExclamation, "Customer Entry"
Exit Sub
End If

'check for YOUR TIN / VAT NO / LST TIN
If Trim(Me.txttinno.Value) = "" Then
MsgBox "Please enter the YOUR TIN / VAT NO / LST TIN", vbExclamation, "Customer Entry"
Exit Sub
End If

'check for DL NO 1
If Trim(Me.txtdlno1.Value) = "" Then
MsgBox "Please enter YOUR DL NO 1", vbExclamation, "Customer Entry"
Exit Sub
End If

'check for DL NO 2
If Trim(Me.txtdlno2.Value) = "" Then
MsgBox "Please enter YOUR DL NO 2", vbExclamation, "Customer Entry"
Exit Sub
End If

'check for DL NO 2
If Trim(Me.txtdlno2.Value) = "" Then
MsgBox "Please enter YOUR DL NO 2", vbExclamation, "Customer Entry"
Exit Sub
End If

'check for Insurance Number
If Trim(Me.txtins.Value) = "" Then
MsgBox "Please enter INSURANCE POLICY NUMBER", vbExclamation, "Customer Entry"
Exit Sub
End If

'check for ECC NO
If Trim(Me.txteccno.Value) = "" Then
MsgBox "Please enter ECC NO", vbExclamation, "Customer Entry"
Exit Sub
End If

'check for ST NO / LST NO
If Trim(Me.txtstno.Value) = "" Then
MsgBox "Please enter ST NO / LST NO", vbExclamation, "Customer Entry"
Exit Sub
End If

'check for CST TIN NO
If Trim(Me.txtcsttinno.Value) = "" Then
MsgBox "Please enter CST TIN NO", vbExclamation, "Customer Entry"
Exit Sub
End If

'check for PAN NO
If Trim(Me.txtpanno.Value) = "" Then
MsgBox "Please enter PAN NO", vbExclamation, "Customer Entry"
Exit Sub
End If

'save the data to the database
ws.Cells(irow, 2).Value = Me.txtcustname.Value
ws.Cells(irow, 3).Value = Me.txtinvad1.Value
ws.Cells(irow, 4).Value = Me.txtinvad2.Value
ws.Cells(irow, 5).Value = Me.txtinvad3.Value
ws.Cells(irow, 6).Value = Me.txtdelyad1.Value
ws.Cells(irow, 7).Value = Me.txtdelyad2.Value
ws.Cells(irow, 8).Value = Me.txtdelyad3.Value
ws.Cells(irow, 9).Value = Me.txtcstno.Value
ws.Cells(irow, 10).Value = Me.txttinno.Value
ws.Cells(irow, 11).Value = Me.txteccno.Value
ws.Cells(irow, 12).Value = Me.txtdlno1.Value
ws.Cells(irow, 13).Value = Me.txtdlno2.Value
ws.Cells(irow, 14).Value = Me.txtstno.Value
ws.Cells(irow, 15).Value = Me.txtcsttinno.Value
ws.Cells(irow, 16).Value = Me.txtpanno.Value
ws.Cells(irow, 17).Value = Me.txtins.Value

Me.txtcustname.Value = ""
Me.txtinvad1.Value = ""
Me.txtinvad2.Value = ""
Me.txtinvad3.Value = ""
Me.txtdelyad1.Value = ""
Me.txtdelyad2.Value = ""
Me.txtdelyad3.Value = ""
Me.txtcstno.Value = ""
Me.txttinno.Value = ""
Me.txteccno.Value = ""
Me.txtdlno1.Value = ""
Me.txtdlno2.Value = ""
Me.txtstno.Value = ""
Me.txtcsttinno.Value = ""
Me.txtpanno.Value = ""
Me.txtins.Value = ""

End Sub

I am new for VBA but can understand a little.

I have created a file having Userform with one combobox and a print button,
the excel sheet1 Col. A contains list of file full path e.g. C:UserssunilmurkikarDesktopnew1.doc, C:UserssunilmurkikarDesktopnew2.doc etc...
The combobox is linked as raw source to this column to populate the data in userform.
My intrest is to print the selected file from the given full file path in the combobox1
i have tried to search a lot on net but did'nt find the code for this.

Can any one help me on this, thanks in Advance.

Hello. Again I need your irreplaceable help.

I've just created a form with 20x20 textboxes ( Some kind of matrix ).
It is here :

And what is more I have created an variable(array), where I will store data inserted into TextBoxes by a user of my Macro.

I would like to know (if it is possible) how to automate reading data from TextBoxes and putting it into array in macro code? I hope I explained my request in a clear way.

My TextBoxes names are for example TextBox11 for a TextBox in verse nr.1 and column nr.1.

Greetings, Adam

In a textbox in my userform only one @ is allowed. How can VBA see the
second @ and delete it?

Dear experts,

I am very new to VBA and GUI creation. Please excuse me if it is a trivial question.

I need to insert a table in an userform for automating an engineering calculation. Supposedly there was an option in "additional controls" for inserting "microsoft office spreadsheet" in previous versions of excel and this feature has been removed in the latest excels.

1. Now, how do I insert such a table in the lastest version? Please find attached the jpeg. When the user inputs the number of bolts in the space provided, say 25 bolts, I would like a 25 X 6 table to appear below it. In this I would want the 2nd and 3rd coloumn as a dropdown (2nd coloumn for direction of bolt with options +x, -x, +y, -y, +z and -z; 3rd coloumn coloumn with dropdown 1,2 and 3). Coloumns 1,4, 5, and 6 will be filled by the user.

2. Once the table is filled by the user, I will put it in one more command button at the bottom, which, when clicked, should save the table as a "tab seperated value" file. Is this possbile?

Can you please let me know how I can achieve these or point me to a relevant source which will help to me achieve this?


No luck finding an answer? You could always try Google.