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.
 

Some videos you may like

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,374
Office Version
  1. 2019
Platform
  1. Windows
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
 

tjcleere

New Member
Joined
Jan 10, 2017
Messages
4
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?
 

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,374
Office Version
  1. 2019
Platform
  1. Windows
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
 

tjcleere

New Member
Joined
Jan 10, 2017
Messages
4
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
 

tjcleere

New Member
Joined
Jan 10, 2017
Messages
4
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,514
Messages
5,596,611
Members
414,080
Latest member
penguin23

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
Top