Userform does not parse data as numbers but rather as text.

tjcleere

New Member
Joined
Jan 10, 2017
Messages
4
I am an Excel newbie and I am having a difficult time; hoping someone will be kind enough to give me some pointers. I have a userform that posts several different things to a sheet. One of the things it posts is length of time the user types into a textbox. I then created another sheet to setup the statistics for the first sheet. The stats sheet uses a sumifs variable to count total time based on the user's name. The sumifs does not sum the cells because the cells are text. If I go into each cell where the data is and I change the data to numbers then the sumifs functions works fine. It would be a pain to do this all the time. I am looking for a way that the useform posts the results to the sheet as numbers rather than text so that my sumifs functions work automatically.
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
Hi,
What you would do is to coerce the Text to the correct data type using a conversion function.

For example, using the Val function will return the numbers contained in a string as a numeric value of appropriate type

Code:
Sheets("Sheet1").Range("A1").Value = Val(Me.TextBox1.Text)

Dave
 
Upvote 0
Thank you for the quick response Dave. Thank you for the information. I had seen this listed as a way to address the problem somewhere else as well. I am learning here so I hope you don't fin the following questions too stupid to respond to! The code that you provided. Is this something I would include in the textbox sub area or does it go into a different area of the code? Does sheet1 get renamed to the sheet it is and I am assuming textbox1 would be renamed to the name of the textbox I am working with?
 
Upvote 0
Hi,

This would normally be done withing your forms code page where code you send the textbox data to your worksheet resides.
As you posted no code, what I gave was just an example of using the Val function - you would adapt to your project as required.

Dave
 
Upvote 0
Dave,

Here is the code I am using. The numbers on the userform are typed into textbox4. The following code provides a confirmation popup window before applying the text to the cells in the worksheet. I just want the numbers typed into textbox4 to be entered into the sheet as integers and not text for summing ability.

Private Sub CommandButton1_Click()
If MsgBox("You are about to submit the following session." & vbCr & vbCr & _
"Youth Number - " & TextBox3.Value & vbCr & _
"Session Date - " & TextBox1.Value & vbCr & _
"Is this correct?", vbYesNo, "Confirm Entry") = vbNo Then Exit Sub
ActiveSheet.Protect UserInterfaceOnly:=True
eRow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
Cells(eRow, 1).Value = ComboBox
Cells(eRow, 2).Value = TextBox3
Cells(eRow, 3).Value = TextBox1
Cells(eRow, 4).Value = ComboBox2
Cells(eRow, 5).Value = ComboBox3
Cells(eRow, 6).Value = TextBox4
End Sub
 
Upvote 0
The following appears to be working:

Private Sub CommandButton1_Click()
If MsgBox("You are about to submit the following session." & vbCr & vbCr & _
"Youth Number - " & TextBox3.Value & vbCr & _
"Session Date - " & TextBox1.Value & vbCr & _
"Is this correct?", vbYesNo, "Confirm Entry") = vbNo Then Exit Sub
ActiveSheet.Protect UserInterfaceOnly:=True
eRow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
Cells(eRow, 1).Value = ComboBox
Cells(eRow, 2).Value = TextBox3
Cells(eRow, 3).Value = TextBox1
Cells(eRow, 4).Value = ComboBox2
Cells(eRow, 5).Value = ComboBox3
Cells(eRow, 6).Value = Val(TextBox4.Text)
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top