Userform - Not calcuating

[KiwiMattNZ]

Hi

Am hoping this is just a simple fix.

I have a user form with 3 textboxes linked to cell which need add up to 100. When a user has entered all 3 numbers there is a button which does a check.

All works fine if the user pushes enter or moves to a different text box.

If however after getting the 3 boxes to balance to 100, they change one of the numbers and do not push enter or select another box, the check takes the old result and flows through, then updates the three linked cells in the spreadsheet.

Any idea arround forcing a calculation would be great, I have tried doing a spreadsheet calculate at different points but to no avail.

Cheers

Matt

Private Sub Image5_Click()

' Turns off screenupdating
With Application
.ScreenUpdating = False
End With

' Selects sheet where check is

Sheets("dvCheckSheet").Select
Range("D21").Select
' Runs check
If ActiveCell = True Then

' If balances
Unload frmTrackedSurcharge

Sheets("mnMain").Select

With Application
.ScreenUpdating = True
.StatusBar = False
End With
Exit Sub

Else

' If not in balance. advices user
MsgBox "Your percentages do not add up to 100%. Please check"
Sheets("mnMain").Select
With Application
.ScreenUpdating = True
End With
End If
End Sub

 

[JamesW]

Hi,

You could maybe add your calculations into Private Sub TextBox1_AfterUpdate()? (You would need to add a check to see if the other textboxes are empty or not so that it doesn't update with only 1 box filled).

Using AfterUpdate it will add the result no matter what the user does (changes textbox, closes form etc).

 

[KiwiMattNZ]

Hi James

Thank you for the reply.

I have tried this but if the user does not push enter or move to another textbox after inputting a number the afterupdate seems to work based on the initial number and continues on past the error.

The problem is only occuring in one specific set of actions, the user already had the form adding to 100 and then changes one option only and does not do anything else before pushing update button.

Or have i completely missed what you were getting at.

Matt

 

[JamesW]

So they add 3 numbers, push the Update button and it updates the cells, then they go back in and change 1 number?

 

[KiwiMattNZ]

Hi James

They have open the form previously and got it to add up to 100, closed the form and re opened it and changed only one number and done nothing else (if they do something else the numbers update correctly)

The spreadsheet is still sitting at 100 until after the spreadsheet has closed, then the number updates and the problem can be seen.

Matt

 

[JamesW]

Having a hard time picturing this. Want me to message you with my email address and I will take a look?

 

[RoryA]

If your textboxes are linked directly to the cells, remove that link and use code to update the cell. Your validation code should check the texboxes and not the worksheet, and only update the sheet if the values are valid.

 

[KiwiMattNZ]

Hi Rorya

Thank you for that, will give that go!

matt

 

[KiwiMattNZ]

Hi rorya and JamesW

I have managed to get this working, thank you for both of your help

Matt