Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Autofilter in a userform
Hi, I'm tryng to make a userform whe
A B C D E 1 sIMON FLETE 1/4 2 SIMON ILDIO 1/2 3 SIMON FLETE 1 4 5 LEON FSTR 1/4 6 LEON FSFR 1/4 appears a kind of autofilter, I mean, if I select in combobox1 the SIMON option then in combobox 2 appears just FLETE and ILDIO, if FLETE is selected then appears in combobox3 1/4 and 1, and so on Thanks in advance ANY HELP WILL BE USEFULL |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Autofilter in a userform
Hi Filo666,
See xlDynamic's page on Dependendent Dropdowns: http://www.xldynamic.com/source/xld.Dropdowns.html --- Regards, Norman "filo666" wrote in message ... Hi, I'm tryng to make a userform whe A B C D E 1 sIMON FLETE 1/4 2 SIMON ILDIO 1/2 3 SIMON FLETE 1 4 5 LEON FSTR 1/4 6 LEON FSFR 1/4 appears a kind of autofilter, I mean, if I select in combobox1 the SIMON option then in combobox 2 appears just FLETE and ILDIO, if FLETE is selected then appears in combobox3 1/4 and 1, and so on Thanks in advance ANY HELP WILL BE USEFULL |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Autofilter in a userform
Here is some code. Just add it to the userform.
Option Explicit Private Sub ComboBox1_Click() PopulateCombobox2 End Sub Private Sub ComboBox2_Click() PopulateCombobox3 End Sub Private Sub UserForm_Initialize() With ComboBox1 .AddItem "Simon" .AddItem "Leon" .ListIndex = 0 End With End Sub Private Sub PopulateCombobox2() If ComboBox1.ListIndex = -1 Then Exit Sub End If With ComboBox2 .Clear If ComboBox1 = "Simon" Then .AddItem "Flete" .AddItem "Ildio" ElseIf ComboBox1 = "Leon" Then .AddItem "FSTR" End If .ListIndex = 0 End With End Sub Private Sub PopulateCombobox3() If ComboBox2.ListIndex = -1 Then Exit Sub End If With ComboBox3 .Clear If ComboBox2 = "Flete" Then .AddItem "1/4" .AddItem "1" ElseIf ComboBox2 = "Ildio" Then .AddItem "1/2" ElseIf ComboBox2 = "FSTR" Then .AddItem "1/4" End If .ListIndex = 0 End With End Sub Alok "filo666" wrote: Hi, I'm tryng to make a userform whe A B C D E 1 sIMON FLETE 1/4 2 SIMON ILDIO 1/2 3 SIMON FLETE 1 4 5 LEON FSTR 1/4 6 LEON FSFR 1/4 appears a kind of autofilter, I mean, if I select in combobox1 the SIMON option then in combobox 2 appears just FLETE and ILDIO, if FLETE is selected then appears in combobox3 1/4 and 1, and so on Thanks in advance ANY HELP WILL BE USEFULL |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Autofilter in a userform
thankd norman, I'll chek the internet page and I'll answer you
Alok: I have 2000 rows and 3 columns, could you tell me with this conditions pleaseĀ” "Norman Jones" wrote: Hi Filo666, See xlDynamic's page on Dependendent Dropdowns: http://www.xldynamic.com/source/xld.Dropdowns.html --- Regards, Norman "filo666" wrote in message ... Hi, I'm tryng to make a userform whe A B C D E 1 sIMON FLETE 1/4 2 SIMON ILDIO 1/2 3 SIMON FLETE 1 4 5 LEON FSTR 1/4 6 LEON FSFR 1/4 appears a kind of autofilter, I mean, if I select in combobox1 the SIMON option then in combobox 2 appears just FLETE and ILDIO, if FLETE is selected then appears in combobox3 1/4 and 1, and so on Thanks in advance ANY HELP WILL BE USEFULL |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Autofilter in a userform
Norman: What I need it's a little bit more complex, the values of combobox1
are in the "ActiveSheet.Range("A3", Range("A65535").End(xlUp)).Cells.Value" (means from a3 to the last value of the sheet); combobox2 "ActiveSheet.Range("B3", Range("B65535").End(xlUp)).Cells.Value" and combobox3 ActiveSheet.Range("C3", Range("C65535").End(xlUp)).Cells.Value" I start making a macro to get the values of the column A (I mean, as in column A there are a lot of values aprx 2000 and among this 2000 there are a lot of values that are equals, as well as the "" values) and it works fine, this macro could be used to obtain values to combobox 2 and 3, but, HOW TO MAKE THE RALITIONS AMONG THE TREE OF THEM????? TKS "Norman Jones" wrote: Hi Filo666, See xlDynamic's page on Dependendent Dropdowns: http://www.xldynamic.com/source/xld.Dropdowns.html --- Regards, Norman "filo666" wrote in message ... Hi, I'm tryng to make a userform whe A B C D E 1 sIMON FLETE 1/4 2 SIMON ILDIO 1/2 3 SIMON FLETE 1 4 5 LEON FSTR 1/4 6 LEON FSFR 1/4 appears a kind of autofilter, I mean, if I select in combobox1 the SIMON option then in combobox 2 appears just FLETE and ILDIO, if FLETE is selected then appears in combobox3 1/4 and 1, and so on Thanks in advance ANY HELP WILL BE USEFULL |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Autofilter in a userform
Hi try the following.
Past the following in the Form's code module Option Explicit Dim col As New Collection Dim cola As New Collection Dim colb As New Collection Dim colc As New Collection Private Sub ComboBox1_Click() PopulateCombobox2 End Sub Private Sub ComboBox2_Click() PopulateCombobox3 End Sub Private Sub UserForm_Initialize() Dim i% PopulateCollections With ComboBox1 For i = 1 To cola.Count .AddItem cola.Item(i) Next i .ListIndex = 0 End With End Sub Private Sub PopulateCombobox2() Dim i% Dim oC As Class1 If ComboBox1.ListIndex = -1 Then Exit Sub End If Set colb = New Collection For i = 1 To col.Count Set oC = col.Item(i) If oC.a = ComboBox1 Then On Error Resume Next colb.Add Item:=oC.b, Key:=oC.b On Error GoTo 0 End If Next i With ComboBox2 .Clear For i = 1 To colb.Count .AddItem colb.Item(i) Next i If .ListCount 0 Then .ListIndex = 0 End If End With End Sub Private Sub PopulateCombobox3() Dim i% Dim oC As Class1 If ComboBox1.ListIndex = -1 Or ComboBox2.ListIndex = -1 Then Exit Sub End If Set colc = New Collection For i = 1 To col.Count Set oC = col.Item(i) If oC.a = ComboBox1 And oC.b = ComboBox2 Then On Error Resume Next colc.Add Item:=oC.c, Key:=oC.c On Error GoTo 0 End If Next i With ComboBox3 .Clear For i = 1 To colc.Count .AddItem colc.Item(i) Next i If .ListCount 0 Then .ListIndex = 0 End If End With End Sub Private Sub PopulateCollections() Dim sKey$ Dim r& Dim oC As Class1 Dim a$, b$, c$ With Sheet1 For r = 1 To .UsedRange.Rows.Count a = .Cells(r, 1).Value b = .Cells(r, 2).Value c = .Cells(r, 3).Value If a < "" And b < "" And c < "" Then On Error Resume Next Set oC = New Class1 oC.a = a oC.b = b oC.c = c col.Add Item:=oC, Key:=a & b & c cola.Add Item:=a, Key:=a On Error GoTo 0 End If Next r End With End Sub '============================================= Post the following in a Class module called Class1 Option Explicit Public a As String Public b As String Public c As String '========================================= Alok "filo666" wrote: Norman: What I need it's a little bit more complex, the values of combobox1 are in the "ActiveSheet.Range("A3", Range("A65535").End(xlUp)).Cells.Value" (means from a3 to the last value of the sheet); combobox2 "ActiveSheet.Range("B3", Range("B65535").End(xlUp)).Cells.Value" and combobox3 ActiveSheet.Range("C3", Range("C65535").End(xlUp)).Cells.Value" I start making a macro to get the values of the column A (I mean, as in column A there are a lot of values aprx 2000 and among this 2000 there are a lot of values that are equals, as well as the "" values) and it works fine, this macro could be used to obtain values to combobox 2 and 3, but, HOW TO MAKE THE RALITIONS AMONG THE TREE OF THEM????? TKS "Norman Jones" wrote: Hi Filo666, See xlDynamic's page on Dependendent Dropdowns: http://www.xldynamic.com/source/xld.Dropdowns.html --- Regards, Norman "filo666" wrote in message ... Hi, I'm tryng to make a userform whe A B C D E 1 sIMON FLETE 1/4 2 SIMON ILDIO 1/2 3 SIMON FLETE 1 4 5 LEON FSTR 1/4 6 LEON FSFR 1/4 appears a kind of autofilter, I mean, if I select in combobox1 the SIMON option then in combobox 2 appears just FLETE and ILDIO, if FLETE is selected then appears in combobox3 1/4 and 1, and so on Thanks in advance ANY HELP WILL BE USEFULL |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
excel 2007 autofilter change to 2003 autofilter functionality? | Excel Discussion (Misc queries) | |||
2007 excel autofilter change back to 2003 autofilter? | Excel Discussion (Misc queries) | |||
Printing from Userform with AutoFilter | Excel Discussion (Misc queries) | |||
AutoFilter & UserForm | Excel Programming | |||
How to use UserForm DTPicker.Value as Autofilter Crieteria | Excel Programming |