input $ values in text boxes

[warrenelec]

Hi,

I'm pretty new to VB and have pretty much taught myself, so this should be pretty easy. I have created a pricing program in MS Excel w VB macros where I would like the user to input variables into a form, then variables would be entered into cells. I would like to:

A) allow the user to only enter numerical data into the text box
B) have the text box display the entered values in dollars & cents format

Some of the text box values are retrieved from the spreadsheet, as I have programmed extensively in excel.
When these values are displayed in the text box, it is formatted 26.2 instead of $26.20.

example of code:

If ComboBox11 = "Bolts" Then
ComboBox6.Text = "adder"
TextBox6.Text = Worksheets("PRICE").Range("BA34")
End If

Any easy way to format cell BA34 to display as currency?

I have searched the web, however this seems to be a little different from other applications.

Any help is appreciated!

Warren

 

[johnwm]

The easiest way of getting syntax is often to record a macro, then dissect the code to polish it. In this case:

Range("BA34").NumberFormat = "$#,##0.00"

Good Luck
johnwm
________________________________________________________
To get the best from these forums read faq731-376 before posting

Steam Engine enthusiasts:

http://www.essexsteam.co.uk

 

[handleman]

I'm not really clear on what you're trying to do. Is the cell on the spreadsheet already formatted as currency? I don't think the VBA forms will allow your text box to have a currency format. You can, however, set an AfterUpdate event on that box to format the text it contains.

As far as reading values from the spreadsheet into the form's text box, your code could be:

If ComboBox11 = "Bolts" Then
ComboBox6.Text = "adder"
TextBox6.Text = Worksheets("PRICE").Range("BA34").Text
End If

The default property for a cell is .Value. Therefore, when you just reference the cell, you get its value, which is 26.2 in your example. However, if cell BA34 is formatted as currency, the .Text property of the cell should return the text displayed in the cell, or $26.20