PDA

View Full Version : Solved: Conditional Range without autofilter?



lifeson
03-01-2007, 11:45 AM
Hi all
Is it posssible to create a range to use as a rowsource for a combobox without using the autofilter :dunno

Eg
Where column 1 = type GF
Then the range is all rows for column 2 and 3 where column 1 = GF

I have attached an example sheet which hopefully demonstrates the problem.

Thanks in advance

Ian

Bob Phillips
03-01-2007, 12:54 PM
Private Sub ComboBox1_Rowsource(idx As String)
Dim iLastRow As Long
Dim i As Long
Dim iItem As Long
Dim aryItems

iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
ReDim aryItems(1 To iLastRow)
For i = 2 To iLastRow
If Cells(i, "A").Value = idx Then
iItem = iItem + 1
aryItems(iItem) = Cells(i, "B").Value
End If
Next i
ReDim Preserve aryItems(1 To iItem)
ComboBox1.Clear
ComboBox1.List = aryItems

End Sub


Private Sub OptionButton1_Click()
' run macro to determine whicjh range is used for combobox1 rowsource
Call ComboBox1_Rowsource("GF")
End Sub

Private Sub OptionButton2_Click()
' run macro to determine whicjh range is used for combobox1 rowsource
Call ComboBox1_Rowsource("IF")

End Sub

lifeson
03-01-2007, 01:26 PM
XLD
Thanks for that it works great. :clap:
Now I need to have a look to see whta you have done

Why is Dim aryItems not followed by anything? eg. dim aryitems as ...

What does idx signify?

Bob Phillips
03-02-2007, 03:41 AM
Because I have left it to default to variant. It wouyld have been better for me to use


Dim aryItems As Variant


idx is just an argument within the procedure, so that I could pass the value to test for rather than having to rely on the procedure to test which button was pressed (you already know that because you have code in their events). This is better practice per se, but also allows you to add more buttons without changing that procedure.

lifeson
03-02-2007, 07:29 AM
idx is just an argument within the procedure, so that I could pass the value to test for rather than having to rely on the procedure to test which button was pressed (you already know that because you have code in their events). This is better practice per se, but also allows you to add more buttons without changing that procedure.

XLD thanks for the explanation
Does that mean I can use the same code and just declare the rowsource using the type ID for the other combo boxes?

I have tried
Private Sub Rowsource(idx As String)
Dim iLastRow As Long
Dim i As Long
Dim iItem As Long
Dim aryItems

iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
ReDim aryItems(1 To iLastRow)
For i = 2 To iLastRow
If Cells(i, "A").Value = idx Then
iItem = iItem + 1
aryItems(iItem) = Cells(i, "B").Value
End If
Next i
ReDim Preserve aryItems(1 To iItem)
ComboBox1.Clear
ComboBox1.List = aryItems

End Sub

Private Sub ComboBox2_DropButtonClick()
Call Rowsource("R")
End Sub
Private Sub OptionButton1_Click()
Call Rowsource("GF")
End Sub

Private Sub OptionButton2_Click()

Call Rowsource("IF")

End Sub

using

Private Sub ComboBox2_DropButtonClick()
Call Rowsource("R")
End Sub
To try and populate combobox 2 but no joy.
I guess it something to do with:
ComboBox1.Clear
ComboBox1.List = aryItems
But when I emove these and add them to the combo box 2 event it all goes wrong :think:

Bob Phillips
03-02-2007, 08:23 AM
Ha ha! I knew you were going to want to do that. I did thinks about including the capability in my first answer, but didn't for some reason.

The problem is that the loading routine specifically loads combobox1. If you change it to 2, then it doesn't work as you want.

You can get what you want, but just a bit more work is required, you have to pass the combobox object to the populating routine



Private Sub Rowsource(ByVal idx As String, ByRef combo As msforms.ComboBox)
Dim iLastRow As Long
Dim i As Long
Dim iItem As Long
Dim aryItems

iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
ReDim aryItems(1 To iLastRow)
For i = 2 To iLastRow
If Cells(i, "A").Value = idx Then
iItem = iItem + 1
aryItems(iItem) = Cells(i, "B").Value
End If
Next i
ReDim Preserve aryItems(1 To iItem)
combo.Clear
combo.List = aryItems

End Sub

Private Sub ComboBox2_DropButtonClick()
Call Rowsource("R", Me.ComboBox2)
End Sub
Private Sub OptionButton1_Click()
Call Rowsource("GF", Me.ComboBox1)
End Sub

Private Sub OptionButton2_Click()
Call Rowsource("IF", Me.ComboBox1)
End Sub

lifeson
03-02-2007, 08:32 AM
:clap: :clap: :clap:
You dont know how long I have been looking for the answer to this

:beerchug:

lifeson
03-02-2007, 09:07 AM
Private Sub ComboBox2_DropButtonClick()
Call Rowsource("R", Me.ComboBox2)
End SubXLD
Slight problem
Although I can get combobox2 to show the correct list of values in the drop down, the combo is not retaining the selection
Any advice/clues?

Bob Phillips
03-02-2007, 11:03 AM
What exactly do you mean by not reatining it? After you select it? If it is this that is because you are using the dropdown click event to populate that combo, so every time you click it, it re-populates. IF there is more that one possible list for combo2, have option buttons like combo1, if there is only one, call the poulate routine in Userform_Initialize.

lifeson
03-02-2007, 12:11 PM
Once again thank you
I needed to call the populate rowsurce routine when the form initalized

:thumb