format excel vba texbox for numers instead of text

[wayneseymour]

When sending data to a worksheet the record is transferred successfully but the all cells that contain numbers have the green triangle(formula error I am guessing) and cannot be formatted as well as the cells with dates also cannot be formatted. This in turn prohibits those rows to be included in any filtering that is on the sheet. Is the form causing this behavior? I have removed the 'unnecessary' greet triangle by going to "options"> "formulas" and un checking the box that gives you the warning but still the cells will not allow formatting.

this is my code to add a new record.:




Private Sub cboMaterialType_Change()
Me.TxtMaterial.Text = cboMaterialType.Text & "-" & Me.TxtMaterial.Text
End Sub



Private Sub TxtDateofPurchase_AfterUpdate()
Dim TxtDateOfPurchase As Long
TxtDateOfPurchase.Value = Format(TxtDateOfPurchase.Value, "dd-mm-yyyy")
End Sub
Private Sub TxtID_AfterUpdate()
Dim TxtID As Integer
TxtID.Value = Format(TxtID.Value, "#,##0.00")
End Sub
Private Sub TxtQty_AfterUpdate()
Dim TxtQty As Integer
Me.TxtQty.Value = Format(Me.TxtQty.Value, "###0")
End Sub
Private Sub txtUnitCost_AfterUpdate()
Dim TxtUnitCost As Integer
Me.TxtUnitCost.Value = Format(Me.TxtUnitCost.Value, "#,##0.00")
End Sub
Private Sub TxtTotal_AfterUpdate()
Dim TxtTotal As Integer
Me.TxtTotal.Value = Format(Me.TxtTotal.Value, "#,##0.00")
End Sub
Private Sub TxtRecieptNo_AfterUpdate()
Dim TxtRecieptNo As Integer
Me.TxtRecieptNo.Value = Format(Me.TxtRecieptNo.Value, "###0")
End Sub




Public Sub UserForm_Initialize()

'dim. the variables
Dim ws As Worksheet
'loop through worksheets
For Each ws In Worksheets
'use the code name in case sheet name changes
Select Case ws.CodeName
'exclude these sheets by code name
Case "sheet2", "sheet3"
'Add the rest
Case Else
Me.cboDataSheet.AddItem ws.Name
End Select
Uni_Send.TxtDateOfPurchase.SetFocus
Me.TxtID = Application.WorksheetFunction.Max(Range("B6"), 0) + 1

TxtDateOfPurchase = Format(Me.TxtDateOfPurchase.Value, "dd-mm-yyyy")
Next ws

End Sub
Private Sub btnStDateCal_Click()

Cal.lblctrlName = "TxtDateOfPurchase"
Cal.lblUF = "Uni_Send"
Cal.Show

End Sub
Private Sub fCal_DateClick(ByVal DateClicked As Date)

For Each Uf In VBA.UserForms
If Uf.Name = Cal.lblUF Then
For Each ctl In Uf.Controls
If ctl.Name = Cal.lblctrlName Then
'found the control to give the date to
ctl.Value = DateClicked
Me.Hide
End If
Next ctl
End If
Next Uf

End Sub
Private Sub cmdClose_Click()

'close the userform
Unload Me

End Sub
Private Sub CmdAdd_Click()
'dimention the variable
Dim DataSH As Worksheet
Dim Addme As Range
Dim TxtID As Integer


'set the variable
Set DataSH = Sheet1
'error handler
On Error GoTo errHandler:
'set variable for the destinat
Set Addme = DataSH.Cells(Rows.Count, 2).End(xlUp).Offset(1, 0)
'hold in memory and stop screen flicker
Application.ScreenUpdating = False
If Me.TxtDateOfPurchase = "" Then
MsgBox "There is insufficient data, Please return and add the needed information"
Exit Sub
End If
'send the values to the database
With DataSH
'add the unique reference ID then all other values
Addme.Offset(0, 0) = DataSH.Range("B6").Value + 1
Addme.Value = Me.TxtID
Addme.Offset(0, 1).Value = Me.TxtDateOfPurchase
Addme.Offset(0, 2).Value = Me.cboCategory
Addme.Offset(0, 3).Value = Me.TxtMaterial
Addme.Offset(0, 4).Value = Me.TxtQty
Addme.Offset(0, 5).Value = Me.cboUnit
Addme.Offset(0, 6).Value = Me.TxtUnitCost
Addme.Offset(0, 7).Value = Me.TxtTotal
Addme.Offset(0, 8).Value = Me.cboVendor
Addme.Offset(0, 9).Value = Me.TxtRecieptNo
Addme.Offset(0, 10).Value = Me.TxtRecieptDetails

End With
'sort the data by "Surname"
'DataSH.Select
'With DataSH
'.Range("B9:H10000").Sort Key1:=Range("C9"), Order1:=xlAscending, Header:=xlGuess
'End With
'clear the values after entry
'Clear
'communicate with the user
MsgBox "Your Purchase data Has Been successfully added"
Me.TxtID = Application.WorksheetFunction.Max(Range("B6"), 1) + 1
'return to interface sheet sheet
'Sheet2.Select
'reset the form
On Error GoTo 0
Exit Sub
errHandler:
'if error occurs then show me exactly where the error occurs
MsgBox "Error " & Err.Number & _
" (" & Err.Description & ")in procedure cmdClear_Click of Form FormFilter"
End Sub
Private Sub Txtunitcost_Exit(ByVal Cancel As MSForms.ReturnBoolean)

TxtTotal.Text = Val(TxtQty.Text) * Val(TxtUnitCost.Text) * 1
TxtTotal.Text = Format(TxtTotal.Text, ("#,0.00##"))
TxtUnitCost.Text = Format(TxtUnitCost.Text, ("#,0.00##"))
End Sub

Private Sub cboVendor_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)

If Me.TxtDateOfPurchase = "" Then
MsgBox "Please enter the date Of the Purchases on the receipt then Continue"
Exit Sub
End If

End Sub


Private Sub cmdRefresh_Click()

'Clear all data from form

With Me

'Addme.Value = Me.TxtateOfPurchase
Me.TxtDateOfPurchase = ""
Me.cboCategory = ""
Me.TxtMaterial = ""
Me.TxtQty = ""
Me.cboUnit = ""
Me.TxtUnitCost = ""
Me.TxtTotal = ""
Me.cboVendor = ""
Me.TxtRecieptNo = ""
Me.TxtRecieptDetails = ""
Me.cboDataSheet = ""
Me.cboMaterialType = ""
'Adds a new ID number



End With
End Sub

 

[Yongle]

Try this method

Range("A1") = [B]CDbl([/B]Me.TxtUnitCost[B])[/B]

 

[Yongle]

This is a PUBLIC forum. Please ask questions on your thread

Received by PM....
Hi,Thank you for the advice on getting my textbox formatted. I am very very green to VBA , so I need a bit of help where in my code would I place this:
Range("A1") = CDbl(Me.TxtUnitCost)

Here is an example from your code

me.Offset(0, 6).Value = Me.TxtUnitCost

replace with

me.Offset(0, 6).Value = [COLOR=#ff0000]CDbl([/COLOR]Me.TxtUnitCost[COLOR=#ff0000])[/COLOR]

VBA function CDbl converts values to type "Double" which should resolve the issue you are experiencing