How to sheet index in calculate event

[DeepButi]

Hi,
I have a common Sub called for several sheets when calculate event fires, so the code inserted is:

Private Sub Worksheet_Calculate()                   ' On Sheet number 2
    Call Drawing(2)
End Sub
Private Sub Worksheet_Calculate()                   ' On Sheet number 5
    Call Drawing(5)
End Sub
Private Sub Worksheet_Calculate()                   ' On Sheet number 6
    Call Drawing(6)
End Sub

Sub Drawing(Sh As integer)                              ' On a Module
.... mycode here
....  Worksheets(sh).              used as Sheet to get info and perform operations
End Sub

I cannot use Call Drawing(Activesheet) because Activesheet is the sheet firing all 3 events at once, not the sheet actually performing mycode.

Any idea how to get the adequate index?

Thks

 

[dmt32]

Try passing the worksheet object to your common code & see if that helps you

sheet code

Private Sub Worksheet_Calculate()                   ' Event On Sheet code page
    Call Drawing(Me)
End Sub

Module Common code

Sub Drawing(ByVal sh As Object)                              ' On a Module

     MsgBox sh.Name

'.... mycode here

End Sub

Dave

 

[DeepButi]

Thanks, it works. Sometimes you don't see the most obvious method!

 

[dmt32]

Thanks, it works. Sometimes you don't see the most obvious method!

no worry we are all probably guilty doing the same - glad resolves your issue & appreciate your feedback

Dave