How to enter inputs from VBA a interface to excel spreadsheet?

[Adin]

I created a user interface to enter data into a backend excel spreadsheet. One important feature is that this inputs must be entered a numbers and not as texts for I can use pivot tables. While I have been able to have this happened I ran into another problem. I want blank inputs to be entered as a blank, whereas my code currently enters them as "0". Here is a sample of my code below:



Private Sub btnSave_Click()
txtUser = txtUser.Text
txtDateEntered = txtDateEntered.Text
txtProjectedCapex = Val(txtProjectedCapex.Text)
txtCompanyPV = Val(txtCompanyPV.Text)
txtActualCapex = Val(txtActualCapex.Text)
For i = 1 To 5000
If Sheet1.Cells(i, 1).Text = "" Then
opencell = i
Sheet1.Cells(opencell, 1).Value = txtUser.Value
Sheet1.Cells(opencell, 2).Value = txtDateEntered
Sheet1.Cells(opencell, 24).Value = txtProjectedCapex.Value
Sheet1.Cells(opencell, 25).Value = txtCompanyPV.Value
Sheet1.Cells(opencell, 26).Value = txtActualCapex.Value
txtUser.Text = ""
txtDateEntered = ""
txtProjectedCapex.Text = ""
txtCompanyPV.Text = ""
txtActualCapex.Text = "
Exit For
End If
Next i
End Sub

[CatDaddy]

use vba tags please!


Private Sub btnSave_Click()
Dim txtArr() As Variant, colArr() As Variant
Dim i As Integer, j As Integer
txtArr = Array(txtUser.Text, txtDateEntered.Text, txtProjectedCapex.Text, txtCompanyPV.Text, txtActualCapex.Text)
colArr = Array(1, 2, 24, 25, 26)
For i = 1 To 5000
If Sheet1.Cells(i, 1).Text = "" Then
For j = LBound(txtArr) To UBound(txtArr)
If j < 2 Then
Sheet1.Cells(i, colArr(j)).Value = txtArr(j)
Else
If txtArr(j) = "" Then
Sheet1.Cells(i, colArr(j)).Value = ""
Else
Sheet1.Cells(i, colArr(j)).Value = Val(txtArr(j))
End If
End If
Next j
Exit For
End If
Next i
End Sub

[Aussiebear]

Welcome to the forum Adin. What CatDaddy meant to say was.....
"When posting code the forum, please wrap the code with the code tags by either highlighting the code then clicking on the green & white VBA button, or click on the green & white VBA button and type your code inside of these."