1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31
| Private Sub CreerCode(ShName As Variant)
With ThisWorkbook.VBProject.VBComponents(ShName).CodeModule
.InsertLines 1, "Private Sub Worksheet_Change(ByVal Target As Range)"
.InsertLines 2, "Dim ws As Worksheet"
.InsertLines 3, "Dim Col As Integer"
.InsertLines 4, "Dim cel As String"
.InsertLines 5, "Set ws = Worksheets(ActiveSheet.Name)"
.InsertLines 26, "If Cells(1, Target.Column).Value Like ""MINIMUM"" Then"
.InsertLines 27, "If ActiveCell.value=0 Then"
.InsertLines 28, "Application.EnableEvents = False"
.InsertLines 29, "For Col = 1 To Columns(Split(ws.UsedRange.Address, ""$"")(3)).Column"
.InsertLines 30, "cel = ws.Cells(1, Col)"
.InsertLines 31, "If cel Like ""*Optionel ou Requis"" Then"
.InsertLines 32, "ws.Cells(ActiveCell.row, Col).value = ""Optionel"""
.InsertLines 33, "End If"
.InsertLines 34, "Next"
.InsertLines 35, "Application.EnableEvents = True"
.InsertLines 36, "ElseIf ActiveCell.value=1 Then"
.InsertLines 37, "Application.EnableEvents = False"
.InsertLines 38, "For Col = 1 To Columns(Split(ws.UsedRange.Address, ""$"")(3)).Column"
.InsertLines 39, "cel = ws.Cells(1, Col)"
.InsertLines 40, "If cel Like ""*Optionel ou Requis"" Then"
.InsertLines 41, "ws.Cells(ActiveCell.row, Col).value = ""Requis"""
.InsertLines 42, "End If"
.InsertLines 43, "Next"
.InsertLines 44, "Application.EnableEvents = True"
.InsertLines 45, "End If"
.InsertLines 46, "End If"
.InsertLines 47, "End Sub"
End With
End Sub |
Partager