In Userform, when data entry, the value doesn't show as Value e.g. doolars or dates

[kitsa]

Hi Guys,
I would like to enter figures into Userform, but would like it to show as Dollars "$#,##0.00" or dates "mm/dd/yyyy", how do I do this?

Sub Reset()

    Dim iRow As Long

iRow = [Counta(Database!A:A)] ' idetifying the last row



With frmForm

.TxtCompanyName.Value = ""
.txtProjectName.Value = ""
.TxtDate.Value = ""
.TxtNo.Value = ""


.CmbConVar.Clear

.CmbConVar.AddItem "Subcontract"
.CmbConVar.AddItem "Variation"

.TxtRowNumber.Value = ""


.TxtQuoteNo.Value = ""
.TxtItemNo.Value = ""
.TxtArea.Value = ""
.TxtDes1.Value = ""
.TxtDes2.Value = ""
.TxtHeight.Value = ""
.TxtLength.Value = ""
.TxtLevels.Value = ""
.TxtErect.Value = ""
.TxtHUI1.Value = ""
.TxtHUI2.Value = ""
.TxtHUI3.Value = ""
.TxtSTD.Value = ""
.TxtLCS.Value = ""
.TxtIB.Value = ""
.txtTMNo.Value = ""
.TxtTM.Value = ""
.TxtHUMNo.Value = ""
.TxtHUM.Value = ""
.TxtDismantle.Value = ""
.TxtTransport.Value = ""
.TxtSCS.Value = ""
.TxtSundry.Value = ""
.TxtSupplyBeams.Value = ""
.TxtOthers.Value = ""
.TxtENG.Value = ""
.TxtHire.Value = ""
.TxtHireWeeks.Value = ""
.TxtOH.Value = ""
.TxtWklyHire.Value = ""
.TxtTotal.Value = ""

'.TxtErect = Format(Erect, "$#,##0.00")

.lstdatabase.ColumnCount = 34
.lstdatabase.ColumnHeads = True

.lstdatabase.ColumnWidths = "50,20,60,60,20,100,120,120,20,20,20,80,80,80,80,80,80,80,20,80,20,80,80,80,80,80,80,80,80,80,20,80,80,80"

If iRow > 1 Then

.lstdatabase.RowSource = "Database!A2:AH" & iRow
Else

.lstdatabase.RowSource = "Database!A2:AH2"

End If



    End With



End Sub
Sub Submit()

    Dim sh As Worksheet
Dim iRow As Long


Set sh = ThisWorkbook.Sheets("Database")

If frmForm.TxtRowNumber.Value = "" Then

iRow = [Counta(Database!A:A)] + 1
Else

iRow = frmForm.TxtRowNumber.Value

End If



With sh


.Cells(iRow, 1) = frmForm.TxtDate.Value = Format(frmForm.TxtDate, Date, "mm/dd/yyyy")
.Cells(iRow, 2) = frmForm.TxtNo.Value
.Cells(iRow, 3) = frmForm.CmbConVar.Value
.Cells(iRow, 4) = frmForm.TxtQuoteNo.Value
.Cells(iRow, 5) = frmForm.TxtItemNo.Value
.Cells(iRow, 6) = frmForm.TxtArea.Value
.Cells(iRow, 7) = frmForm.TxtDes1.Value
.Cells(iRow, 8) = frmForm.TxtDes2.Value
.Cells(iRow, 9) = frmForm.TxtHeight.Value
.Cells(iRow, 10) = frmForm.TxtLength.Value
.Cells(iRow, 11) = frmForm.TxtLevels.Value
.Cells(iRow, 12) = frmForm.TxtErect.Value
.Cells(iRow, 13) = frmForm.TxtHUI1.Value
.Cells(iRow, 14) = frmForm.TxtHUI2.Value
.Cells(iRow, 15) = frmForm.TxtHUI3.Value
.Cells(iRow, 16) = frmForm.TxtSTD.Value
.Cells(iRow, 17) = frmForm.TxtLCS.Value
.Cells(iRow, 18) = frmForm.TxtIB.Value
.Cells(iRow, 19) = frmForm.txtTMNo.Value
.Cells(iRow, 20) = frmForm.TxtTM.Value
.Cells(iRow, 21) = frmForm.TxtHUMNo.Value
.Cells(iRow, 22) = frmForm.TxtHUM.Value
.Cells(iRow, 23) = frmForm.TxtDismantle.Value
.Cells(iRow, 24) = frmForm.TxtTransport.Value
.Cells(iRow, 25) = frmForm.TxtSCS.Value
.Cells(iRow, 26) = frmForm.TxtSundry.Value
.Cells(iRow, 27) = frmForm.TxtSupplyBeams.Value
.Cells(iRow, 28) = frmForm.TxtOthers.Value
.Cells(iRow, 29) = frmForm.TxtENG.Value
.Cells(iRow, 30) = frmForm.TxtHire.Value
.Cells(iRow, 31) = frmForm.TxtHireWeeks.Value
.Cells(iRow, 32) = frmForm.TxtOH.Value
.Cells(iRow, 33) = frmForm.TxtWklyHire.Value
.Cells(iRow, 34) = frmForm.TxtTotal.Value
.Cells(iRow, 35) = Application.UserName
.Cells(iRow, 36) = [text(Now(),"DD-MM-YY HH:MM:SS")]


    End With


End Sub

 

[DanteAmor]

Example:

Me.TxtErect.Value = Format(Me.lstdatabase.List(Me.lstdatabase.ListIndex, 11), "$ #,##0.00")

 

[kitsa]

You sent this one to me before, but now I have different codes?

 

[DanteAmor]

Ok, It is for you to observe the example and what you apply to your need.

 

[kitsa]

I don't know, that why I'm asking. I'm very very new and don't really know VBA. Every time I get support I can kinda work it out?

 

[DanteAmor]

so I don't understand what you need.
the code you put is to pass the data from the userform to the sheet.
What do you need?

 

[kitsa]

I need when entering new data, that the display is dollars or dates, every time I enter it's just normal numbers. e.g. pic

The VBA code I have is all I have, not sure how to code it, or do I need to do something else?

 

[DanteAmor]

For the dollars you must put the following code for each textbox

Private Sub TextBox1_AfterUpdate()
  TextBox1 = Format(TextBox1, "$ #,##0.00")
End Sub

For dates I recommend using a date control, it can be DTPicker or MonthView

 

[kitsa]

Thanks, The dollar sign came up, but somehow it doesn't allow me to put in figures e.g. I wanted to put in $5,000, but it's only allowed me to put in $5.00? So there something stopping me from entering larger data?

Private Sub TxtErect_Change()
TxtErect = Format(TxtErect, "$#,##0.00")
End Sub

 

[DanteAmor]

Try this event

Private Sub TextBox1_AfterUpdate()