Textbox formatting questions/problems

Hey Guys (and Gals)!

New to the forum but have lurked for a while. Hopefully I can contribute as much as you guys have...guidance from this forum certainly has helped keep my sanity!!! :-p

So, let me start with a question. I've searched but have not found exactly what I'm looking for, maybe its because I'm a VB newbie & may not recognize an answer even it was staring me in the face, lol. Anyway...

I have a UserForm which moves the TextBox values to a data table when a command button is hit. The problem is that the values in the data table then show as text. I need them in numerical form so the pivot tables derived from the data table work properly. The two TextBox formats needed are 1) a normal number (temperature) and 2) a scientific number (ex: 1E-9). So, long story short, I'd like for the user to be able to enter his/her numbers in the TextBoxes & they end up as numbers (without errors) in the data table. Can someone assist me with this?

Thanks in Advance,
Jay

Hi,

You can always use the Function "Format" you can read more about it here http://www.techonthenet.com/excel/fo...mat_string.php

So your code would look something like this:

Please Login or Register  to view this content.

See if it helps

Thanks

Originally Posted by fredlo2010

Hi,

You can always use the Function "Format" you can read more about it here http://www.techonthenet.com/excel/fo...mat_string.php

So your code would look something like this:

Please Login or Register  to view this content.

See if it helps

Thanks

Thanks! I will definitely check that out when I have time...

So this would be inserted somewhere in my command button script (see below)?
Private Sub CommandButtonSaveAndClose_Click()
Dim nextrow As Integer
'Moves data from userform to the data table
nextrow = WorksheetFunction.CountA(Sheets("BakeDataTable").Range("E:E")) + 6
Sheets("BakeDataTable").Cells(nextrow, 5) = TextBoxDate
Sheets("BakeDataTable").Cells(nextrow, 6) = TextBoxBakeTemp
Sheets("BakeDataTable").Cells(nextrow, 7) = TextBoxInternalTemp
Sheets("BakeDataTable").Cells(nextrow, 8) = TextBoxDepoPressure
Sheets("BakeDataTable").Cells(nextrow, 9) = TextBox2Hydrogen
Sheets("BakeDataTable").Cells(nextrow, 10) = TextBox18Water
Sheets("BakeDataTable").Cells(nextrow, 11) = TextBox28Nitrogen
Sheets("BakeDataTable").Cells(nextrow, 12) = TextBox32Oxygen
Sheets("BakeDataTable").Cells(nextrow, 13) = TextBox62P2
Sheets("BakeDataTable").Cells(nextrow, 14) = TextBox75As
Sheets("BakeDataTable").Cells(nextrow, 15) = TextBox91AsO
Sheets("BakeDataTable").Cells(nextrow, 16) = TextBox124P4

Unload UserFormNewEntry
End Sub

Hello,

The final code should look like this. If I am missing anything you can add it in a similar fashion. Also I modified a little the way you find the last row of data.

Please Login or Register  to view this content.

Thanks

Originally Posted by fredlo2010

Hello,

The final code should look like this. If I am missing anything you can add it in a similar fashion. Also I modified a little the way you find the last row of data.

Please Login or Register  to view this content.

Thanks

Great! I appreciate the help. Looks like that has the regular numbers (temps) taken care of. The Scientific numbers are still showing as text (errors) when added to the data table. Any ideas what can be done there? The users usually enter these as scientific (i.e. 1.00E-9). Again, thanks for the help...

I don't know what field are supposed to be in scientific so i didnt program it.

Ah, I see now! My son had me preoccupied earlier so I didn't catch that part of the changes you made. Just gave it a try and it works great!!!

Thanks,
Jay

I am glad I helped. I should have made it pop out a little.

After a little testing, I went back to the following to move the data to the table:
nextrow = WorksheetFunction.CountA(Sheets("BakeDataTable").Range("E:E")) + 6

Using the following, it always started 1 row below the table.
nextrow = Sheets("BakeDataTable").Cells(Rows.Count, "E").End(xlUp).Row + 1

It works so I'm fine with it. If there is a more efficient or proper way, please let me know...

I dont think one is better or anything, but the one i gave you is more of a coding vba way while the other is subject to worksheet contents, blanks, zeros...etc. Its the way you will find most of the times in forms like this it might not work 100% with you because i am guessing you have 5 rows before your actual data. With the form i gave you always goes from the buttom up and not downwards.