Private Sub Worksheet_Calculate() is not private?

[spacely]

Hi,

I have a workbook/sheet that has a callback on Calculation associated with it:

Private Sub Worksheet_Calculate()

But whenever it is opened in the same workBOOK as anothe excel application, changes in the other excel sheets/book actually trigger this callback. Why is that?

Baffled,
Dave

 

[spacely]

Anyone?

 

[VoG]

Does the sheet contain formulas that refer to other sheets?

 

[spacely]

No. There is only 1 sheet in that book. Is there a way to specify the sheet name that is being recalculated? I have the code in the Sheet code:

Private Sub Worksheet_Calculate()
ScrollBar13.Max = Int(Worksheets("static clear").Cells(2, 23).Value)
End Sub

 

[Andrew Poulsom]

That procedure is in the worksheet's module, so there is no need to specify its name. The procedure will fire only when that particular worksheet is calculated.

By the way, if there is only one worksheet what is Worksheets("static clear")?

 

[spacely]

That's the name of the single worksheet. I am just changing a scrollbar's max value which is in a cell of that sheet.

Is it all coded right? Maybe an Excel bug?

 

[RoryA]

If it's the same sheet, then you can use:

Private Sub Worksheet_Calculate()
ScrollBar13.Max = Int(Me.Cells(2, 23).Value)
End Sub

whether it's a bug or not, I can't say as I don't know what is in the sheet that might cause it to recalculate.<!-- / message -->

 

[spacely]

The weird thing is changes in another book (opened in the same Instance of Excel) trigger the Calculate callback in that sheet belonging to a different book.

 

[RoryA]

That's not necessarily weird - depends what is in your sheet. Probably one or more volatile functions

 

[Boltie]

I'm having the same problem.

I have a workbook with 3 worksheets and have used Private Sub worksheet_calculate. When I open another workbook along side this one, when ever I change something in the unrelated workbook, it produces an error for the worksheet_calculate that is contained within the first workbook. Why is this happening? There is nothing linking them. It happens when any other workbook is open alongside.