Microsoft 365 Commentaire cellule dans TextBox dans UserForm

[Francky79]

Bonjour le forum,

Je sollicite votre aide pour la récupération d'un commentaire d'une cellule pour le mettre dans une TextBox qui se trouve dans un userForm.
Dans le code ci-dessous le code récupère le commentaire, si il y a un commentaire c'est ok, par contre si pas de commentaire plantage.
Comment faire pour gérer cette erreur ?

Les 2 lignes posant problème

'UserForm6.TextBox20 = Range("W" & Lig).Comment.Text
'UserForm6.TextBox21 = Range("X" & Lig).Comment.Text

Sub Vision()
Cells.Find(What:=ActiveCell.Value, After:=ActiveCell, LookIn:=xlValues, LookAt:= _
        xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
        , SearchFormat:=False).Activate
    Sheets("Usinage").Select
    Cells.FindNext(After:=ActiveCell).Activate
Dim Lig As Long
Lig = ActiveCell.Row
UserForm6.TextBox1.Text = ActiveCell.Value
UserForm6.TextBox2.Text = Range("B" & Lig).Value
UserForm6.TextBox3.Text = Range("C" & Lig).Value
UserForm6.TextBox7.Text = Range("E" & Lig).Value
UserForm6.TextBox4.Text = Range("G" & Lig).Value
UserForm6.TextBox5.Text = Range("H" & Lig).Value
UserForm6.TextBox6.Text = Range("I" & Lig).Value
UserForm6.TextBox8.Text = Range("AQ" & Lig).Value
UserForm6.TextBox9.Text = Range("J" & Lig).Value
UserForm6.TextBox10.Text = Range("K" & Lig).Value
UserForm6.TextBox11.Text = Range("L" & Lig).Value
UserForm6.TextBox12.Text = Range("M" & Lig).Value
UserForm6.TextBox13.Text = Range("O" & Lig).Value
UserForm6.TextBox14.Text = Range("N" & Lig).Value
UserForm6.TextBox15.Text = Range("Q" & Lig).Value
UserForm6.TextBox16.Text = Range("R" & Lig).Value
UserForm6.TextBox17.Text = Range("S" & Lig).Value
UserForm6.TextBox18.Text = Range("T" & Lig).Value
UserForm6.TextBox19.Text = Range("U" & Lig).Value
'UserForm6.TextBox20 = Range("W" & Lig).Comment.Text
'UserForm6.TextBox21 = Range("X" & Lig).Comment.Text
UserForm6.TextBox22.Text = Range("V" & Lig).Value
UserForm6.TextBox23.Text = Range("W" & Lig).Value
UserForm6.TextBox24.Text = Range("X" & Lig).Value
UserForm6.TextBox25.Text = Range("Y" & Lig).Value
UserForm6.Show
End Sub

Merci pour votre aide,

 

[patricktoulon]

re
bonjour
vu que tu fait de l'auto incrémentation je pense qu'il va nous falloir un fichier exemple

de plus tu utilise find et find next sans controls du find effectif donc si ton find et findnext trouve plus rien après le dernier ben tu plante le bourrin

 

[Francky79]

Merci pour ton aide,

Ci-joint un fichier simplifié.

 

[patricktoulon]

re
ben chez moi j'ai bien les commentaires dans les TextBoxs
je comprends pas tres bien l'utilisation de find ici
ok supprimé donc

ensuite

donc ton module standard

