Userform ne pas afficher textbox vide
D
Bonjour,
dans le cadre d'un formulaire excel je voudrais savoir s'il était possible de ne pas afficher les textbox et autres s'il n'y avait pas de résultat
si l'on imagine un form comprenant 10 textbox correspondant à max 10 résultat possible mais que l'id que l'on cherche en trouve 8 comment ne pas afficher les deux derniers textbox?
merci à vous
Bonjour,
Il existe une propriété TextBox.Visible, False : elle n’apparaît pas, True le cas échéant.
https://docs.microsoft.com/fr-fr/office/vba/api/access.textbox.visible
Un exemple :
If Len(TextBox1.Value) = 0 Then TextBox1.Visible = False Else TextBox1.Visible = Truep
Salut Pedro,
Mais... Pas besoin de If, lorsque le résultat attendu est un Booléen.
Ceci suffit :
TextBox1.Visible = Len(TextBox1.Value) > 0D
parfait merci à vous pour vos retours!
je vais tester
D
par contre comment s'il vous plait l'implanter dans mon code*
If ok Then
Del_Nr = ici.Offset(0, 2)
Else
Del_Nr = ""
End Ifquand je test ça me dit Objet Requis
Merci par avance
p
Il nous faudrait l'intégralité du code :
> qui appelle l'userform
> de l'userform lui-même.
Tout ton code quoi...
Salut pijaku,
Oui c'est tout à fait vrai, mais cette syntaxe est à mon avis moins instinctive pour des utilisateurs peu familiers avec VBA (ou le codage au sens large) !
Darkangel,
Cet extrait ne nous aide pas beaucoup... Il faudrait à minima fournir le code complet de la macro, et idéalement le fichier associé puisque la macro interagit avec celui-ci !
D
Voici le code complet
For i = 1 To 18
With Sheets("WP")
ok = False
Set ici = .Range("C:C").Find(Acronym, LookIn:=xlValues)
If Not ici Is Nothing Then
prem = ici.Address
Do
If ici.Offset(0, -2) = (ProjectId & "_" & i) Then ok = True
If Not ok Then Set ici = .Range("C:C").FindNext(ici)
Loop While Not ici Is Nothing And ici.Address <> prem And Not ok
End If
If ok Then
WP_nb = "WP " & ici.Offset(0, 1)
WP_Title = ici.Offset(0, 2)
WP_PM = ici.Offset(0, 3)
WP_Month_Start = ici.Offset(0, 4)
WP_Start_Date = ici.Offset(0, 5)
WP_Month_End = ici.Offset(0, 6)
WP_End_Date = ici.Offset(0, 7)
WP_Status = ici.Offset(0, 8)
Else
WP_nb = ""
WP_Title = ""
WP_PM = ""
WP_Month_Start = ""
WP_Start_Date = ""
WP_Month_End = ""
WP_End_Date = ""
WP_Status = ""
End If
End With
Select Case i
Case 1
WP1 = WP_nb
WP1_Title = WP_Title
WP1_PM = WP_PM
WP1_Month_Start = WP_Month_Start
WP1_Start = WP_Start_Date
WP1_Month_End = WP_Month_End
WP1_End = WP_End_Date
WP1_Status = WP_Status
Case 2
WP2 = WP_nb
WP2_Title = WP_Title
WP2_PM = WP_PM
WP2_Month_Start = WP_Month_Start
WP2_Start = WP_Start_Date
WP2_Month_End = WP_Month_End
WP2_End = WP_End_Date
WP2_Status = WP_Status
Case 3
WP3 = WP_nb
WP3_Title = WP_Title
WP3_PM = WP_PM
WP3_Month_Start = WP_Month_Start
WP3_Start = WP_Start_Date
WP3_Month_End = WP_Month_End
WP3_End = WP_End_Date
WP3_Status = WP_Status
Case 4
WP4 = WP_nb
WP4_Title = WP_Title
WP4_PM = WP_PM
WP4_Month_Start = WP_Month_Start
WP4_Start = WP_Start_Date
WP4_Month_End = WP_Month_End
WP4_End = WP_End_Date
WP4_Status = WP_Status
Case 5
WP5 = WP_nb
WP5_Title = WP_Title
WP5_PM = WP_PM
WP5_Month_Start = WP_Month_Start
WP5_Month_End = WP_Month_End
WP5_Start = WP_Start_Date
WP5_End = WP_End_Date
WP5_Status = WP_Status
Case 6
WP6 = WP_nb
WP6_Title = WP_Title
WP6_PM = WP_PM
WP6_Month_Start = WP_Month_Start
WP6_Start = WP_Start_Date
WP6_Month_End = WP_Month_End
WP6_End = WP_End_Date
WP6_Status = WP_Status
Case 7
WP7 = WP_nb
WP7_Title = WP_Title
WP7_PM = WP_PM
WP7_Month_Start = WP_Month_Start
WP7_Start = WP_Start_Date
WP7_Month_End = WP_Month_End
WP7_End = WP_End_Date
WP7_Status = WP_Status
Case 8
WP8 = WP_nb
WP8_Title = WP_Title
WP8_PM = WP_PM
WP8_Month_Start = WP_Month_Start
WP8_Start = WP_Start_Date
WP8_Month_End = WP_Month_End
WP8_End = WP_End_Date
WP8_Status = WP_Status
Case 9
WP9 = WP_nb
WP9_Title = WP_Title
WP9_PM = WP_PM
WP9_Month_Start = WP_Month_Start
WP9_Start = WP_Start_Date
WP9_Month_End = WP_Month_End
WP9_End = WP_End_Date
WP9_Status = WP_Status
Case 10
WP10 = WP_nb
WP10_Title = WP_Title
WP10_PM = WP_PM
WP10_Month_Start = WP_Month_Start
WP10_Start = WP_Start_Date
WP10_Month_End = WP_Month_End
WP10_End = WP_End_Date
WP10_Status = WP_Status
Case 11
WP11 = WP_nb
WP11_Title = WP_Title
WP11_PM = WP_PM
WP11_Month_Start = WP_Month_Start
WP11_Start = WP_Start_Date
WP11_Month_End = WP_Month_End
WP11_End = WP_End_Date
WP11_Status = WP_Status
Case 12
WP12 = WP_nb
WP12_Title = WP_Title
WP12_PM = WP_PM
WP12_Month_Start = WP_Month_Start
WP12_Start = WP_Start_Date
WP12_Month_End = WP_Month_End
WP12_End = WP_End_Date
WP12_Status = WP_Status
Case 13
WP13 = WP_nb
WP13_Title = WP_Title
WP13_PM = WP_PM
WP13_Month_Start = WP_Month_Start
WP13_Start = WP_Start_Date
WP13_Month_End = WP_Month_End
WP13_End = WP_End_Date
WP13_Status = WP_Status
Case 14
WP14 = WP_nb
WP14_Title = WP_Title
WP14_PM = WP_PM
WP14_Month_Start = WP_Month_Start
WP14_Start = WP_Start_Date
WP14_Month_End = WP_Month_End
WP14_End = WP_End_Date
WP14_Status = WP_Status
Case 15
WP15 = WP_nb
WP15_Title = WP_Title
WP15_PM = WP_PM
WP15_Month_Start = WP_Month_Start
WP15_Start = WP_Start_Date
WP15_Month_End = WP_Month_End
WP15_End = WP_End_Date
WP15_Status = WP_Status
Case 16
WP16 = WP_nb
WP16_Title = WP_Title
WP16_PM = WP_PM
WP16_Month_Start = WP_Month_Start
WP16_Start = WP_Start_Date
WP16_Month_End = WP_Month_End
WP16_End = WP_End_Date
WP16_Status = WP_Status
Case 17
WP17 = WP_nb
WP17_Title = WP_Title
WP17_PM = WP_PM
WP17_Month_Start = WP_Month_Start
WP17_Start = WP_Start_Date
WP17_Month_End = WP_Month_End
WP17_End = WP_End_Date
WP17_Status = WP_Status
Case 18
WP18 = WP_nb
WP18_Title = WP_Title
WP18_PM = WP_PM
WP18_Month_Start = WP_Month_Start
WP18_Start = WP_Start_Date
WP18_Month_End = WP_Month_End
WP18_End = WP_End_Date
WP18_Status = WP_Status
End Select
NextD
mais voila également la macro en entier
mon objectif est de pouvoir enlever les champs n'ayant aucun résultat
merci à vous pour votre aide
Private Sub UserForm_Initialize()
Acronym = Sheets("Projects' View").Range("G2")
Completeform
End Sub
Private Sub Completeform()
ProjectId = Application.WorksheetFunction.VLookup(Acronym, Sheets("Projects").Range("A:N"), 2, 0)
Investigator = Application.WorksheetFunction.VLookup(Acronym, Sheets("Projects").Range("A:N"), 3, 0)
Title = Application.WorksheetFunction.VLookup(Acronym, Sheets("Projects").Range("A:N"), 4, 0)
CallFrame = Application.WorksheetFunction.VLookup(Acronym, Sheets("Projects").Range("A:N"), 5, 0)
CallTheme = Application.WorksheetFunction.VLookup(Acronym, Sheets("Projects").Range("A:N"), 6, 0)
CallDate = Application.WorksheetFunction.VLookup(Acronym, Sheets("Projects").Range("A:N"), 7, 0)
Topic = Application.WorksheetFunction.VLookup(Acronym, Sheets("Projects").Range("A:N"), 8, 0)
Account = Application.WorksheetFunction.VLookup(Acronym, Sheets("Projects").Range("A:N"), 9, 0)
Status = Application.WorksheetFunction.VLookup(Acronym, Sheets("Projects").Range("A:N"), 10, 0)
StartDate = Application.WorksheetFunction.VLookup(Acronym, Sheets("Projects").Range("A:N"), 11, 0)
Duration = Application.WorksheetFunction.VLookup(Acronym, Sheets("Projects").Range("A:N"), 12, 0) & " Months"
Prolongation = Application.WorksheetFunction.VLookup(Acronym, Sheets("Projects").Range("A:N"), 13, 0) & " Months"
EndDate = Application.WorksheetFunction.VLookup(Acronym, Sheets("Projects").Range("A:N"), 14, 0)
Abstract = Application.WorksheetFunction.VLookup(Acronym, Sheets("Abstract").Range("A:B"), 2, 0)
For i = 1 To 8
With Sheets("Deliverables")
ok = False
Set ici = .Range("B:B").Find(Acronym, LookIn:=xlValues)
If Not ici Is Nothing Then
prem = ici.Address
Do
If ici.Offset(0, 2) = ("Reporting " & i) Then ok = True
If Not ok Then Set ici = .Range("B:B").FindNext(ici)
Loop While Not ici Is Nothing And ici.Address <> prem And Not ok
End If
If ok Then
debut = ici.Offset(0, 6)
fin = ici.Offset(0, 8)
Else
debut = ""
fin = ""
End If
End With
Select Case i
Case 1
Period1_Start = debut
Period1_End = fin
Case 2
Period2_Start = debut
Period2_End = fin
Case 3
Period3_Start = debut
Period3_End = fin
Case 4
Period4_Start = debut
Period4_End = fin
Case 5
Period5_Start = debut
Period5_End = fin
Case 6
Period6_Start = debut
Period6_End = fin
Case 7
Period7_Start = debut
Period7_End = fin
Case 8
Period8_Start = debut
Period8_End = fin
End Select
Next
For i = 1 To 18
With Sheets("WP")
ok = False
Set ici = .Range("C:C").Find(Acronym, LookIn:=xlValues)
If Not ici Is Nothing Then
prem = ici.Address
Do
If ici.Offset(0, -2) = (ProjectId & "_" & i) Then ok = True
If Not ok Then Set ici = .Range("C:C").FindNext(ici)
Loop While Not ici Is Nothing And ici.Address <> prem And Not ok
End If
If ok Then
WP_nb = "WP " & ici.Offset(0, 1)
WP_Title = ici.Offset(0, 2)
WP_PM = ici.Offset(0, 3)
WP_Month_Start = ici.Offset(0, 4)
WP_Start_Date = ici.Offset(0, 5)
WP_Month_End = ici.Offset(0, 6)
WP_End_Date = ici.Offset(0, 7)
WP_Status = ici.Offset(0, 8)
Else
WP_nb = ""
WP_Title = ""
WP_PM = ""
WP_Month_Start = ""
WP_Start_Date = ""
WP_Month_End = ""
WP_End_Date = ""
WP_Status = ""
End If
End With
Select Case i
Case 1
WP1 = WP_nb
WP1_Title = WP_Title
WP1_PM = WP_PM
WP1_Month_Start = WP_Month_Start
WP1_Start = WP_Start_Date
WP1_Month_End = WP_Month_End
WP1_End = WP_End_Date
WP1_Status = WP_Status
Case 2
WP2 = WP_nb
WP2_Title = WP_Title
WP2_PM = WP_PM
WP2_Month_Start = WP_Month_Start
WP2_Start = WP_Start_Date
WP2_Month_End = WP_Month_End
WP2_End = WP_End_Date
WP2_Status = WP_Status
Case 3
WP3 = WP_nb
WP3_Title = WP_Title
WP3_PM = WP_PM
WP3_Month_Start = WP_Month_Start
WP3_Start = WP_Start_Date
WP3_Month_End = WP_Month_End
WP3_End = WP_End_Date
WP3_Status = WP_Status
Case 4
WP4 = WP_nb
WP4_Title = WP_Title
WP4_PM = WP_PM
WP4_Month_Start = WP_Month_Start
WP4_Start = WP_Start_Date
WP4_Month_End = WP_Month_End
WP4_End = WP_End_Date
WP4_Status = WP_Status
Case 5
WP5 = WP_nb
WP5_Title = WP_Title
WP5_PM = WP_PM
WP5_Month_Start = WP_Month_Start
WP5_Month_End = WP_Month_End
WP5_Start = WP_Start_Date
WP5_End = WP_End_Date
WP5_Status = WP_Status
Case 6
WP6 = WP_nb
WP6_Title = WP_Title
WP6_PM = WP_PM
WP6_Month_Start = WP_Month_Start
WP6_Start = WP_Start_Date
WP6_Month_End = WP_Month_End
WP6_End = WP_End_Date
WP6_Status = WP_Status
Case 7
WP7 = WP_nb
WP7_Title = WP_Title
WP7_PM = WP_PM
WP7_Month_Start = WP_Month_Start
WP7_Start = WP_Start_Date
WP7_Month_End = WP_Month_End
WP7_End = WP_End_Date
WP7_Status = WP_Status
Case 8
WP8 = WP_nb
WP8_Title = WP_Title
WP8_PM = WP_PM
WP8_Month_Start = WP_Month_Start
WP8_Start = WP_Start_Date
WP8_Month_End = WP_Month_End
WP8_End = WP_End_Date
WP8_Status = WP_Status
Case 9
WP9 = WP_nb
WP9_Title = WP_Title
WP9_PM = WP_PM
WP9_Month_Start = WP_Month_Start
WP9_Start = WP_Start_Date
WP9_Month_End = WP_Month_End
WP9_End = WP_End_Date
WP9_Status = WP_Status
Case 10
WP10 = WP_nb
WP10_Title = WP_Title
WP10_PM = WP_PM
WP10_Month_Start = WP_Month_Start
WP10_Start = WP_Start_Date
WP10_Month_End = WP_Month_End
WP10_End = WP_End_Date
WP10_Status = WP_Status
Case 11
WP11 = WP_nb
WP11_Title = WP_Title
WP11_PM = WP_PM
WP11_Month_Start = WP_Month_Start
WP11_Start = WP_Start_Date
WP11_Month_End = WP_Month_End
WP11_End = WP_End_Date
WP11_Status = WP_Status
Case 12
WP12 = WP_nb
WP12_Title = WP_Title
WP12_PM = WP_PM
WP12_Month_Start = WP_Month_Start
WP12_Start = WP_Start_Date
WP12_Month_End = WP_Month_End
WP12_End = WP_End_Date
WP12_Status = WP_Status
Case 13
WP13 = WP_nb
WP13_Title = WP_Title
WP13_PM = WP_PM
WP13_Month_Start = WP_Month_Start
WP13_Start = WP_Start_Date
WP13_Month_End = WP_Month_End
WP13_End = WP_End_Date
WP13_Status = WP_Status
Case 14
WP14 = WP_nb
WP14_Title = WP_Title
WP14_PM = WP_PM
WP14_Month_Start = WP_Month_Start
WP14_Start = WP_Start_Date
WP14_Month_End = WP_Month_End
WP14_End = WP_End_Date
WP14_Status = WP_Status
Case 15
WP15 = WP_nb
WP15_Title = WP_Title
WP15_PM = WP_PM
WP15_Month_Start = WP_Month_Start
WP15_Start = WP_Start_Date
WP15_Month_End = WP_Month_End
WP15_End = WP_End_Date
WP15_Status = WP_Status
Case 16
WP16 = WP_nb
WP16_Title = WP_Title
WP16_PM = WP_PM
WP16_Month_Start = WP_Month_Start
WP16_Start = WP_Start_Date
WP16_Month_End = WP_Month_End
WP16_End = WP_End_Date
WP16_Status = WP_Status
Case 17
WP17 = WP_nb
WP17_Title = WP_Title
WP17_PM = WP_PM
WP17_Month_Start = WP_Month_Start
WP17_Start = WP_Start_Date
WP17_Month_End = WP_Month_End
WP17_End = WP_End_Date
WP17_Status = WP_Status
Case 18
WP18 = WP_nb
WP18_Title = WP_Title
WP18_PM = WP_PM
WP18_Month_Start = WP_Month_Start
WP18_Start = WP_Start_Date
WP18_Month_End = WP_Month_End
WP18_End = WP_End_Date
WP18_Status = WP_Status
End Select
Next
For i = 1 To 20
With Sheets("Deliverables")
ok = False
Set ici = .Range("B:B").Find(Acronym, LookIn:=xlValues)
If Not ici Is Nothing Then
prem = ici.Address
Do
If ici.Offset(0, -1) = (ProjectId & "_" & i) Then ok = True
If Not ok Then Set ici = .Range("B:B").FindNext(ici)
Loop While Not ici Is Nothing And ici.Address <> prem And Not ok
End If
If ok Then
Del_Nr = ici.Offset(0, 2)
Del_Name = ici.Offset(0, 3)
Del_PI = ici.Offset(0, 4)
Del_Start_month = "M " & ici.Offset(0, 5)
Del_Start_date = ici.Offset(0, 6)
Del_End_month = "M " & ici.Offset(0, 7)
Del_End_date = ici.Offset(0, 8)
Achievements = Format(Val(ici.Offset(0, 9)) / 100, "#0%") & " Achieved"
Status = ici.Offset(0, 12)
Else
Del_Nr = ""
Del_Name = ""
Del_PI = ""
Del_Start_month = ""
Del_Start_date = ""
Del_End_month = ""
Del_End_date = ""
Achievements = ""
Status = ""
End If
End With
Select Case i
Case 1
Del1_Nr = Del_Nr
Del1_Name = Del_Name
Del1_PI = Del_PI
Del1_Start_month = Del_Start_month
Del1_Start_date = Del_Start_date
Del1_End_month = Del_End_month
Del1_End_date = Del_End_date
Del1_Achievements = Achievements
Del1_Status = Status
End Select
Next
End Sub