Copy code of Sheet module into the rest of sheet's modules

[cgcamal]

Hi everybody,

Please some help over here.

I have 50 sheets, I have an event code in Sheet2 module, and I want to execute the same code event in all 49 sheets (excep sheet1).

I need to copy in sheet3 to sheet50 the same code I have in Sheet2 module?
If the answer is yes, how can I copy Sheet2 module code into the Sheet3 to Sheet50 without doing one by one?

Or is there other way to execute the same code of Sheet2 module in Sheet3 to Sheet50 without copy it into every sheet?

Many thanks in advance for any help.

Regards

 

[Norie]

You don't say what event the code is for, but you might be able to add a workbook level worksheet event.

eg SheetChange, SheetCalculate

Most of these are passed two arguments - the sheet (Sh) that the event occured on and the range (Target) it occurred in.

 

[cgcamal]

Hi Norie,

Thanks for your reply.

Is a "Sub Worksheet_Change()" event. In all sheet's modules from Sheet3 to Sheet50 I only have this code :

Private Sub CommandButton1_Click()
Sheets("Main").Select
End Sub

In Sheet2 I have the code above plus the public variables declarations and Worksheet_Change() event like below:

[COLOR=Navy]Dim [/COLOR]Var1 As String, wbk As Workbook, Addr As String
[COLOR=Navy]Dim [/COLOR]LastR As Long, ShName As String
[B]---------------------------------------------------------------------[/B]
[COLOR=Navy]Private Sub[/COLOR] CommandButton1_Click()
Sheets("Main").Select
End Sub
[B]---------------------------------------------------------------------[/B]
Private Sub Worksheet_Change(ByVal Target As Range)
[COLOR=Green]'Line code 1
.
.
.
'Code line 137[/COLOR]
End Sub

I want to have the same code in all sheets 2 to 50.
How can I implement what you suggest me?

Many thanks.

Regards

 

[AlphaFrog]

If you put the code below in the ThisWorkbook module, it will run for all sheets. The variable Sh is the worksheet that the change event occured on and Target is the range of cell(s) that was changed.

Dim Var1 As String, wbk As Workbook, Addr As String
Dim LastR As Long, ShName As String

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

    If Sh.Name <> "Main" Then
        MsgBox "You made a change on sheet: " & Sh.Name & vbLf & _
               "in cell(s): " & Target.Address
    

    'Line code 1
    '
    '
    '
    'Code line 137
    
    End If

End Sub

I think what Nori was suggesting is if you put your code in the ThisWorkbook module, you can configure it to work for all your sheets except the one.

 

[cgcamal]

Hi AlphaFrog,

Thanks for your reply. I've tried inserting the code in "ThisWorkbook" module
but is not working.

The "Worksheet_Change()" event doesn't run. It only runs when
I insert the code within a sheet module.

I'm not sure if I'm doing something wrong or something else is needed.

Please advice.

Thanks in advance.

Regards

 

[xenou]

I've tried inserting the code in "ThisWorkbook" module

Did you try Alphafrog's code or your own code? What is the code you put in the ThisWorkbook module?

ξ

 

[cgcamal]

Hi xenou,

I put my own code within ThisWorkbook module including the IF statement suggested by AlphaFrog.

The problem is that the Worksheet_Change event doesn't become active when I put it
in ThisWorkbook module.

I was thinking if EnableEvents option was in false state, but Is not that either, I sent
Application.EnableEvents = True before try to change a value in any sheet, but
doesn't work either.

Thanks for any help.

Regards

 

[AlphaFrog]

Can you post your code?
'Line code 1
'
'
'
'Code line 137

Also, in the ThisWorkbook module, the equivalent to the Worksheet_Change event is called...

Private Sub [COLOR="Red"]Workbook_SheetChange[/COLOR](ByVal Sh As Object, ByVal Target As Range)

 

[xenou]

Hi,
You need to use the workbook level worksheet change event, which is not the same as the sheet level change event. Post your code (simplest) and someone will easily spot if this is the problem. Otherwise, first, go into the thisworkbook and start a brand new sheet change event by choosing it from the dropdown for workbook events ... you'll get the "frame" of the sub that way. Then put your code in the middle, with appropriate modifications. You can't just copy the sheet code as is into the workbook module.

ξ

 

[cgcamal]

Also, in the ThisWorkbook module, the equivalent to the Worksheet_Change event is called...

Private Sub [COLOR=Red]Workbook_SheetChange[/COLOR](ByVal Sh As Object, ByVal Target As Range)

Great, this is what was missing. Now works perfect.

Many thanks AlphaFrog and xenou for your kindly and appreciated help.

Something new learned today.

Best regards