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 32 33 34 35 36 37 38 39 40
| Sub tabx1()
Dim entete, new_nom$, themes, lignes
entete = Array("toto", "titi", "riri", "fifi", "loulou", "truc", "bidule", "machin", "chose")
new_nom = "panel1"
lignes = 6
themes = "truc bidule" & Date & "/" & "machin"
create_tableau entete, new_nom, themes, lignes
End Sub
Sub tabx2()
Dim entete, new_nom$, themes, lignes
entete = Array("vcbgcc", "titi", "riri", "fifi", "loulou", "truc", "bidule", "machin", "chose")
new_nom = "panel1"
lignes = 6
themes = "truc bidule" & Date & "/" & "machin"
create_tableau entete, new_nom, themes, lignes
End Sub
Function create_tableau(entete, new_nom As String, ByVal themes As String, ByVal lignes As Long)
Dim rng As Range, TbS As ListObject
With ActiveSheet
'************************************************************************************
'ici je voudrais le supprimer si il existe ainsi que tout mise en couleur ou format sur la plage occupée par le tableau
For Each TbS In .ListObjects
Debug.Print TbS.Name & " : "
If TbS.Name = new_nom Then Set rng = Union(Range(new_nom & "[#all]"), Range(new_nom & "[#all]").Offset(-2))
Next
If Not rng Is Nothing Then MsgBox "range a supprimer : " & rng.Address
If Not rng Is Nothing Then rng.EntireRow.Delete
'************************************************************************************
.Cells(Rows.Count, 1).End(xlUp).Offset(1) = themes
With .ListObjects.Add(xlSrcRange, .Cells(Rows.Count, 1).End(xlUp).Offset(2).Resize(lignes, UBound(entete) + 1), , xlNo)
.Name = new_nom
.TableStyle = "TableStyleMedium11"
With .HeaderRowRange
.Value = entete
.Interior.Color = RGB(0, 100, 0)
.Font.Color = vbWhite
End With
End With
End With
End Function |
Partager