Sub Vision()
Dim Lig&, commentaire As Comment
Lig = ActiveCell.Row
UserForm6.TextBox1.Text = ActiveCell.Value
UserForm6.TextBox2.Text = Range("B" & Lig).Value
UserForm6.TextBox3.Text = Range("C" & Lig).Value
UserForm6.TextBox7.Text = Range("E" & Lig).Value
UserForm6.TextBox4.Text = Range("G" & Lig).Value
UserForm6.TextBox5.Text = Range("H" & Lig).Value
UserForm6.TextBox6.Text = Range("I" & Lig).Value
UserForm6.TextBox8.Text = Range("AQ" & Lig).Value
UserForm6.TextBox9.Text = Range("J" & Lig).Value
UserForm6.TextBox10.Text = Range("K" & Lig).Value
UserForm6.TextBox11.Text = Range("L" & Lig).Value
UserForm6.TextBox12.Text = Range("M" & Lig).Value
UserForm6.TextBox13.Text = Range("O" & Lig).Value
UserForm6.TextBox14.Text = Range("N" & Lig).Value
UserForm6.TextBox15.Text = Range("Q" & Lig).Value
UserForm6.TextBox16.Text = Range("R" & Lig).Value
UserForm6.TextBox17.Text = Range("S" & Lig).Value
UserForm6.TextBox18.Text = Range("T" & Lig).Value
UserForm6.TextBox19.Text = Range("U" & Lig).Value

Set commentaire = Range("W" & Lig).Comment
 If Not commentaire Is Nothing Then UserForm6.TextBox20 = Range("W" & Lig).Comment.Text
 Set commentaire = Range("W" & Lig).Comment
If Not commentaire Is Nothing Then UserForm6.TextBox21 = Range("X" & Lig).Comment.Text

UserForm6.TextBox22.Text = Range("V" & Lig).Value
UserForm6.TextBox23.Text = Range("W" & Lig).Value
UserForm6.TextBox24.Text = Range("X" & Lig).Value
UserForm6.TextBox25.Text = Range("Y" & Lig).Value

UserForm6.Show

End Sub

ensuite le userform

Sub AllsTextBox_change(TbX As MSForms.TextBox)
Select Case TbX.Value
    Case Is = "n"
        TbX.BackColor = RGB(96, 96, 96) 'gris
    Case Is = "o"
        TbX.BackColor = RGB(255, 255, 64) 'jaune
    Case Is = "x"
        TbX.BackColor = RGB(64, 224, 64) ' vert
End Select
End Sub

Private Sub TextBox10_Change(): AllsTextBox_change TextBox10: End Sub

Private Sub TextBox11_Change(): AllsTextBox_change TextBox11: End Sub

Private Sub TextBox12_Change(): AllsTextBox_change TextBox12: End Sub

Private Sub TextBox13_Change(): AllsTextBox_change TextBox13: End Sub

Private Sub TextBox14_Change(): AllsTextBox_change TextBox11: End Sub

Private Sub TextBox15_Change(): AllsTextBox_change TextBox15: End Sub

Private Sub TextBox16_Change(): AllsTextBox_change TextBox16: End Sub

Private Sub TextBox17_Change(): AllsTextBox_change TextBox17: End Sub

Private Sub TextBox18_Change(): AllsTextBox_change TextBox18: End Sub

Private Sub TextBox19_Change(): AllsTextBox_change TextBox19: End Sub

Private Sub TextBox22_Change(): AllsTextBox_change TextBox22: End Sub

Private Sub TextBox23_Change(): AllsTextBox_change TextBox23: End Sub

Private Sub TextBox24_Change(): AllsTextBox_change TextBox24: End Sub

Private Sub TextBox25_Change(): AllsTextBox_change TextBox25: End Sub

Private Sub TextBox4_Change(): AllsTextBox_change TextBox4: End Sub

Private Sub TextBox5_Change(): AllsTextBox_change TextBox5: End Sub

Private Sub TextBox6_Change(): AllsTextBox_change TextBox6: End Sub

Private Sub TextBox9_Change(): AllsTextBox_change TextBox9: End Sub

Private Sub TextBox8_Change()
Select Case Me.TextBox8.Value
    Case Is = ""
        Me.TextBox8.BackColor = RGB(96, 96, 96) 'gris
End Select
End Sub

 

[Francky79]

Ok, merci
Problème résolu.