Probleme variable de bloc with
Bonjour à tous,
Je suis devant un problème que mes connaissances en vb ne me permette pas d'appréhender, alors voilà j'ai un bout de code pour initialiser un userform dans thisworkbook
Private Sub Workbook_SheetBeforeRightClick(ByVal Sh As Object, ByVal target As Range, Cancel As Boolean)
Dim i%, k%, isect
If Sh.Name = "Planning" Then
If target.Count = 1 Then
i = [AList1].Count + 1
Set isect = Application.Intersect(target, [Planning])
If Not isect Is Nothing Then
Cancel = True
k = target.Column
ActiveWindow.ScrollColumn = IIf(k > 30, target.Column - 20, 3)
usfCol.Top = 80
usfCol.Left = 25 * (target.Column + 1) 'IIf(k > 20, Target.Left + 540 - (Target.Column - 1) * (Target.Width), Target.Left + 80)
usfCol.Show
End If
End If
End If
End Subet l'erreur 91 se produit sur usfCol.Top = 80
le probleme est survenue lorsque j'ai modifier mon code d'intégration de liste dans le user form
initialement j'avais ceci
Private Sub UserForm_Initialize()
'ActiveSheet.Protect UserInterfaceOnly:=True
ListBox1.ColumnWidths = "140;30"
ListBox1.List() = [ListPost].Resize(, 1).Value
End SubListPost étant une liste définit sur mon onglet WspostAg
je l'ai modifier ainsi :
Private Sub UserForm_Initialize()
'ActiveSheet.Protect UserInterfaceOnly:=True
Dim isect
Dim k%, Dico, a, b, i, Ws
Dim iR%, iC%
Dim target As Range
If target.Count = 1 Then
Set isect = Application.Intersect(target, Range("Zone"))
If Not isect Is Nothing Then
iR = target.Row: iC = target.Column
ListBox1.Top = Cells(ActiveWindow.ScrollRow, 1).Top
ListBox1.Left = target.Left + 100
Set Ws = Sheets("Poste_Agent")
Set Dico = CreateObject("Scripting.Dictionary")
a = Ws.Range("Post1")
For i = LBound(a) To UBound(a)
If a(i, 1) <> "" Then Dico(a(i, 1)) = ""
Next i
b = Range(Cells(iR, 2), Cells(iR, Range("A1").CurrentRegion.Columns.Count))
On Error Resume Next
WsPostAg.Columns("c:c").ClearContents
For i = 1 To UBound(b, 2)
Dico.Remove (b(1, i))
Next
a = Dico.keys
For i = 1 To (UBound(a))
WsPrm.Cells(i + 1, 3) = a(i - 1)
Next
ListBox1.ColumnWidths = "140;30"
ListBox1.List() = [ListPost].Resize(, 1).Value
End If
End If
End SubCette modification avait pour but d'avoir une liste rafraichit à chaque utilisation du userform ( déincrémentation de la liste principale retranchée des valeurs déjà utilisées.)
Dans l'idée ma liste Post1 est la liste mère
la liste Poste 2 est la Liste recalculer et
la liste ListPost est alimentée par des valeurs fixe + la liste Post2 c'est cette liste qui alimente mon userform
un petit fichier joint, histoire de mieux comprendre comment j'ai torturé le code
Si vous avez une piste , des explications voir même une solution je suis preneur
Et merci à ceux qui prennent le temps de lire le message !
Bonjour,
dans ta procédure userform_initialize target n'a pas de valeur. dans le fichier que tu as fourni, la plage "zone" n'est pas définie. 2 raisons pour lesquelles tu reçois ce message d'erreur.
voici une manière de résoudre le problème de target
dans le code de thisworkbook
Private Sub Workbook_SheetBeforeRightClick(ByVal Sh As Object, ByVal target As Range, Cancel As Boolean)
Dim i%, k%, isect
If Sh.Name = "Planning" Then
If target.Count = 1 Then
i = [AList1].Count + 1
Set isect = Application.Intersect(target, [Planning])
If Not isect Is Nothing Then
Cancel = True
k = target.Column
ActiveWindow.ScrollColumn = IIf(k > 30, target.Column - 20, 3)
Set cible = target
usfCol.Show
End If
End If
End If
End Subdans le code de usfcol
Private Sub UserForm_Initialize()
'ActiveSheet.Protect UserInterfaceOnly:=True
Dim isect
Dim k%, Dico, a, b, i, Ws
Dim iR%, iC%
usfCol.Top = 80
usfCol.Left = 25 * (cible.Column + 1) 'IIf(k > 20, Target.Left + 540 - (Target.Column - 1) * (Target.Width), Target.Left + 80)
If cible.Count = 1 Then
Set isect = Application.Intersect(cible, Range("Zone"))
If Not isect Is Nothing Then
iR = cible.Row: iC = cible.Column
ListBox1.Top = Cells(ActiveWindow.ScrollRow, 1).Top
ListBox1.Left = cible.Left + 100
Set Ws = Sheets("Poste_Agent")
Set Dico = CreateObject("Scripting.Dictionary")
a = Ws.Range("Post1")
For i = LBound(a) To UBound(a)
If a(i, 1) <> "" Then Dico(a(i, 1)) = ""
Next i
b = Range(Cells(iR, 2), Cells(iR, Range("A1").CurrentRegion.Columns.Count))
On Error Resume Next
WsPrm.Columns("BW:BW").ClearContents
For i = 1 To UBound(b, 2)
Dico.Remove (b(1, i))
Next
a = Dico.keys
For i = 1 To (UBound(a))
WsPrm.Cells(i + 1, 4) = a(i - 1) 'Ecriture Post2
Next
ListBox1.ColumnWidths = "140;30"
ListBox1.List() = [ListPost].Resize(, 1).Value
End If
End If
End Subdans un nouveau module
Global ciblemes tests ont été bloqués par l'absence de définition pour la plage "zone"
Merci H2So4,
Effectivement Zone me pause problème il devrait définit dans le gestionnaire de nom comme suit :
=Planning!$F$2:$Z$372
Mon problème est que je n'arrive pas à définir Zone correctement car ma colonne finale varie régulièrement (présence de stagiaire, nouvel agent, etc...)
j'ai essyer de faire un Décaler comme j'avais réaliser pour ma zone [Planning]
=DECALER(Planning!$A$1;1;5;NBVAL(Planning!$A:$A)-1;NBVAL(Planning!$1:$1)-2)
Mes tests ne sont pas probant
Sinon je me demande si ce serait pas plus rapide de la définir en VBA directement
ColFin=Wsplan.Cells(1, Columns.Count-2).End(xlToLeft).Column
a=Split(Columns(ColFin).Address(ColumnAbsolute:=False), ":")(1)
Zone= Range("F2:"& a "&372")Qu'en penses tu?
Sinon j'ai appliqué ce que tu m'as dis j'ai juste remplacé Zone par planning mais il me semble que planning ne couvre pas bien ma plage de donnée
Sinon le userform apparaît
Mais j'ai perdu m'a liste, je penses que c'est en rapport avec cible = global
dans un nouveau module
CODE: TOUT SÉLECTIONNER
Global cible
??? je l'ai fait dans mon module Post_Ag mais j'ai pas compris pourquoi??
j'ai corrigé quelques petites erreurs dans mon code initiale lors de la réécriture des données qui doivent servir à alimenté ma liste PostList (la liste s'alimente bien dans mon onglet Post_Ag mais impossible de rapatrier la liste dans userform)
Bon et bien je valide car je n'est plus l'érreur 91 mais j'ai toujours pas réussit à ré affiché ma Liste sniff :
Bonjour,
pas facile de debugger ton application avec toutes les protections et suppressions de menu que tu as faites et nombreux bugs à gauche et à droite ...
j'ai trouvé 2 problèmes
1) dans usfcol tu déplaces ton listbox1 en dehors de la fenêtre visible.
2) ta plage Listpost ne contient rien
correction pour problème 1
Private Sub UserForm_Initialize()
'ActiveSheet.Protect UserInterfaceOnly:=True
Dim isect
Dim k%, Dico, a, b, i, Ws, c
Dim iR%, iC%
usfCol.Top = 80
usfCol.Left = 25 * (cible.Column + 1) 'IIf(k > 20, Target.Left + 540 - (Target.Column - 1) * (Target.Width), Target.Left + 80)
If cible.Count = 1 Then
Set isect = Application.Intersect(cible, [Planning ])
If Not isect Is Nothing Then
iR = cible.Row: iC = cible.Column
'ListBox1.Top = Cells(ActiveWindow.ScrollRow, 1).Top
'ListBox1.Left = cible.Left + 100
Set Ws = Sheets("Poste_Agent")
Set Dico = CreateObject("Scripting.Dictionary")
a = Ws.Range("Post1")
For i = LBound(a) To UBound(a)
If a(i, 1) <> "" Then Dico(a(i, 1)) = ""
Next i
b = Range(Cells(iR, 2), Cells(iR, Range("A1").CurrentRegion.Columns.Count))
On Error Resume Next
Ws.Columns("D2:D200").ClearContents
For i = 1 To UBound(b, 2)
Dico.Remove (b(1, i))
Next
a = Dico.keys
For i = 1 To (UBound(a))
WsPostAg.Cells(i + 1, 4) = a(i - 1) 'Ecriture Post2
Next
ListBox1.ColumnWidths = "140;30"
ListBox1.List = Range("listpost") '[ListPost].Resize(, 1).Value
End If
End If
End SubFinalement j'ai trouver la réponse, ( en voulant faire un fichier propre pour poster un nouveau message ! )
alors dans thisworkbook
Private Sub Workbook_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
Dim i%, k%, isect
On Error Resume Next
If Sh.Name = "Planning" Then
If Target.Count = 1 Then
i = [AList1].Count + 1
Set isect = Application.Intersect(Target, [Planning])
If Not isect Is Nothing Then
Cancel = True
k = Target.Column
ActiveWindow.ScrollColumn = IIf(k > 30, Target.Column - 20, 3)
usfCol.Top = 80
usfCol.Left = 25 * (Target.Column + 1) 'IIf(k > 20, Target.Left + 540 - (Target.Column - 1) * (Target.Width), Target.Left + 80)
usfCol.Show
End If
End If
End If
End Subdans l'onglet planning ' je recalcules ma liste
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim isect
Dim k%, Dico, a, b, i, Ws
If Target.Count = 1 Then
Set isect = Application.Intersect(Target, Range("Planning"))
If Not isect Is Nothing Then
iR = Target.Row: iC = Target.Column
' ListBox1.Top = Cells(ActiveWindow.ScrollRow, 1).Top
' ListBox1.Left = Target.Left + 100
Set Ws = Sheets("Poste_Agent")
Set Dico = CreateObject("Scripting.Dictionary")
a = WsPostAg.Range("Post1")
For i = LBound(a) To UBound(a)
If a(i, 1) <> "" Then Dico(a(i, 1)) = ""
Next i
b = Range(Cells(iR, 2), Cells(iR, Range("A1").CurrentRegion.Columns.Count))
On Error Resume Next
WsPostAg.Range("D9:D200").ClearContents
For i = 1 To UBound(b, 2)
Dico.Remove (b(1, i))
Next
a = Dico.keys
For i = 1 To (UBound(a))
WsPostAg.Cells(i + 8, 4) = a(i - 1)
Next
End If
End If
End Subet dans mon userForm j'appelle simplement ma liste
Private Sub UserForm_Initialize()
'ActiveSheet.Protect UserInterfaceOnly:=True
ListBox1.ColumnWidths = "140;30"
ListBox1.List() = [ListPost].Resize(, 1).Value
End SubEn tout cas un grand Merci pour toutes tes réponses qui m'ont fortement mise sur la voie!
il me reste encore un problème sur le quelle je but, il faudrait que dans mon gestionnanire de nom je declares la zone exacte ou l'userforme s'initialise,
Actuellement j'appelle mon range "planning" mais il me permet d'initialiser l'userform entre les ligne 2 est 372 sans limite de colonne,
il faudrait que je puisse définir juste la zone entre "F2 et ??372 " en sachant que ?? est variable au cours de la vie du fichier, je penses qu'il y a quelques chose à faire avec A Decaler mais j'ai pas encore trouvé la solution! si tu as une piste pour faire ca je suis preneur car je bug la dessus depuis presque 6 mois
Bonsoir,
J'ai pas trop planché sur ton bazard mébon...
P'tre que quelque chose comme ça pourrait le faire :
Sub galopin()
Dim i%, rng$, Zone As Range
With WsPlan
i% = .[A1].End(xlToRight).Column - 2
rng = .Range(.Cells(2, 6), .Cells(372, i)).Address
Set Zone = .Range(rng)
MsgBox Zone.Address
End With
End SubBon là j'ai tourné un peu autour du pot :
Tu pourras soit l'affecter directement à ton USF, soit en faire une fonction String soit créer un NomDéfini dans le gestionnaire avec périodiquement...
Si tu n'en as pas besoin ailleurs je pencherai plutôt pour une affectation directe au USF sinon crée une Public Function
A+
Bonsoir galopin
Je test ca dans la soiree je vais voir ci ca passe ds le thisworkbook
Merci
Si tu veux le mettre dans le Gestionnaire, il faudra utiliser le nom de Feuille et NON le CodeName :
ThisWorkbook.Names("Zone").RefersTo = "=Planning!" & rngA+
Hello galopin c'est impeccable par contre j'ai pas utilisé le
ThisWorkbook.Names("Zone").RefersTo = "=Planning!" & rng
j'ai juste modifié
Set Zone = WsPlan.Range(rng) bon en fait je viens de voir que j'avais meme pas besoin de faire la modif vu que c'était pour le gestionnanire de nom!!
et ça fonctionne nickel
plus qu'un module à rédiger et normalement j'ai fini!
Encore merci pour t'as bienveillance, je sens que je vais appeler mon fichier Galoptime !