[Excel-VBA] Base de données --> Formulaire

Private Sub UserForm_Initialize()
    'L'idée est de REMPLIR LE FORMULAIRE DEPUIS LA BASE DE DONNEES
    Dim macellule As Range
    ' sélection de la feuille où se trouve les clients "Feuil2"
    Worksheets("Feuil2").Activate
    'Sélection de la cellule du client auquel on souhaite apporter des modifications
    Set macellule = Application.InputBox("Sélectionnez la cellule avec le nom du client.", _
                                         "Sélection de cellules", _
                                         Type:=8)
    If (macellule = "") Then UserForm_Initialize
    '
    ' remplir le formulaire
    With ComboBox1
        .Clear
        .AddItem macellule
        If ComboBox1.ListCount >= 1 Then
            .ListIndex = 0
        End If
    End With
    ' raisons
    CheckBox6.Value = IIf(InStr(1, macellule.Offset(0, 1), "A", vbTextCompare) > 0, True, False)
    CheckBox7.Value = IIf(InStr(1, macellule.Offset(0, 1), "B", vbTextCompare) > 0, True, False)
    CheckBox8.Value = IIf(InStr(1, macellule.Offset(0, 1), "C", vbTextCompare) > 0, True, False)
    CheckBox9.Value = IIf(InStr(1, macellule.Offset(0, 1), "D", vbTextCompare) > 0, True, False)
    ' sexe
    OptionButton7.Value = IIf(macellule.Offset(0, 2) = "Homme", True, False)
    OptionButton8.Value = IIf(macellule.Offset(0, 2) = "Femme", True, False)
    '
End Sub

Select Case (OptionButton7.Value) 
 Case = True 
  MaCellule.Offset(0, 2) = "Homme" 
 Case = False 
  MaCellule.Offset(0, 2) = "Femme" 
 Case = NiTrueNiFalse 
  MaCellule.Offset(0, 2) = "Ange" 
End Select

Select Case (Val(TextBox1.Text)) 
 Case = 1 
  MaCellule.Offset(0, 2) = "Homme" 
 Case = 2 
  MaCellule.Offset(0, 2) = "Femme" 
 Case = 3 
  MaCellule.Offset(0, 2) = "Ange" 
 Case Else 
  MaCellule.Offset(0, 2) = "néant" 
End Select

Public Sub ou Private Sub : une bonne pratique, sinon c'est pris pour de la négligence, manque de rigueur !

Option Explicit
Private HandleUF&
'
Private Sub UserForm_Initialize()
    Const WS_MAXIMIZEBOX& = &H10000, _
        WS_MINIMIZEBOX& = &H20000, GWL_STYLE& = -16&
    HandleUF = FindWindow(vbNullString, Me.Caption)
    SetWindowLong HandleUF, GWL_STYLE, _
        GetWindowLong(HandleUF, GWL_STYLE) Or WS_MAXIMIZEBOX Or WS_MINIMIZEBOX
    BaseUFProc = SetWindowLong(HandleUF, GWL_WNDPROC, BaseUFProc)
End Sub
'
Private Sub UserForm_Terminate()
    SetWindowLong HandleUF, GWL_WNDPROC, BaseUFProc
End Sub
'
Private Sub Enregistrer_Click()
    Call EnregistrerX
    Unload Me
End Sub

Option Explicit

Public Declare Function FindWindow& _
    Lib "user32" Alias "FindWindowA" _
    (ByVal lpClassName$, ByVal lpWindowName$)

Public Declare Function GetWindowLong& _
    Lib "user32" Alias "GetWindowLongA" _
    (ByVal hwnd&, ByVal nIndex&)

Public Declare Function SetWindowLong& _
    Lib "user32" Alias "SetWindowLongA" _
    (ByVal hwnd&, ByVal nIndex&, ByVal dwNewLong&)

Private Declare Function EnableWindow& _
    Lib "user32" _
    (ByVal hwnd&, ByVal fEnable&)

Private Declare Function CallWindowProc& _
    Lib "user32" Alias "CallWindowProcA" _
    (ByVal lpPrevWndFunc&, ByVal hwnd&, ByVal Msg&, ByVal wParam&, ByVal lParam&)

Public Const GWL_WNDPROC& = -4&, WM_SYSCOMMAND& = &H112&

Public BaseUFProc&, BaseXLProc&, AncState&

Function UFProc&(ByVal hwnd&, ByVal uMsg&, ByVal wParam&, ByVal lParam&)
    Dim HwndXL&
    Const SC_MINIMIZE& = &HF020&
    If uMsg = WM_SYSCOMMAND Then
            If wParam = (SC_MINIMIZE And &HFFF0&) Then
                HwndXL = FindWindow("XLMAIN", Application.Caption)
                EnableWindow HwndXL, True
                UserForm1.Hide
                AncState = Application.WindowState
                Application.WindowState = xlMinimized
                BaseXLProc = SetWindowLong(HwndXL, GWL_WNDPROC, AddressOf XLProc)
                UFProc = 1&
                Exit Function
            End If
    End If
    UFProc = CallWindowProc(BaseUFProc, hwnd, uMsg, wParam, lParam)
End Function


Function XLProc&(ByVal hwnd&, ByVal uMsg&, ByVal wParam&, ByVal lParam&)
    Const SC_MAXIMIZE& = &HF030&, _
        SC_RESTORE& = &HF120&, SC_CLOSE& = &HF060&
    If uMsg = WM_SYSCOMMAND Then
            If wParam = (SC_MAXIMIZE And &HFFF0&) Or wParam = (SC_RESTORE _
                And &HFFF0&) Or wParam = SC_CLOSE Then
                SetWindowLong hwnd, GWL_WNDPROC, BaseXLProc
                Application.WindowState = AncState
                UserForm1.Show
                XLProc = 1&
                Exit Function
            End If
    End If
    XLProc = CallWindowProc(BaseXLProc, hwnd, uMsg, wParam, lParam)
End Function
'
Public Sub EnregistrerX()
    ActiveWorkbook.Save
    MsgBox "Enregistrement effectué !", vbInformation, "Etat de la procédure ""Sauvegarder"" !"
End Sub