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

 

[mahmed1]

i have this same form on 3 sheets (weekly, fortnightly and monthly) and when i pass the data, i want to pass it to the relevant sheet.

so i guess another way possible is see which sheet has call the form and set the ws to be the sheet that has called the macro abut i dont know how to do this

I have tried this with no luck to get the sheet name of the macro being called

Set ws = Application.Parent.Caller

 

[RoryA]

Add a public variable to the userform:

Public ws as Worksheet

then when loading the form use:

Load userform1
set userform1.ws = activesheet ' (or whichever sheet)
userform1.show

Note: if you want to do this 'properly' you would create a property procedure rather than using a public member variable, and you'd use a variable for the form, since it's a class.

 

[mahmed1]

Thank yoU

That works fab

you are the best

it works but i have no idea what you mean by this :

"Note: if you want to do this 'properly' you would create a property procedure rather than using a public member variable, and you'd use a variable for the form, since it's a class."

P.s could i have just used dim ws as activesheet in the Ok command button?

so paste the values to the activesheet.range ???

and what does LOAD FORM DO? never used that method other than Show Form

 

[RoryA]

Load does exactly that - loads the form into memory but doesn't show it.

If you only ever want to use the active sheet, then you can simply put that in the OK code and skip all this.

My note meant that to do this following best practices, you would have a Property procedure in the userform rather than a Public variable and you would use something like:

Dim frm as Userform1
' create new instance of form
Set frm = New Userform1
set frm.ws = activesheet
frm.show
set frm = Nothing

 

[mahmed1]

HAVE A BIG FRIENDLY KISSSS OF ME MWAH

THANK YOU

P.s going back to the problem i had where it slowed it down, even though i pressed delete it still was slow unless i deleted the Columns that i input the formulas in.

Why is that delete wont clear it and i physically have delete the columns/rows?

 

[mahmed1]

Can i ask a few q's (it may be something very simple but will help me understand)

1) What is the difference with using the cint or cdbl etc compared with using the format method. The reason i say this is because im curious to why the formula element worked with cdbl and not cint.

does this convert a variable type whereas format just formats it but does not convert data type?

2) I saw that you simply put ws.range("a1") = cdbl(txtbal)

now i would have added the .value at the end so my question is when can i ommit this .value and when is it required. This will help me understand and reduce code and understand when and when not to use it

thank you

 

[RoryA]

P.s going back to the problem i had where it slowed it down, even though i pressed delete it still was slow unless i deleted the Columns that i input the formulas in.

Why is that delete wont clear it and i physically have delete the columns/rows?

Hard to say. Did you have conditional formatting applied to the column?

 

[shg]

2) I saw that you simply put ws.range("a1") = cdbl(txtbal)

now i would have added the .value at the end so my question is when can i ommit this .value and when is it required. This will help me understand and reduce code and understand when and when not to use it

That works because Value is the default property of a range. IMO, it's better in all circumstances to be explicit, unless your disk drive is down to its last few kilobytes.

 

[RoryA]

1) What is the difference with using the cint or cdbl etc compared with using the format method. The reason i say this is because im curious to why the formula element worked with cdbl and not cint.

Format returns a String rather than an actual number data type.

2) I saw that you simply put ws.range("a1") = cdbl(txtbal)

No, I didn't. I wrote

activecell.value

now i would have added the .value at the end so my question is when can i ommit this .value and when is it required. This will help me understand and reduce code and understand when and when not to use it

thank you

ALWAYS use .Value if you are referring to the value.

 

[mahmed1]

Lol thank you

When adding a cell/range,Would Cint(txtbal) work when returning value to a cell or would i need the cdbl for it to work?