Putting Private Sub in created sheet

[Mhd_Telecom]

In PERSONAL.XLSB I am trying to put VBA code in a created sheet as private sub but until now I am unable to do so! the part of code that creates the sheet is:


Set SynthEse_Global = ActiveWorkbook.Sheets.Add(Before:=ActiveWorkbook.Worksheets(ActiveWorkbook. Worksheets.count))
SynthEse_Global.Name = "Synthèse_Globale"


after which I create a validation list in cell B1 of "Synthèse_Globale" based on column A1 of the same sheet:


Synthese_Global.Activate
Synthese_Global.Range("B1").Validation.Delete
Synthese_Global.Range("B1") = Empty
Dim i As Integer
For i = 2 To Synthese_Global.Range("A2").End(xlDown).Row
If Synthese_Global.Cells(i, 1) <> "N/A" Then
If Len(str) = 0 Then
str = Synthese_Global.Cells(i, 1)
Else
str = str & "," & Synthese_Global.Cells(i, 1)
End If
End If
Next i
Synthese_Global.Range("B1").Validation.Add xlValidateList, , , str

So cell B1 contains a validation list from which if I choose a value then the cell change event is activated because I am monitoring change in cell B1 after putting the code below:


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim Synthese_Global As Worksheet
Dim ws As Worksheet
Set Synthese_Global = ActiveWorkbook.Worksheets("Synthèse_Globale")
If Application.Intersect(Target, Range("B1:B1")) Is Nothing Then Exit Sub
For Each ws In Worksheets
If ws.Name = "Dropped_Calls_MME_Graphs" Then
Application.DisplayAlerts = False
Sheets("Dropped_Calls_MME_Graphs").Delete
Application.DisplayAlerts = True
End If
Next
If Not Intersect(Target, Range("B1:B1")) Is Nothing And Target.Value <> "" Then
Range("M2:AY2").EntireColumn.Delete
Call Chosen_Cell
Call Chart_Graphics_DroppedCalls_eNodeB
Call Chart_Graphics_IMEISV
End If
End Sub


This code basically scans if there is a sheet named "Dropped_Calls_MME_Graphs" and deletes it if it finds it then it deletes rows of Range("M2:AY2") in my created sheet Synthese_Global then it takes into account the chosen value in cell B1 (from the validation list) and it calls some procedures that are going to draw some graphs in a new sheet called "Dropped_Calls_MME_Graphs" (I deleted the old one) Now what i really want is to automatically put that private sub in my created sheet Synthese_Global after creating it, which i don't know how to do it! Any help is much appreciated, thanks in advance!

[June7]

Why is your code posting as all on one line with no returns and indentations?

Makes it very hard to follow. Suggest you edit your post.

[Mhd_Telecom]

Yes sorry for that, i updated it !

[Aussiebear]

In the Visual basic Projects window, simply right click on the preferred worksheet then Insert a Module, and copy your code to that module.

[June7]

Maybe these will help:
https://www.exceltip.com/modules-class-modules-in-vba/create-a-new-module-using-vba-in-microsoft-excel.html
https://stackoverflow.com/questions/24807623/inserting-a-module-into-a-new-workbook-using-vba
https://stackoverflow.com/questions/34837006/excel-vba-add-code-to-sheet-module-programmatically

[June7]

And cross-post https://stackoverflow.com/questions/79320259/putting-private-sub-in-created-sheet

[Aussiebear]

Well the OP seems to have asked there about the same time as here, which to me indicates no qualms about cross posting.... without the links. So I guess I have no qualms about closing this thread.