User Form And Formatted Currency

[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!

 

[Arash_The_A_Level_Student]

I need to know this please help anyone!

 

[Mudface]

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

 

[Arash_The_A_Level_Student]

I don't mean to soubnd rude but could you please elaborate on this? thanks Arash

 

[Mudface]

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.

 

[Arash_The_A_Level_Student]

Yeah I made the user form in the VBA editor, I just don't know where to put the code

 

[Mudface]

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.

 

[Arash_The_A_Level_Student]

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

 

[Mudface]

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

 

[Arash_The_A_Level_Student]

Thanks alot I have a feeling this will help thank you for your time!