VBA userform to worksheet - 2 decimal places only

[chuf]

I've been reading around the board a lot on this.

I'm trying to make sure than the financial value entered into a textbox is limited to 2 decimal places .. I'm not sure whether this is best done at entry time or whether it can be rounded, or set to a format.

I've got so far ( read: not very )

Me.txtFin = Format(.Cells(varMatch, "G").Value, "###0.00")

it doesn't do what i need... i'd appreciate any advice on what event should kick this off.

I can't wait to finish this userform - i want it nailed by Christmas .. Christmas 2009 !!

Cheers. Chuf

 

[BrianB]

Here is a way of checking a textbox as data is typed.
Bear in mind that all texbox data is text until we use it for something else. This macro runs when each single character entered.

'- LIMIT TEXBOX ENTRY TO 2 DECIMALS
Private Sub TextBox1_Change()
    Dim v As String, d As Integer
    '-------------------------------------
    v = TextBox1.Value
    d = InStr(1, v, ".", vbTextCompare) ' FIND DECIMAL POINT
    If d > 0 And Len(v) > (d + 2) Then ' MORE THAN 2 DECIMAL NUMBERS
        MsgBox ("2 decimals only")
        TextBox1.Value = Left(v, d + 2)
    End If
End Sub

 

[mikerickson]

Put this in the userform's code module. txtFin will only accept numerals to two decimal points.

Private Sub txtFin_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
    Dim newText As String
    
    With txtFin
        newText = Left(.Text, .SelStart) & Chr(KeyAscii) & Mid(.Text, .SelStart + 1)
        If Not (IsNumeric(newText & "0") And Len(newText) <= InStr(newText & ".", ".") + 2) Then
           KeyAscii = 0
        End If
    End With
End Sub

Private Sub txtFin_AfterUpdate()
    txtFin.Text = Format(Val(txtFin), "#.00")
End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    txtFin.Text = Format(Val(txtFin), "#.00")
End Sub

The QueryClose code is to handle the case when the corner X is pressed while the focus is still in txtFin.

 

[Norie]

Why not deal with this when you are transferring the data to the worksheet?

 

[chuf]

Thanks very much Mike, works brilliantly.

Norie - i think i'm a control freak After a meeting on Friday i realise the team want/need a lot of these userforms - just when i thought i'd finished ... so i'd like them to get used to the discipline of there being protocols for completion. I may be wrong in this.. i may live to regret it.