Workbook_SheetCalculate

Joe4

MrExcel MVP, Junior Admin

Joined

Aug 1, 2002

Messages

72,361

Office Version

1. 365

Platform

1. Windows

• Dec 15, 2005

• #2

Is your code changing any values? If so, then it is probably calling itself. To fix that, you need to disable the event caller within the macro until it changes the value.

So, at the beginning of your code, you would have the line:
Application.EnableEvents = False
and at the end you would turn it back on again with:
Application.EnableEvents = True

 

Upvote 0

S

stevebutler

Board Regular

Joined

Jul 5, 2005

Messages

65

• Dec 15, 2005

• #3

Thank you for the quick reply, but that is not the case.

simple example:

when the user enters data in cell A1, it calculates something for cell B1 (then my code runs), then it uses B1 to calculate something for C1 (then my code runs again)

as you can see, the code ran twice because there were two calculations.

but i only want the user to be prompt once.

i hope i made it a little clearer

thank you
steve

 

Upvote 0

S

stevebutler

Board Regular

Joined

Jul 5, 2005

Messages

65

• Dec 15, 2005

• #4

ideally, this what i would want, but i have no idea how to do it:

this code would run each time the value in cell "K26" changes:

1) check if the value of cell "k26" is greater then 24
2) if it is, prompt [warn] the user
3) if it is not, then do nothing until the value of cell K26 changes again, then check again to see if it is greater than 24

 

Upvote 0

Joe4

MrExcel MVP, Junior Admin

Joined

Aug 1, 2002

Messages

72,361

Office Version

1. 365

Platform

1. Windows

• Dec 15, 2005

• #5

In your first post, you mention that you have code. Can you post it?

 

Upvote 0

Joe4

MrExcel MVP, Junior Admin

Joined

Aug 1, 2002

Messages

72,361

Office Version

1. 365

Platform

1. Windows

• Dec 15, 2005

• #6

The Worksheet_Calculate event runs anytime any cell on the sheet changes, not just a specific cell. So it would run anytime any cell is changed. That shouldn't be a problem, except that if you allow the value to be over 24, the warning will pop-up every time any other cell is changed if the value in K26 stays above 24.

That code would look like:

Code:

Private Sub Worksheet_Calculate()

    If Range("K26") > 24 Then MsgBox "Warning!!!"
    
End Sub

Another way to go about it is to determine which values K26 is dependent upon, and how those values are entered. We may be able to use the Worksheet_Change event instead, in which we can limit it to run only when specific cell values are changed.

 

Upvote 0

S

stevebutler

Board Regular

Joined

Jul 5, 2005

Messages

65

• Dec 15, 2005

• #7

That's what i am asking (i think)
how can i limit it to take action when a specific cell changes?
here is my code:


Dim x As Integer
Dim NoWarnings As Boolean

Private Sub Workbook_Open()
NoWarnings = False

End Sub

Private Sub Workbook_SheetCalculate(ByVal Sh As Object)


Dim WarningResponse
x = 24 ' tons

If NoWarnings = False Then


If Sheets("Summary").Range("K26") > x Then

MsgBox "YOU HAVE EXCEEDED " & x & " TON LIMIT"

WarningResponse = MsgBox("Do you want to receive further warnings?", vbYesNo)

If WarningResponse = vbNo Then
NoWarnings = True
End If
End If

 

Upvote 0

Joe4

MrExcel MVP, Junior Admin

Joined

Aug 1, 2002

Messages

72,361

Office Version

1. 365

Platform

1. Windows

• Dec 15, 2005

• #8

Did you see the end of my last post?

Another way to go about it is to determine which values K26 is dependent upon, and how those values are entered. We may be able to use the Worksheet_Change event instead, in which we can limit it to run only when specific cell values are changed.

Click to expand...

In order to do this, we need more detail from you. Specifically,

1. What is the value in cell K26? I assume it is a formula. If so, please list the formula.

2. If K26 is a formula, how are the cells that it is dependent upon changed? For example, let's say your formula in K26 is =K24+K25. How are the values in K24 and K25 entered? Manually typed in? Copied in? Linked in? It makes a BIG difference when determining which Worksheet events you can use to track this.

 

Upvote 0

S

stevebutler

Board Regular

Joined

Jul 5, 2005

Messages

65

• Dec 15, 2005

• #9

cell formula

k23 ='Straight Bars'!H62
K24 ='Bent Bars'!H69
K25 =K23+K24
K26 =K25/2000

cells H62 & H69 (from their respected sheets) are formatted as follows:

=sum(range)

they are summing up cells with the following formula:

=IF(K4=0,"",K4*N4*O4)

and it all starts with a user entering data into various cells

very confusing, a lot going on at once.

i understand if there isn't a solution

thanks,
steve

 

Upvote 0

Joe4

MrExcel MVP, Junior Admin

Joined

Aug 1, 2002

Messages

72,361

Office Version

1. 365

Platform

1. Windows

• Dec 15, 2005

• #10

Hmmm....

I am beginning to see the problem. In order to use the Worksheet_Change event, you want to check the "root" cell changes that filter up. However, since your are dealing with larger ranges on other worksheets, this would probably get quite complicated.

I think I may have seen a trick before to handle something like this. I will try to find it.

 

Upvote 0