User Form And Formatted Currency

G

Guest

Guest
I have made a data form for my excel invoice template it runs at the start up and when you enter the data into the form it transfers it to the cells I have specified, however I want the cells I have entered data to from my form specified as currency but when I highlight them and click currency so the next time it will be made currency I save the workbook and exit. I load the workbook up again and the data form pops up, I enter data into it and it is entered still as number I don't know what I'm doing please help!
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
Maybe not quite what you need but it should get you started- the following will transfer what you've put into TextBox1 on your userform to cell A1 on the active worksheet and format it as currency, once you click the command button: -

Private Sub CommandButton1_Click()

If Not IsNumeric(TextBox1.Text) Then
TextBox1.Text = ""
MsgBox "Please enter a number"
TextBox1.SetFocus
Exit Sub
End If
ActiveSheet.Range("A1").Value = FormatCurrency(TextBox1.Text)

End Sub
 
Upvote 0
Sorry, I was presuming you'd made a UserForm in the VBA editor. If you're just using an automatic form (from the Data-Form menu), then just type in your values as a number on the form. Select the column(s) where you want the data to appear, click on Format-Cells and select currency. Hope that's what you meant.
 
Upvote 0
OK, start again :).

Presumably, your form has one or more textboxes to enter your data into and a command button which, when clicked, transfers your data to the worksheet?

If so, enter the code above into the command button's click event (this is automatically generated when you double click on the command button in design mode). Modify the code if you have called your textbox something else.

If you're still having problems, please be more specific about what you've done, what's on your form and what you've called each control. Post your existing code too, as it's much easier to respond when you have all the info.
 
Upvote 0
sorry I can't really get what I'm trying to say out here is my VBA code:

Private Sub cmdEnterD_Click()
Sheet1.Range("g18").Value = txtCollection
Unload Me
End Sub

Private Sub Label1_Click()

End Sub

Private Sub Label2_Click()

End Sub

Private Sub txtGrossW_Change()

End Sub

Private Sub UserForm_Click()

End Sub
 
Upvote 0
Aha- amend your first sub to this: -

Private Sub cmdEnterD_Click()

If Not IsNumeric(txtCollection.Text) Then
txtCollection.Text = ""
MsgBox "Please enter a number"
txtCollection.SetFocus
Exit Sub
End If
ActiveSheet.Range("g18").Value = FormatCurrency(txtCollection.Text)
Unload Me

End Sub
 
Upvote 0

Forum statistics

Threads
1,213,485
Messages
6,113,931
Members
448,533
Latest member
thietbibeboiwasaco

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
Back
Top