Marking items in listbox as TRUe based on Combobox - sorta lookup..

[Welshbird]

I have a VBA userform which has two comboboxes and a list box.

What I'm aiming for is that the user can 'shortcut' to the items required in the list box based on the selections made in the combos.

I'm talking about a hierachical geography here, so my first combobox lets me select from a list of 7 items, and based on this the second combo box populates with a list of anything between 1 and 10 items.

That bit works so far (bless the search option in this forum!)

I'm guessing I need to have some kind of loopy thing going through some arrays to set specific items in the list box to "TRUE".

(I want always to display the complete list in the listbox, which I hope makes it easier.)

So, I have the following lookup range in another (hidden) sheet of the same workbook.
[tt]
Reg1 Reg2 Area
LO LO1 A1
LO LO1 A2
LO LO2 A3
etc[/tt]
and the code for my comboboxes:

Private Sub RegCombo1_Change()
Dim RadCount As Integer
'Populate the RAD combo box based on the selection from the 'REG' combo box (first two letters)
Select Case RegCombo1.Value
    Case Is = "LO": RadCombo1.RowSource = "Validation!B3:B8"    '6 items
    Case Is = "ME": RadCombo1.RowSource = "Validation!B9:B18"   '10 items
    Case Is = "NI": RadCombo1.RowSource = "Validation!B19:B19"  '1 item
    Case Is = "NR": RadCombo1.RowSource = "Validation!B20:B29"  '10 items
    Case Is = "SC": RadCombo1.RowSource = "Validation!B30:B31"  '2 items
    Case Is = "SO": RadCombo1.RowSource = "Validation!B32:B38"  '7 items
    Case Is = "WA": RadCombo1.RowSource = "Validation!B39:B40"  '2 items (Wales!)
End Select
'then build some loopy arrays to make appropriate PCO's as TRUE based on selections above
End Sub

Its that loopy array bit that is confusing me. If anyone can point me towards some help with that I can possibly work out how to make those items as True innit.

Ta chaps.

Fee

"The cure for anything is salt water – sweat, tears, or the sea." Isak Dinesen

 

[Welshbird]

OK - Have tried on me own here, but am clearly missing something major:

Private Sub RegCombo1_Change()
Dim RadCount As Integer
'Populate the RAD combo box based on the selection from the 'REG' combo box (first two letters)
Select Case RegCombo1.Value
    Case Is = "LO": RadCombo1.RowSource = "Validation!B3:B8"    '6 items
    Case Is = "ME": RadCombo1.RowSource = "Validation!B9:B18"   '10 items
    Case Is = "NI": RadCombo1.RowSource = "Validation!B19:B19"  '1 item
    Case Is = "NR": RadCombo1.RowSource = "Validation!B20:B29"  '10 items
    Case Is = "SC": RadCombo1.RowSource = "Validation!B30:B31"  '2 items
    Case Is = "SO": RadCombo1.RowSource = "Validation!B32:B38"  '7 items
    Case Is = "WA": RadCombo1.RowSource = "Validation!B39:B40"  '2 items (Wales!)
End Select
'then build some loopy arrays to make appropriate PCO's as TRUE based on selections above
Dim Ar As String
Dim PCOArray(Ar) As String
Dim counter As Integer
Ar = Application.WorksheetFunction.CountA(RadCombo1.RowSource) + 1
For counter = 1 To Ar
PCOArray(Ar) = RadCombo1.RowSource.Offset(rowoffset:=0, columnoffset:=1)
If PCOList1(r) = PCOArray(Ar) Then PCOList1(r) = "True"
Next counter
End Sub

Nothing happens to my list..... what obvious thing am I missing then??

Fee

"The cure for anything is salt water – sweat, tears, or the sea." Isak Dinesen

 

[Welshbird]

Please ignore that last bit of code. Looking it up (with offset) where it actaully exists would be a good start!

Tsk...

Fee

"The cure for anything is salt water – sweat, tears, or the sea." Isak Dinesen

 

[Welshbird]

Me again. trying to work through this on my own. Toothless budgie right now (not really succeeding.... )

