compléter les optionbutton d'un userform grace aux informations d'un tableau excel

For n = 2 To Range("A65536").End(xlUp).Row
 If Range("a" & n) = Cbonom Then
  TxtCP = Range("b" & n)
  Txtville = Range("c" & n)
  If Range("D" & n) = Me.Optgpe1.Caption Then Me.Optgpe1 = True
  If Range("D" & n) = Me.Optgpe2.Caption Then Me.Optgpe2 = True
 End If

Private Sub CBONOM_Change()
Dim r As Long
    With Cbonom
        If .ListIndex > -1 Then
            r = Sheets("bd").Range(.RowSource)(.ListIndex + 1).Row
        End If
    End With
    With Sheets("bd")
        TxtCP = Range("b" & r)
        Txtville = .Range("c" & r)
        Optgpe1 = LCase(.Range("d" & r)) = LCase(Optgpe1.Caption)
        Optgpe2 = Not Optgpe1
    End With
End Sub

For n = 2 To Range("A65536").End(xlUp).Row
 If Ucase(Range("a" & n)) =Ucase(Cbonom) Then
  TxtCP = Range("b" & n)
  Txtville = Range("c" & n)
  If Range("D" & n) = Me.Optgpe1.Caption Then Me.Optgpe1 = True
  If Range("D" & n) = Me.Optgpe2.Caption Then Me.Optgpe2 = True
 End If