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]

Instead of using SetFocus, simply change the Backcolor to say vbRed to indicate that it needs reviewing.

ahhhh thank you

You have been a great help.

I have 1 other performance problem and i was hoping that if i send you this spreadsheet, you will be able to look at this for me. Everything works fine but the performance side and the clicking on the buttons take for ages. The spreadsheet also shows the calculate constantly in the task bar.

I do have a few formulas that uses the rows function but too many.

I am also going to create this same sheet another 2 more times and change the sheet names slightly and the formula slightly so i have a weekly, fort and monthly sheet but at the minute

the navigating and moving around seems slow and was hoping that you could have a look at this for me. The formulas and code work fine.

Can i send you this and when you are free, if you could see where things can be improved that would really be awesome

Thank You once again x

 

[RoryA]

That's really not how the board works.

If you want to put the file on a hosting site and post a link back here, then anyone can have a look when they have time.

 

[mahmed1]

Thank you rory

I think my probs was i copied down this volotile function and then pressed delete but in the background it did not delete the functions and showed as CALCULATE

I have 1 problem with this code

Private Sub txtBal_AfterUpdate()
txtBal = Format(txtBal, "£#,##0.00")
End Sub

when i read that value back to a cell

sheets("Sheet1").range("A1").VALUE = txtBal the txtBal shows as Text even though i have formatted via the cell and code to it to be a currency/number) and now when i try to sum that range i get a 0 as this is showing as text

How can i actually have those number/values as currency/numbers so that my sum functions pick that up?

 

[RoryA]

When you assign the value to the cell, wrap in in CDbl():

activecell.value = cdbl(txtBal)

for example.

 

[mahmed1]

When you assign the value to the cell, wrap in in CDbl():

activecell.value = cdbl(txtBal)

for example.

That worked - Curious to why that worked and not the cint or format?

mwah thank you

 

[mahmed1]

I am almost there Rory THANK YOU

Now i have this userform set up. I need to recreate this userform (Duplicate it) a couple of times. Can i do this easily like copy and paste userform?

 

[RoryA]

Why would you want to duplicate a userform?

 

[mahmed1]

Why would you want to duplicate a userform?

Hi

I need a weekly, fortnightly and monthly form

 

[RoryA]

But if they're the same, just re-use the same form in code.

 

[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