How to format TextBox output as numerical

[knarf]

I am making a user form in Excel 2007 and for some reason where I am using textboxes for a user to enter numeric values the output on the spreadsheet has a general format which is causing an IF function to return an incorrect value. Is there a way to make sure the output from the form is in "numeric" format?

Thanks,

Frank

[shg]

Unlike cells, textboxes hold text -- that's it. It can look like a number, text that looks like numbers can be coerced to numbers, but what's there is text.

That said, I don't understand what you're trying to do.

[knarf]

I am creating a user form that will populate a spreadsheet for estimating. I am new to VBA and did not see another way to create a spot for a user to enter a numerical value. Should I be using a combo box instead?

Thank you,


Frank

[shg]

All of those controls contain text, not numbers, but that's not an impediment to accomplishing whatever you're trying to do -- I just don't know what it is.

Please Login or Register  to view this content.

If the textbox has a numeric string (i.e., one that Excel can interpret as a number), it will be converted to a number when it arrives in the cell.

[knarf]

I'm not where I can send you the file right now, but basically I am creating a user form that asks the user for numeric values that then are populated to a spreadsheet. The spreadsheet then uses the values from the form to calculate costs. All the calculations are working out OK except for one that uses an IF function to determine what value should be returned. The IF function asks IF"cell" >40,3,2.

No matter the value that is in the cell from the User Form the IF statement returns 3. If I directly write the correct value in the "cell" then all is well.

Also an interesting side note, when I select a group of "numbers" populated by the Form all I get is a "count" on the bar at the bottom of the screen, no total or average values. This is what tipped me off to the fact that these "values" are not being recognized as "Numbers" even though they appear as "Numbers".

I am brand new to VBA so please forgive me if I have made a basic error.

Any help I can get is greatly appreciated.

Frank

[shg]

I can't do it blind, Frank.

Post a skinnied-down example.

[knarf]

I have attached the sheet. If you can give me a small example how to format the text as numbers in my code I hopefully can figure it out from there.

Thank you for your help,

Frank

[shg]

Change the Click event to this:

Please Login or Register  to view this content.

Instead of using the default names of all those controls, they can (should) have descriptive names, like btnSave instead of CommandButton1, txtCabID instead of TextBox1, ... to make your code more readable. You can change the names in the Properties window.

[knarf]

SHG,

Thank you for the help with this problem. I'm sorry I was not better able to articulate what I was trying to accomplish in my earlier posts. This worked quite well and I see how more descriptive labels on the text/list boxes makes the code writing much simpler.


I am waiting on 2 books on VBA for Excel so I am having to figure all this out from an Excel manual and help on the internet.

If you have the time can you explain what the code you gave me is doing so that I might understand what it was we changed?

If you don't have the time I understand.

Also, how do I mark this "Solved"?



Thaks again,

Frank

[shg]

This

Please Login or Register  to view this content.

... says starting from B5 on that sheet, do the same thing in VBA as you would do by pressing selecting B5 and pressing Ctrl+Down, i.e., go to the last cell before a blank cell in that column. The Offset(1) says go down one more cell (to the first blank cell). EntireRow means reference the entire row that that first blank cell is in. That reference (the whole row of the first blank cell below B5) then qualifies all the references that follow.

Please Login or Register  to view this content.

That says set the value of the third cell in that row (i.e., col C) to the value of the text box.

Also, how do I mark this "Solved"?

For that and other useful information, see the Forum Rules

[knarf]

Once again thank you for your time.