Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
dynamically created listbox
Hi,
I have a problem using events with a listbox. I have a first listbox, named "listbox1", and I want to display a second listbox "listbox3" when the user selects a value of the first listbox. I thought I could use the change or afterupdate events, placed in the userform module, but nothing happends when an item of the first listbox is selected. Does it have something to do with the fact that both of my listboxes are created dynamically? Here is my code: 'creation of listbox1 Set champ = UserFormCreerRequete.MultiPage1.ongletselection.Co ntrols.Add("Forms.ListBox.1", "ListBox" & nb) champ.Visible = False ...... 'display nomchamp = UserFormCreerRequete.MultiPage1.ongletselection.Co ntrols.Item(nb - 4).Name For i = 0 To UserFormCreerRequete.ListBoxChamps.ListCount - 1 If UserFormCreerRequete.ListBoxChamps.Selected(i) = True Then UserFormCreerRequete.MultiPage1.ongletselection.Co ntrols.Item(nomchamp).AddItem UserFormCreerRequete.ListBoxChamps.List(i) End If Next i With UserFormCreerRequete.MultiPage1.ongletselection.Co ntrols.Item(nomchamp) .Height = 16 .Left = leftpremieroptionbouton + 70 .Top = toppremieroptionbouton '+... .Width = 90 .Visible = True End With the change event: Private Sub ListBox1_Change() For i = 0 To UserFormCreerRequete.MultiPage1.ongletselection.Co ntrols.Item(nomchamp).ListCount - 1 If UserFormCreerRequete.MultiPage1.ongletselection.Co ntrols.Item(nomchamp).Selected(i) = True Then 'on a la colonneselectionnee Set recherche = Cells.Find(What:=UserFormCreerRequete.MultiPage1.o ngletselection.Controls.Item(nomchamp).Selected(i) , After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False) colonneSelectionnee = recherche.Column feuilleSelectionnnee = recherche.Sheet lastline = ThisWorkbook.Sheets(feuilleSelectionnnee).Cells(65 536, colonneSelectionnee).End(xlUp).Row For j = 2 To lastline UserFormCreerRequete.MultiPage1.ongletselection.Co ntrols.Item(nomvaleur).AddItem Cells(j, colonneSelectionnee) Next j End If Next i nomvaleur = UserFormCreerRequete.MultiPage1.ongletselection.Co ntrols.Item(nb - 2).Name With UserFormCreerRequete.MultiPage1.ongletselection.Co ntrols.Item(nomvaleur) .Height = 16 .Left = leftpremieroptionbouton + 225 .Top = toppremieroptionbouton '+... .Width = 90 .Visible = True End With End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
dynamically created listbox
since you don't name the listbox Listbox1 and the change event is for
Listbox1, then there would be no link between the added listbox and the event. Dim champ as MsForms.Listbox Set champ = UserFormCreerRequete.MultiPage1.ongletselection.Co ntrols.Add("Forms.ListBox.1", "ListBox" & nb) champ.Visible = False champ.Name = "ListBox1" -- Regards, Tom Ogilvy "clem" wrote: Hi, I have a problem using events with a listbox. I have a first listbox, named "listbox1", and I want to display a second listbox "listbox3" when the user selects a value of the first listbox. I thought I could use the change or afterupdate events, placed in the userform module, but nothing happends when an item of the first listbox is selected. Does it have something to do with the fact that both of my listboxes are created dynamically? Here is my code: 'creation of listbox1 Set champ = UserFormCreerRequete.MultiPage1.ongletselection.Co ntrols.Add("Forms.ListBox.1", "ListBox" & nb) champ.Visible = False ..... 'display nomchamp = UserFormCreerRequete.MultiPage1.ongletselection.Co ntrols.Item(nb - 4).Name For i = 0 To UserFormCreerRequete.ListBoxChamps.ListCount - 1 If UserFormCreerRequete.ListBoxChamps.Selected(i) = True Then UserFormCreerRequete.MultiPage1.ongletselection.Co ntrols.Item(nomchamp).AddItem UserFormCreerRequete.ListBoxChamps.List(i) End If Next i With UserFormCreerRequete.MultiPage1.ongletselection.Co ntrols.Item(nomchamp) .Height = 16 .Left = leftpremieroptionbouton + 70 .Top = toppremieroptionbouton '+... .Width = 90 .Visible = True End With the change event: Private Sub ListBox1_Change() For i = 0 To UserFormCreerRequete.MultiPage1.ongletselection.Co ntrols.Item(nomchamp).ListCount - 1 If UserFormCreerRequete.MultiPage1.ongletselection.Co ntrols.Item(nomchamp).Selected(i) = True Then 'on a la colonneselectionnee Set recherche = Cells.Find(What:=UserFormCreerRequete.MultiPage1.o ngletselection.Controls.Item(nomchamp).Selected(i) , After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False) colonneSelectionnee = recherche.Column feuilleSelectionnnee = recherche.Sheet lastline = ThisWorkbook.Sheets(feuilleSelectionnnee).Cells(65 536, colonneSelectionnee).End(xlUp).Row For j = 2 To lastline UserFormCreerRequete.MultiPage1.ongletselection.Co ntrols.Item(nomvaleur).AddItem Cells(j, colonneSelectionnee) Next j End If Next i nomvaleur = UserFormCreerRequete.MultiPage1.ongletselection.Co ntrols.Item(nb - 2).Name With UserFormCreerRequete.MultiPage1.ongletselection.Co ntrols.Item(nomvaleur) .Height = 16 .Left = leftpremieroptionbouton + 225 .Top = toppremieroptionbouton '+... .Width = 90 .Visible = True End With End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
dynamically created listbox
I thought
UserFormCreerRequete.MultiPage1.ongletselection.Co ntrols.Add("Forms.ListBox.1", "ListBox" & nb) was doing it (since at that step nb=1). I checked with spies, and there exist a "ListBox1" in the controls. Adding champ.Name = "ListBox1" doesn't change the result. "Tom Ogilvy" wrote: since you don't name the listbox Listbox1 and the change event is for Listbox1, then there would be no link between the added listbox and the event. Dim champ as MsForms.Listbox Set champ = UserFormCreerRequete.MultiPage1.ongletselection.Co ntrols.Add("Forms.ListBox.1", "ListBox" & nb) champ.Visible = False champ.Name = "ListBox1" -- Regards, Tom Ogilvy "clem" wrote: Hi, I have a problem using events with a listbox. I have a first listbox, named "listbox1", and I want to display a second listbox "listbox3" when the user selects a value of the first listbox. I thought I could use the change or afterupdate events, placed in the userform module, but nothing happends when an item of the first listbox is selected. Does it have something to do with the fact that both of my listboxes are created dynamically? Here is my code: 'creation of listbox1 Set champ = UserFormCreerRequete.MultiPage1.ongletselection.Co ntrols.Add("Forms.ListBox.1", "ListBox" & nb) champ.Visible = False ..... 'display nomchamp = UserFormCreerRequete.MultiPage1.ongletselection.Co ntrols.Item(nb - 4).Name For i = 0 To UserFormCreerRequete.ListBoxChamps.ListCount - 1 If UserFormCreerRequete.ListBoxChamps.Selected(i) = True Then UserFormCreerRequete.MultiPage1.ongletselection.Co ntrols.Item(nomchamp).AddItem UserFormCreerRequete.ListBoxChamps.List(i) End If Next i With UserFormCreerRequete.MultiPage1.ongletselection.Co ntrols.Item(nomchamp) .Height = 16 .Left = leftpremieroptionbouton + 70 .Top = toppremieroptionbouton '+... .Width = 90 .Visible = True End With the change event: Private Sub ListBox1_Change() For i = 0 To UserFormCreerRequete.MultiPage1.ongletselection.Co ntrols.Item(nomchamp).ListCount - 1 If UserFormCreerRequete.MultiPage1.ongletselection.Co ntrols.Item(nomchamp).Selected(i) = True Then 'on a la colonneselectionnee Set recherche = Cells.Find(What:=UserFormCreerRequete.MultiPage1.o ngletselection.Controls.Item(nomchamp).Selected(i) , After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False) colonneSelectionnee = recherche.Column feuilleSelectionnnee = recherche.Sheet lastline = ThisWorkbook.Sheets(feuilleSelectionnnee).Cells(65 536, colonneSelectionnee).End(xlUp).Row For j = 2 To lastline UserFormCreerRequete.MultiPage1.ongletselection.Co ntrols.Item(nomvaleur).AddItem Cells(j, colonneSelectionnee) Next j End If Next i nomvaleur = UserFormCreerRequete.MultiPage1.ongletselection.Co ntrols.Item(nb - 2).Name With UserFormCreerRequete.MultiPage1.ongletselection.Co ntrols.Item(nomvaleur) .Height = 16 .Left = leftpremieroptionbouton + 225 .Top = toppremieroptionbouton '+... .Width = 90 .Visible = True End With End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
dynamically created listbox
There was nothing in the code you showd to indicate that nb = 1 at that step
Why are you building the controls dynamically? Seems like you are inviting problems like you are having. Put the controls in at design time and hide or unhide them as needed. -- Regards, Tom Ogilvy "clem" wrote: I thought UserFormCreerRequete.MultiPage1.ongletselection.Co ntrols.Add("Forms.ListBox.1", "ListBox" & nb) was doing it (since at that step nb=1). I checked with spies, and there exist a "ListBox1" in the controls. Adding champ.Name = "ListBox1" doesn't change the result. "Tom Ogilvy" wrote: since you don't name the listbox Listbox1 and the change event is for Listbox1, then there would be no link between the added listbox and the event. Dim champ as MsForms.Listbox Set champ = UserFormCreerRequete.MultiPage1.ongletselection.Co ntrols.Add("Forms.ListBox.1", "ListBox" & nb) champ.Visible = False champ.Name = "ListBox1" -- Regards, Tom Ogilvy "clem" wrote: Hi, I have a problem using events with a listbox. I have a first listbox, named "listbox1", and I want to display a second listbox "listbox3" when the user selects a value of the first listbox. I thought I could use the change or afterupdate events, placed in the userform module, but nothing happends when an item of the first listbox is selected. Does it have something to do with the fact that both of my listboxes are created dynamically? Here is my code: 'creation of listbox1 Set champ = UserFormCreerRequete.MultiPage1.ongletselection.Co ntrols.Add("Forms.ListBox.1", "ListBox" & nb) champ.Visible = False ..... 'display nomchamp = UserFormCreerRequete.MultiPage1.ongletselection.Co ntrols.Item(nb - 4).Name For i = 0 To UserFormCreerRequete.ListBoxChamps.ListCount - 1 If UserFormCreerRequete.ListBoxChamps.Selected(i) = True Then UserFormCreerRequete.MultiPage1.ongletselection.Co ntrols.Item(nomchamp).AddItem UserFormCreerRequete.ListBoxChamps.List(i) End If Next i With UserFormCreerRequete.MultiPage1.ongletselection.Co ntrols.Item(nomchamp) .Height = 16 .Left = leftpremieroptionbouton + 70 .Top = toppremieroptionbouton '+... .Width = 90 .Visible = True End With the change event: Private Sub ListBox1_Change() For i = 0 To UserFormCreerRequete.MultiPage1.ongletselection.Co ntrols.Item(nomchamp).ListCount - 1 If UserFormCreerRequete.MultiPage1.ongletselection.Co ntrols.Item(nomchamp).Selected(i) = True Then 'on a la colonneselectionnee Set recherche = Cells.Find(What:=UserFormCreerRequete.MultiPage1.o ngletselection.Controls.Item(nomchamp).Selected(i) , After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False) colonneSelectionnee = recherche.Column feuilleSelectionnnee = recherche.Sheet lastline = ThisWorkbook.Sheets(feuilleSelectionnnee).Cells(65 536, colonneSelectionnee).End(xlUp).Row For j = 2 To lastline UserFormCreerRequete.MultiPage1.ongletselection.Co ntrols.Item(nomvaleur).AddItem Cells(j, colonneSelectionnee) Next j End If Next i nomvaleur = UserFormCreerRequete.MultiPage1.ongletselection.Co ntrols.Item(nb - 2).Name With UserFormCreerRequete.MultiPage1.ongletselection.Co ntrols.Item(nomvaleur) .Height = 16 .Left = leftpremieroptionbouton + 225 .Top = toppremieroptionbouton '+... .Width = 90 .Visible = True End With End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Assign click functionality to dynamically created textboxes? | Excel Programming | |||
Scaling a chart that has been dynamically created in vb | Charts and Charting in Excel | |||
Back color of dynamically-created buttons | Excel Programming | |||
Assign macros to dynamically created form elements | Excel Programming |