XL 2016 VBA - OLEObjects.add fait perdre les valeurs des variables globales de tous les Modules

Option Explicit
Public VariableModule1 As Variant
Sub CréationComboBox()
    Dim T
    T = Array("item1", "item2", "item2")
    ufListbox.ShowX T
End Sub

Sub ValoriserVariableModules()
    VariableModule1 = "Valeur de la variable module1"
    VariableModule2 = "Valeur de la variable module2"
    MsgBox "VariableModule1 valorisée à """ & VariableModule1 & """" & vbCrLf & _
           "VariableModule2 valorisée à """ & VariableModule2 & """"
End Sub

Sub AfficherVariableModules()
    MsgBox "VariableModule1 = """ & VariableModule1 & """" & vbCrLf & _
           "VariableModule2 = """ & VariableModule2 & """"
End Sub

'sub pour faire ce que l'on veux de la valeur ou l'index selectionné dans la combo du userform
'c'est un pseudo event dispo dans un module standard (rigolo non???)
Sub MacomboBox_change(value, Optional index As Long = -1)
    MsgBox value
End Sub

Option Explicit
Public tablo

'fonction show perso incluant des parametres
Public Function ShowX(arr, Optional rng As Range = Nothing)
    With ufListbox
        .tablo = arr
        .Show 0
    End With
End Function

Private Sub Combo_Change()
    With Combo
        If .ListIndex > -1 Then MacomboBox_change .value, .ListIndex
    End With
    Unload Me
End Sub



Private Sub UserForm_Activate()
    Dim cel As Range
    Set cel = ActiveCell
    Combo.List = tablo
    Nocaption
    placementRange cel
End Sub

Private Sub Nocaption()
    Dim hwnd&, h&
    h = Me.Combo.Height
    hwnd = ExecuteExcel4Macro("CALL(""user32"",""GetActiveWindow"",""JCC"")")         'api GetActiveWindow
    ExecuteExcel4Macro ("CALL(""user32"",""SetWindowLongA"",""JJJJJ""," & hwnd & ", " & -16 & ", " & &H94080080 & ")")      'api SetWindowLongA
    Me.Height = 0
    Me.Width = Combo.Width - 4
    Combo.Width = Me.InsideWidth
    Combo.Height = Me.InsideHeight

End Sub

Private Function placementRange(Obj As Object)
'function  put Userform  into range périmeter  by patricktoulon (france) exceldownloads forum)
'see my calandar
    If Obj Is Nothing Then Exit Function
    Dim z#, EcX#, L1#, T1#, C#, R#, Vr As Range, HX#, Wx#, Ok As Boolean, Op&, PtoPx#, I&
    With ActiveWindow
        PtoPx = (.ActivePane.PointsToScreenPixelsX(72) - .ActivePane.PointsToScreenPixelsX(0)) / 72    'coeff point to pixel
        Op = Int(Val(Mid(Application.OperatingSystem, InStrRev(Application.OperatingSystem, " ") + 1)))    'number version system
        'exit si la cellule injecté n'est pas vible a l'ecran
        For I = 1 To .Panes.Count: Ok = IIf(Not Intersect(.Panes(I).VisibleRange, Obj) Is Nothing, True, Ok): Next
        If Ok = False Then Beep: MsgBox " cette cellule n'est pas visible a l'ecran": Exit Function
        z = (ActiveWindow.Zoom / 100): Set Vr = .VisibleRange    'Coeff zoom ,  rangevisible partie mobile
        'EcX = 4 And Op = 6 And Int(Val(Application.Version)) < 16  'ecart cadre
        L1 = (.ActivePane.PointsToScreenPixelsX(Int(Obj.Left)) / PtoPx) * z + EcX    'placement partie mobile
        T1 = .ActivePane.PointsToScreenPixelsY(Int(Obj.Top)) / PtoPx * z + EcX
        With .Panes(1).VisibleRange: C = .Cells(.Cells.Count).Column: R = .Cells(.Cells.Count).Row: End With    'limite splitrow et splitcolumn
        If .SplitRow > 0 Then  'placement  dans le splitrow
            If Obj.Row < R + 1 And .ScrollRow > R Then T1 = ((.ActivePane.PointsToScreenPixelsY(Vr.Cells(1).Top) / PtoPx) * z) - (Range(Obj, Cells(R, 1)).Height * z) + EcX
        End If
        If .SplitColumn > 0 Then    'placement  dans le splitcolumn
            If Obj.Column < C + 1 And .ScrollColumn > C Then L1 = ((.ActivePane.PointsToScreenPixelsX(Vr.Cells(1).Left) / PtoPx) * z) - (Range(Obj, Cells(1, C)).Width * z) + EcX
        End If
    End With
    'option de placement :
    Wx = (Obj.Width / 2) * z * 0
    HX = (Obj.Height / 2) * z * 0
    L1 = L1    '+ (Wx)
    T1 = T1    '+ (HX)
    If L1 > Application.Left + Application.Width - Me.Width Then L1 = Application.Left + Application.Width - Me.Width - 15
    If T1 > Application.Top + Application.Height - Me.Height Then T1 = Application.Top + Application.Height - Me.Height - 15
    With Me: .Left = L1: .Top = T1: End With
End Function

Sub CréationComboBox()
    Dim Top As Long, Left As Long, Width As Long, Height As Long
    Dim CbB As OLEObject
    Dim OCB As ComboBox
    Dim old1, old2
    
    On Error Resume Next
    old1 = VariableModule1: old2 = VariableModule2                                 '<========
    ActiveSheet.Shapes(NomComboBox).Delete
    
    'Cadre la ComboBox sur la cellule active
    With ActiveCell
        Top = .Top
        Left = .Left
        Width = .Width + 16
        Height = .Height * 1.1
    End With
    
    'Création de la ComboBox Active X
    Set CbB = ActiveSheet.OLEObjects.Add(ClassType:="Forms.ComboBox.1", Link:=False, _
                                         DisplayAsIcon:=False, Left:=Left, Top:=Top, Width:=Width, Height:=Height)
    CbB.Name = NomComboBox
    CbB.Object.AddItem "Item 1"
    CbB.Object.AddItem "Item 2"
    CbB.Object.AddItem "Item 3"
    
    VariableModule1 = old1: VariableModule2 = old2                       '<========
    
    'Application.OnTime Now, "ValoriserVariableModule1"
End Sub

Option Explicit
Public tablo
Public cel As Range
Public ecart&
Private Type RECT
    Left As Long
    Top As Long
    Right As Long
    Bottom As Long
End Type    'bouton coin arrondi
Private Sub Nocaption()
    Dim lRet As Long, lWidth, lHeight, r As RECT, ptopx#, t&, L&, hwnd&
    With ActiveWindow.ActivePane: ptopx = Round((.PointsToScreenPixelsX(72) - .PointsToScreenPixelsX(0)) / 72, 2): End With
    t = (Me.Height - Me.InsideHeight) * ptopx
    L = ((Me.Width - Me.InsideWidth) / 2) * ptopx
    r.Left = L: r.Top = t - L
    r.Right = (Combo.Width * ptopx) + L
    r.Bottom = (Combo.Height * ptopx) + t - L
    lWidth = r.Right: lHeight = r.Bottom
    hwnd = ExecuteExcel4Macro("CALL(""user32"",""GetActiveWindow"",""JCC"")")         'api GetActiveWindow
    lRet = ExecuteExcel4Macro("CALL(""gdi32"",""CreateRoundRectRgn"",""JJJJJJJ""," & r.Left & ", " & r.Top & ", " & lWidth & ", " & lHeight & ", " & 1 & ", " & 1 & ")")
    ExecuteExcel4Macro ("CALL(""user32"",""SetWindowRgn"",""JJJJ""," & hwnd & ", " & lRet & ", " & 1 & ")")
    ExecuteExcel4Macro ("CALL(""gdi32"",""DeleteObject"",""JJ""," & lRet & ")")
End Sub

Sub CréationComboBox()
    Dim t
    t = Array("item1", "item2", "item3")
    ufListbox.ShowX t, , vbRed, vbYellow, "algerian", 10
    'ufListbox.ShowX t'tout par défaut
End Sub

Option Explicit

Private Type RECT
    Left As Long
    Top As Long
    Right As Long
    Bottom As Long
End Type

Private Sub Nocaption()
    Dim lRet As Long, lWidth, lHeight, r As RECT, ptopx#, t&, L&, hwnd&
    With ActiveWindow.ActivePane: ptopx = Round((.PointsToScreenPixelsX(72) - .PointsToScreenPixelsX(0)) / 72, 2): End With
    t = (Me.Height - Me.InsideHeight) * ptopx
    L = ((Me.Width - Me.InsideWidth) / 2) * ptopx
    r.Left = L: r.Top = t - L
    r.Right = (Combo.Width * ptopx) + L
    r.Bottom = (Combo.Height * ptopx) + t - L
    lWidth = r.Right: lHeight = r.Bottom
    hwnd = ExecuteExcel4Macro("CALL(""user32"",""GetActiveWindow"",""JCC"")")         'api GetActiveWindow
    lRet = ExecuteExcel4Macro("CALL(""gdi32"",""CreateRoundRectRgn"",""JJJJJJJ""," & r.Left & ", " & r.Top & ", " & lWidth & ", " & lHeight & ", " & 1 & ", " & 1 & ")")
    ExecuteExcel4Macro ("CALL(""user32"",""SetWindowRgn"",""JJJJ""," & hwnd & ", " & lRet & ", " & 1 & ")")
    ExecuteExcel4Macro ("CALL(""gdi32"",""DeleteObject"",""JJ""," & lRet & ")")
End Sub

Private Sub UserForm_Initialize()
    Call Nocaption
End Sub

With ufListbox
        .tablo = arr
        If rng Is Nothing Then Set .cel = ActiveCell Else Set .cel = rng
        .ecart = Me.Height - .InsideHeight - 3

With ufListbox 'avec l'object userform 'DANS  UN BLOC WIDTH 
.tablo = arr 'lavariable public [B]tablo [/B]du userform= la variable arr(l'array injecté)
'si rng (argument(2) de la fonction("ShowX") = rien alors la variable public [B]cel [/B]du userform  est la cellule active sinon c'est la variable rng injecté 
If rng Is Nothing Then Set .cel = ActiveCell Else Set .cel = rng
'ecart = la hauteur du userform - l'interieur du userform 
.ecart = .Height - .InsideHeight - 3

'fonction show perso incluant des parametres
Public Function ShowX(arr, _
                      Optional rng As Range = Nothing, _
                      Optional backgroundcolor As Long = vbWhite, _
                      Optional FontColor As Long = 0, _
                      Optional Fontname As String = "calibri", _
                      Optional Fontsize As Long = 8)
    With ufListbox    'AVEC L OBJECT UFLISTBOX'DONC  LA ON EST DANS UN BLOC WITH OBJECT USERFORM ET NON MODULE
'arr est l'array ijincté par l appel dans le module1
        .tablo = arr    'LA VARIABLE TABLO APPARTENANT A L'USERFORM EN PUBLIC = L ARGUMENT ARR(INJECTE DANS L'APEL

        If rng Is Nothing Then Set .cel = ActiveCell Else Set .cel = rng    'attention au "." devant cel on est dans un bloc with  et cel etant une variable du userform

'rng est le 2d argument de la fonction c'est un object range  en optional =nothing si omis
' donc si rng est omis donc = nothing  ben la varaiable  public  (cel) de l'userform = la cellule active sinon  cest le rng injecté

'comme on supprime pas la caption comme dans ma version 2  avec setwindowlong
'mais que l'on decoupe avec une region  il faut prevoir un ecart de la hauteur de la caption soit le tout - l'interieur du userform

        .ecart = .Height - .InsideHeight - 3

        'et maintenant avec la combo  on modifie les propertie avec les arguments  (pas besoins d'expliquer cela je pense
        With .Combo
            .BackColor = backgroundcolor
            .ForeColor = FontColor
            .Font.Name = Fontname
            .Font.Size = Fontsize
        End With
        'et enfin maintenant on fait  le vrai  show qui va afficher le userform avec le combo modifié et les variable cel  et tablo  valoriser
        .Show 0

    End With
    'TOUTE LES Variables PUBLIC DU USERFORM  DOIVENT ETRE PRÉCÉDÉES PAR UN "."!!!!!!!!

End Function