PDA

View Full Version : saving userform textbox as a number in worksheet



buckchow
01-18-2007, 08:21 PM
When a user enters a quantity in a userform textbox it is saved on the worksheet as text instead of a number. How do I get the data to save as a number? As an example if a user enters the quantity of 40 into the textbox it shows as 40 on the worksheet but has a flag next to the cell indicating it is text. Also, when I try to total the worksheet column it doesn't count this cell.

lucas
01-18-2007, 08:38 PM
Try something along these lines buckchow.....

[A1].Value = TextBox1.Value
[A1].NumberFormat = "0.00"

buckchow
01-18-2007, 09:34 PM
Can I enter this code anywhere in my code? What does the [A1] signify?

lucas
01-18-2007, 09:37 PM
Propably you would use it in your command button code....
[A1] is Cell A1

buckchow
01-18-2007, 09:41 PM
The cells are located in column F and if I understand correctly your code is looking at cell A1. When I added your code nothing changed.

lucas
01-18-2007, 09:45 PM
cells..plural?
can you post your workbook?

lucas
01-18-2007, 09:48 PM
here's a quick example using the code from before.

right click on the cell and check it's format against the others around it.

lucas
01-18-2007, 09:59 PM
The [A1] can also be written as:
Range("A1")
Some folks don't like the former which is shorcut notation.

buckchow
01-20-2007, 07:01 PM
I tried substituting a column range for Range ("A1") and instead of populating just the cell in the current row it populated the whole column.

Bob Phillips
01-21-2007, 03:20 AM
If you change Range("A1") to say Range("A:A") it will fill the whole column.

If you want column A of the active row, try



With Range("A" & Activecell.Row)
.Value = TextBox1.Value
.NumberFormat = "0"
End With

buckchow
01-22-2007, 10:18 PM
I can't seem to get it to work. My end goal is to be able to do calculation on the cells in the worksheet but presently this can't be done because the data is saved as text instead of a quantity.

lucas
01-22-2007, 10:28 PM
Hey Buckchow......don't post a workbook because then we might be able to see what your trying to do and be able to help you with it.......:jawdown:

Seriously how hard can that be. You keep posting back with problems that we can't relate to. Just clean it up to the problem and post it.:whip I'm sure this is not a big problem and you can get the help you need.

Course I was kidding in the first sentence..:)

buckchow
01-27-2007, 08:09 PM
You're absolutely right. I just didn't want to complicate what I thought was a simple question but, after searching multiple sites, this doesn't seem to be the case. All I'm trying to do is to have the cell quantities on the workbook that were populated from a userform show as actual numbers and not text. Listed below is the code I'm using. I just can't figure out why the data is being saved as text instead of numerical on the worksheet. As an example, if 50 is entered in the txtQty textbox then 50 is saved to the workbook but it is being saved as text and I can't do any calculations with it.

Private Sub cmdAdd_Click()
Dim strRange As String
If txtBatch = vbNullString Then
MsgBox "No batch number", vbCritical
txtBatch.SetFocus
Exit Sub

End If
strRange = txtBatch.RowSource
If txtBatch.ListIndex > -1 Then
With Range(strRange).Cells(txtBatch.ListIndex + 1, 1)
.Offset(0, 6) = IIf(txtQty <> vbNullString, txtQty, .Offset(0, 6))
.Offset(0, 7) = IIf(txtAcc <> vbNullString, txtAcc, .Offset(0, 7))
.Offset(0, 8) = IIf(txtRej <> vbNullString, txtRej, .Offset(0, 8))
End With
Else
With Range(strRange).Cells(Range(strRange).Rows.Count + 1, 1)
.Value = txtBatch
End With
End If
txtBatch = vbNullString
'clear the data
Me.txtBatch.Value = ""
Me.txtQty.Value = ""
Me.txtAcc.Value = ""
Me.txtRej.Value = ""
Me.txtBatch.SetFocus

End Sub