Why is this EVENT not cancelling?

[mahmed1]

Hi All,

I have written this afterUpdate event

For some reason when i set the txtBal.setfocus it starts that event again but i thought the enable event would have cancell the event while it resets the focus

Thank You

Here is the code

Private Sub txtAmt_AfterUpdate()
    txtAmt = Format(txtAmt, "£#,##0.00")
    If txtBal.Value = "" Or txtAmt.Value = "" Then
        MsgBox "Both Balance and Amount fields need to be filled in to proceed further", vbOKOnly, "Update All Fields"
        Exit Sub
    ElseIf txtBal.Value < txtAmt.Value Then
        MsgBox "Balance has to be greater than Amount", vbOKOnly, "INVALID INPUT"
        txtBal.Value = ""
        txtAmt.Value = ""
        Application.EnableEvents = False
        txtBal.SetFocus
        Application.EnableEvents = True
        Exit Sub
    Else
        txtNoPmt = txtBal.Value / txtAmt.Value
    End If
End Sub

 

[shg]

Excel Options > Add-Ins, Manage: select Excel Add-Ins from the drop-down, press Go.

 

[mahmed1]

Hi Rory,

I have a slight formatting problem.

In my form, if i enter a date say 01/10/2014 (Uk date) whem it transfers this to the cell it displays as 10/01/2014. The cells are formatted as dd/mm/yyyy

I have even tried to format in code and this does not work.

ws.Range("wkDate").Value = Format(txtDte.Value, "dd/mm/yy")

1) How can i get around this

2) What validation code can i have to ensure that anything in that date text box has to be a date entered and no text, plain number and date only entered in format dd/mm/yy and a correct date also so not allowing 32/14/14 etc

I tried to start with adding this

If wkFrm.txtDte.Value <> "" AND IsDate(wkFrm.txtDte.Value) AND (THE FORMAT IS LIKE "DD/MM/YY") AND (IT IS A CORRECT VALID DATE) THEN
Run code
End if

 

[RoryA]

You should start a new thread as that's completely unrelated to the rest of this.

Anyway, use CDate:

ws.Range("wkDate").Value = CDate(txtDte.Value)

 

[mahmed1]

Want to say masssive thank you rory x