Variable objet ou variable de bloc with non définie
Bonjour à tous,
Le message d'erreur suivant s'affiche : "variable objet ou variable de bloc with non définie"
J'ai vu plusieurs post qui en parle, mais je ne suis pas suffisemment pro pour comprendre pourquoi ça bug.
Si vous avez une idée, ça serait cool ! =)
Option Explicit
Private Sub UserForm_Initialize()
TXBPRE = Sheets("LISTES").Range("J3")
TXBNOM = Sheets("LISTES").Range("J4")
TXBDAT = Now()
TXBDAT = Format(TXBDAT, "dd/mm/yyyy")
TXBMA1 = Sheets("LISTES").Range("T2")
TXBMA2 = Sheets("LISTES").Range("T3")
TXBMA3 = Sheets("LISTES").Range("T4")
TXBMA4 = Sheets("LISTES").Range("T5")
TXBMA5 = Sheets("LISTES").Range("T6")
End Sub
Private Sub Retour_Click()
Unload Me 'Fermer ce UserForm
VBAProject.FRMRAS.Show 'Ouvrir le UserForm
End Sub
' Réfrences ******************************************************************
Private Sub CBBDU1_AfterUpdate()
Dim CL As Range
Dim Dlig As Integer
Dlig = Cells(Rows.Count, "A").End(xlUp).Row
Set CL = Range("A2:A" & Dlig).Find(What:=Me.CBBDU1.Value, LookIn:=xlValues, LookAt:=xlWhole)
End Sub
Private Sub CBBDU2_AfterUpdate()
Dim CL As Range
Dim Dlig As Integer
Dlig = Cells(Rows.Count, "A").End(xlUp).Row
Set CL = Range("A2:A" & Dlig).Find(What:=Me.CBBDU2.Value, LookIn:=xlValues, LookAt:=xlWhole)
End Sub
Private Sub CBBDU3_AfterUpdate()
Dim CL As Range
Dim Dlig As Integer
Dlig = Cells(Rows.Count, "A").End(xlUp).Row
Set CL = Range("A2:A" & Dlig).Find(What:=Me.CBBDU3.Value, LookIn:=xlValues, LookAt:=xlWhole)
End Sub
Private Sub CBBDU4_AfterUpdate()
Dim CL As Range
Dim Dlig As Integer
Dlig = Cells(Rows.Count, "A").End(xlUp).Row
Set CL = Range("A2:A" & Dlig).Find(What:=Me.CBBDU4.Value, LookIn:=xlValues, LookAt:=xlWhole)
End Sub
Private Sub CBBDU5_AfterUpdate()
Dim CL As Range
Dim Dlig As Integer
Dlig = Cells(Rows.Count, "A").End(xlUp).Row
Set CL = Range("A2:A" & Dlig).Find(What:=Me.CBBDU5.Value, LookIn:=xlValues, LookAt:=xlWhole)
End Sub
' ****************************************************************************
' Activités ******************************************************************
Private Sub CBBAC1_Change()
Dim CL As Variant
Dim Dlig As Integer
Dlig = Cells(Rows.Count, "A").End(xlUp).Row
Set CL = Range("A2:A" & Dlig).Find(What:=Me.CBBAC1.Value, LookIn:=xlValues, LookAt:=xlWhole)
Sheets("TOUT").Range("E" & CL.Row) = Me.CBBAC1.Value
End Sub
Private Sub CBBAC2_Change()
Dim CL As Range
Dim Dlig As Integer
Dlig = Cells(Rows.Count, "A").End(xlUp).Row
Set CL = Range("A2:A" & Dlig).Find(What:=Me.CBBAC2.Value, LookIn:=xlValues, LookAt:=xlWhole)
Sheets("TOUT").Range("E" & CL.Row) = Me.CBBAC2.Value
End Sub
Private Sub CBBAC3_Change()
Dim CL As Range
Dim Dlig As Integer
Dlig = Cells(Rows.Count, "A").End(xlUp).Row
Set CL = Range("A2:A" & Dlig).Find(What:=Me.CBBAC3.Value, LookIn:=xlValues, LookAt:=xlWhole)
Sheets("TOUT").Range("E" & CL.Row) = Me.CBBAC3.Value
End Sub
Private Sub CBBAC4_Change()
Dim CL As Range
Dim Dlig As Integer
Dlig = Cells(Rows.Count, "A").End(xlUp).Row
Set CL = Range("A2:A" & Dlig).Find(What:=Me.CBBAC4.Value, LookIn:=xlValues, LookAt:=xlWhole)
Sheets("TOUT").Range("E" & CL.Row) = Me.CBBAC4.Value
End Sub
Private Sub CBBAC5_Change()
Dim CL As Range
Dim Dlig As Integer
Dlig = Cells(Rows.Count, "A").End(xlUp).Row
Set CL = Range("A2:A" & Dlig).Find(What:=Me.CBBAC5.Value, LookIn:=xlValues, LookAt:=xlWhole)
Sheets("TOUT").Range("E" & CL.Row) = Me.CBBAC5.Value
End Sub
' ****************************************************************************
' Durée***********************************************************************
Private Sub CBBDU1_Change()
Dim CL As Range
Dim Dlig As Integer
Dlig = Cells(Rows.Count, "A").End(xlUp).Row
Set CL = Range("A2:A" & Dlig).Find(What:=Me.CBBDU1.Value, LookIn:=xlValues, LookAt:=xlWhole)
Sheets("TOUT").Range("F" & CL.Row) = Me.CBBDU1.Value
CBBDU1 = Format(CBBDU1, "hh:mm")
End Sub
Private Sub CBBDU2_Change()
Dim CL As Range
Dim Dlig As Integer
Dlig = Cells(Rows.Count, "A").End(xlUp).Row
Set CL = Range("A2:A" & Dlig).Find(What:=Me.CBBDU2.Value, LookIn:=xlValues, LookAt:=xlWhole)
Sheets("TOUT").Range("F" & CL.Row) = Me.CBBDU2.Value
CBBDU2 = Format(CBBDU2, "hh:mm")
End Sub
Private Sub CBBDU3_Change()
Dim CL As Range
Dim Dlig As Integer
Dlig = Cells(Rows.Count, "A").End(xlUp).Row
Set CL = Range("A2:A" & Dlig).Find(What:=Me.CBBDU3.Value, LookIn:=xlValues, LookAt:=xlWhole)
Sheets("TOUT").Range("F" & CL.Row) = Me.CBBDU3.Value
CBBDU3 = Format(CBBDU3, "hh:mm")
End Sub
Private Sub CBBDUC4_Change()
Dim CL As Range
Dim Dlig As Integer
Dlig = Cells(Rows.Count, "A").End(xlUp).Row
Set CL = Range("A2:A" & Dlig).Find(What:=Me.CBBDU4.Value, LookIn:=xlValues, LookAt:=xlWhole)
Sheets("TOUT").Range("F" & CL.Row) = Me.CBBDU4.Value
CBBDU4 = Format(CBBDU4, "hh:mm")
End Sub
Private Sub CBBDU5_Change()
Dim CL As Range
Dim Dlig As Integer
Dlig = Cells(Rows.Count, "A").End(xlUp).Row
Set CL = Range("A2:A" & Dlig).Find(What:=Me.CBBDU5.Value, LookIn:=xlValues, LookAt:=xlWhole)
Sheets("TOUT").Range("F" & CL.Row) = Me.CBBDU5.Value
CBBDU5 = Format(CBBDU5, "hh:mm")
End Sub
' ****************************************************************************
' Nombre *********************************************************************
Private Sub TXBNB1_Change()
Dim CL As Range
Dim Dlig As Integer
Dlig = Cells(Rows.Count, "A").End(xlUp).Row
Set CL = Range("A2:A" & Dlig).Find(What:=Me.TXBNB1.Value, LookIn:=xlValues, LookAt:=xlWhole)
Sheets("TOUT").Range("G" & CL.Row) = Me.TXBNB1.Value
End Sub
Private Sub TXBNB2_Change()
Dim CL As Range
Dim Dlig As Integer
Dlig = Cells(Rows.Count, "A").End(xlUp).Row
Set CL = Range("A2:A" & Dlig).Find(What:=Me.TXBNB2.Value, LookIn:=xlValues, LookAt:=xlWhole)
Sheets("TOUT").Range("G" & CL.Row) = Me.TXBNB2.Value
End Sub
Private Sub TXBNB3_Change()
Dim CL As Range
Dim Dlig As Integer
Dlig = Cells(Rows.Count, "A").End(xlUp).Row
Set CL = Range("A2:A" & Dlig).Find(What:=Me.TXBNB3.Value, LookIn:=xlValues, LookAt:=xlWhole)
Sheets("TOUT").Range("G" & CL.Row) = Me.TXBNB3.Value
End Sub
Private Sub TXBNB4_Change()
Dim CL As Range
Dim Dlig As Integer
Dlig = Cells(Rows.Count, "A").End(xlUp).Row
Set CL = Range("A2:A" & Dlig).Find(What:=Me.TXBNB4.Value, LookIn:=xlValues, LookAt:=xlWhole)
Sheets("TOUT").Range("G" & CL.Row) = Me.TXBNB4.Value
End Sub
Private Sub TXBNB5_Change()
Dim CL As Range
Dim Dlig As Integer
Dlig = Cells(Rows.Count, "A").End(xlUp).Row
Set CL = Range("A2:A" & Dlig).Find(What:=Me.TXBNB5.Value, LookIn:=xlValues, LookAt:=xlWhole)
Sheets("TOUT").Range("G" & CL.Row) = Me.TXBNB5.Value
End Sub
' ****************************************************************************
Voici mon fichier :
Bonjour,
Le message d'erreur suivant s'affiche : "variable objet ou variable de bloc with non définie"
oui mais ... sur quelle ligne de code ?
Dans l'userform FRMACT, les combobox CBBAC1 à CBBAC5 ne sont pas initialisées; lorsqu'on qu'on veut y saisir une activité la procédure Private Sub CBBAC1_Change() (ou celle correspondant à la combobox) se déclenche dès le premier caractère entré, la ligne ...find.... ne trouve pas et donc plantage.
Après test d'initialisation d'une combobox, il y a le même phénomène car on va chercher , en colonne E, l'activité sélectionnée, mais...la feuille de la plage de recherche n'est pas précisée et de plus dans la feuille TOUT, l'activité n'est pas renseignée (ni en colonne E ni ailleurs...) le ...Find... ne trouve rien et donc ...plantage.
Pas fait de tests supplémentaires...
A+