Update code in 220 objects

[wilg]

I have posted earlier a similar dillema to which a suggestion from p45cal provided a working solution. Thank you.
Now I cannot use that same solution and need some further help.

I have a 230 sheet workbook already build with info keyed. I need to update a worksheet_calculate event on 220 of the 230 sheets.

Please tell me there is a easy was to select all objects (worksheets) in VBA and copy and past this code in at the same time as opposed to paste 220 times?

Please advise if possible.

[Paul_Hossler]

I'd make a single Application level SheetCalculate event and put the logic there.

The worksheet that was calcualted is passed so you can make decisions



In ThisWorkbook

[vba]
Option Explicit
Private XLApp As CExcelEvents
Private Sub Workbook_Open()
Set XLApp = New CExcelEvents
End Sub
[/vba]


In a class module called CExcelEvents

[vba]
Option Explicit
Private WithEvents App As Application

Private Sub App_SheetCalculate(ByVal Sh As Object)
MsgBox "Calculation Happened on " & Sh.Name
End Sub
Private Sub Class_Initialize()
Set App = Application
End Sub
[/vba]

Paul

[wilg]

Is there no way to hold shift, ctrl to select multiple worksheets in VBA at once?

[GTO]

...I have a 230 sheet workbook already build with info keyed. I need to update a worksheet_calculate event on 220 of the 230 sheets.

Please tell me there is a easy was to select all objects (worksheets) in VBA and copy and past[e] this code in at the same time as opposed to paste 220 times?...

Is there no way to hold shift, ctrl to select multiple worksheets in VBA at once?

Hi there,

What do you mean "Paste code"? Are you asking to "select" all the sheet modules at once?

Mark

[wilg]

Yes exactly. Just like if using excel sheets i want to select a sheet modules and paste in my updated code in the specific 220 vba modules (sheets)

[GTO]

This is probably a terrible explanation, but its not really like changing a cell or cells in multiple sheets, because there are places for Excel to 'aim' at (ie the cells), where the modules would be like trying to paste into mutiple Word docs at one time.

I am curious why you would not want to use the workbook level event, or application level event as Paul has shown. You are obviously using identical code in ea sheet, why have it in 220 places instead of one?

[wilg]

Well I'm not sure on how to put this in somewhere else. I tried a module, but it didn't regognize "me" for the tabs when I tried calling it from a worksheet_calculate event.
In only 3 sheets out of the 223 tabs I need a differnt code for worksheet_calculate.
one important thing to note is I have the workbook on manual calculate because one tab compiles a huge amount of info and slows down the workbook. Each tab only calculates if active.
Paul's suggestion I am having a had time manipulating to my specific code.
Any other suggestion is much appreciated.




[VBA]Private Sub Worksheet_Calculate()
If Range("as1") = "n" Then
Me.Tab.ColorIndex = 3 ' RED
Me.Move After:=Sheets(Sheets.Count)
ThisWorkbook.Sheets("2 STATUS").Select
Else
If Range("as5") > 7 Then
Me.Tab.ColorIndex = 5 ' BLUE

Else

If Range("as5") > 1 And Range("AS5") < 7 Then
If Range("as2") <> "Informed" Then
MsgBox Range("AS1").Value & vbNewLine & vbNewLine & " IS PAST THEIR ANNIVERSARY DATE." & vbNewLine & vbNewLine & "PLEASE PRINT OUT ATTENDANCE AND CLEAR CELLS TO START A NEW YEAR.", vbInformation, "AD.A.M. ASSISTANCE."

Me.Tab.ColorIndex = 6 ' yellow
Me.Move before:=Sheets("1 maint")
Me.Range("as2").FormulaR1C1 = "Informed"
Else

If Range("as5") > 1 And Range("AS5") < 7 Then
Me.Tab.ColorIndex = 6
Me.Move before:=Sheets("1 maint")



End If
End If
End If
End If
End If
End Sub[/VBA]

[GTO]

Well I'm not sure on how to put this in somewhere else. I tried a module, but it didn't regognize "me" for the tabs when I tried calling it from a worksheet_calculate event.

If you mean a Standard Module, the reason is that Me is a special keyword used to refer to the object that the class module refers to. Its not too hard to have the basics if you just remember this: Me in a Userform refers to the UserForm, Me in a worksheet refers to the worksheet. Its like ThisWorkbook refers to the workbook that the code is housed in. The difference is that Me can only be used in the class/object module.

In only 3 sheets out of the 223 tabs I need a differnt code for worksheet_calculate.
one important thing to note is I have the workbook on manual calculate because one tab compiles a huge amount of info and slows down the workbook. Each tab only calculates if active.
Paul's suggestion I am having a had time manipulating to my specific code.
Any other suggestion is much appreciated.

Okay, a quick stab, and not tested. In a junk copyof your wb, put this in the ThisWorkbook Module:

Option Explicit
    
Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
    
    Select Case Sh.Name
    Case "Sheet1", "Sheet2", "Sheet3" '<--Change to the sheetnames you do NOT want the code to run against.//
    Case Else
        With Sh
            If .Range("as1") = "n" Then
                .Tab.ColorIndex = 3 ' RED
                .Move After:=Sheets(Sheets.Count)
                ThisWorkbook.Sheets("2 STATUS").Select
            Else
                If .Range("as5") > 7 Then
                    .Tab.ColorIndex = 5 ' BLUE
                Else
                     
                    If .Range("as5") > 1 And .Range("AS5") < 7 Then
                        If .Range("as2") <> "Informed" Then
                            MsgBox .Range("AS1").Value & vbNewLine & vbNewLine & " IS PAST THEIR ANNIVERSARY DATE." & vbNewLine & vbNewLine & "PLEASE PRINT OUT ATTENDANCE AND CLEAR CELLS TO START A NEW YEAR.", vbInformation, "AD.A.M. ASSISTANCE."
                             
                            .Tab.ColorIndex = 6 ' yellow
                            .Move before:=Sheets("1 maint")
                            .Range("as2").FormulaR1C1 = "Informed"
                        Else
                             
                            If .Range("as5") > 1 And .Range("AS5") < 7 Then
                                .Tab.ColorIndex = 6
                                .Move before:=Sheets("1 maint")
                            End If
                        End If
                    End If
                End If
            End If
        End With
    End Select
End Sub

Again, not tested or proofed, I just copied your code.

Hope that helps,

Mark

[wilg]

......thats it. It seems to work.

Now question. If a previous code already resides in the worksheet module which I don't want to go back and delete all 220 this code works, but which one superceeds the other?

[GTO]

......thats it. It seems to work.

Now question. If a previous code already resides in the worksheet module which I don't want to go back and delete all 220 this code works, but which one superceeds the other?

Did you try any testing? All you have to do is put a breakpoint at the worksheet level, workbook level (and application level if present) events, and you can step through the code. If you have the same event at worksheet and workbook levels, ythe worksheet level runs first, then the workbook level.

[wilg]

Yes I tested it. It works. Can't see any duplicate issued. Looks good. But getting a loop on another issue I'm trying to solve now....lol. May need further help..

Thanks very much for the time you've already given me too..