OK - I think this must be a closer idea: I now have a command button to 'autoselect' the third level of geography:

Private Sub PCOButton_Click()
Dim RAD As String
RAD = RadCombo1.Value
Dim P As Integer
P = 4
Dim PCOArray(4) As String
For P = 1 To 4
[purple]PCOArray(P) = Application.WorksheetFunction.Match(RAD, Worksheets("Geography").Range("RngGeog"), P)[/purple]
MsgBox "PCO's are :" & PCOArray(P)
Next P
Unload Me
End Sub

but the purple line is giving me a runtime error 1004 - unable to get the match property of the worksheetfunction class.

ARG! And indeed... TSK!

Fee

"The cure for anything is salt water – sweat, tears, or the sea." Isak Dinesen

 

[Welshbird]

Right. Beginning the winning! I have some test code that gives me the correct 3rd level of geography in an array! (Am posting this as I solve it just in case it helps someone else in the future. Hope no-one minds. Feel free to chip-in though!)

Private Sub PCOButton_Click()
Dim RAD As String
Dim RADRange As Range
Dim LkpRange As Range
Dim PCOArray() As String
Set RADRange = Range("Geography!B2:B184")
Set LkpRange = Range("Geography!B2:C184")
RAD = RadCombo1.Value
Dim RADCount As Integer
Dim StartCell As Integer
Dim Q As Double

RADCount = Application.WorksheetFunction.CountIf(RADRange, RAD)
StartCell = Application.Match(RAD, RADRange, 0)
ReDim PCOArray(RADCount)
For Q = 1 To RADCount
PCOArray(Q) = Application.WorksheetFunction.Index(LkpRange, StartCell + Q - 1, 2)
MsgBox "PCO is : " & PCOArray(Q)
Next Q
Unload Me
End Sub

So far, so good....

Fee

"The cure for anything is salt water – sweat, tears, or the sea." Isak Dinesen

 

[Welshbird]

Wowza! Easier than I thought.

Private Sub PCOButton_Click()
Dim RAD As String
Dim RADRange As Range
Dim LkpRange As Range
Dim PCOArray() As String
Set RADRange = Range("Geography!B2:B184")
Set LkpRange = Range("Geography!B2:C184")
RAD = RadCombo1.Value
Dim RADCount As Integer
Dim StartCell As Integer
Dim Q As Double

RADCount = Application.WorksheetFunction.CountIf(RADRange, RAD)
StartCell = Application.Match(RAD, RADRange, 0)
ReDim PCOArray(RADCount)
For Q = 1 To RADCount
PCOArray(Q) = Application.WorksheetFunction.Index(LkpRange, StartCell + Q - 1, 2)
Me!PCOList1.Selected(StartCell + Q - 2) = True
Next Q
End Sub

All I need to do now, is to scroll to put the focus on the correct bit of the listbox, as it has 182 members....

Any ideas chaps?

Fee

"The cure for anything is salt water – sweat, tears, or the sea." Isak Dinesen

 

[Welshbird]

Also.... at the moment this can add multiple selections, and I'm not sure that's sensible for the process..... I guess I need to find a way to make the list box = ALL FALSE just in case that's required.

Fee

"The cure for anything is salt water – sweat, tears, or the sea." Isak Dinesen

 

[SkipVought]

I want always to display the complete list in the listbox, which I hope makes it easier

It ain't so, Joe!

Sucessive selections ought to narrow down the choices.


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Welshbird]

I would agrede, but this is designed to allow the sales team to re-define areas, so they cant to be able to add to (and delete from) the auto-selected items I give them.

So, right now, it seems that it does what they want!

And TopIndex has solved my 'focus to the right bit of the list' option.

Any minute now I might be a proper developer innit!


Fee

"The cure for anything is salt water – sweat, tears, or the sea." Isak Dinesen

 

[Welshbird]

(Oh, I removed my array after testing - as I clearly didn't need it.)



Fee

"The cure for anything is salt water – sweat, tears, or the sea." Isak Dinesen