Adding Selection to List Box based upon prior List Box selection

Larcen60

New Member
Joined
Sep 12, 2017
Messages
18
***This is cross-posted on StackOverflow at this link . It has received no comments yet, so I'm posting here as well***

I have a User Form where a user makes a selection in a list box (called Relay). That selection populates an option in a second list box (Called Squad) based upon the following criteria:

  • The value in column "O" is not blank
  • The value in column "D" IS blank
  • The value in column "L" matches the user's selection in the 1st list box (Relay)
The code I have is somewhat working, however the last bullet point is the problem. Whenever I select a "Relay" it is displaying all the blank "Squads" instead of the ones where just Column "L" matches the User Selection. Do I have an error in the logic of my "If" statement? I have attached some screenshots to help explain what I am attempting to accomplish. Thanks for any help you can provide!

VBA Code:
Private Sub lstRelayNumber_Click()

Dim Dict As Object
Dim LastRow As Long
Dim Relay As Range, vL, vD, vO
Dim RelayNumber As Long

Set Dict = CreateObject("Scripting.Dictionary")

With Sheets("Score Sheet")
    LastRow = .Cells(.Rows.Count, "O").End(xlUp).Row
    Me.lstSquadNumber.Clear
    RelayNumber = Me.lstRelayNumber.Value
    
          
    For Each Relay In .Range("O2:O" & LastRow).Cells
       vO = Relay.Value                     'Column O value
       vD = .Range("D" & Relay.Row).Value  'Column D value
       vL = .Range("L" & Relay.Row).Value  'Column L Value
       
       
             If Len(vL) = RelayNumber And Len(vD) = 0 And Len(vO) > 0 Then
           If Not Dict.exists(vO) Then
               Dict.Add vO, 1
               lstSquadNumber.AddItem vO
           End If
        End If
       Next Relay
End With
End Sub


Sample1.jpg
Sample2.png
 

Some videos you may like

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,774
Office Version
  1. 365
Platform
  1. Windows
How about
VBA Code:
   For Each Relay In .Range("O2:O" & lastRow).Cells
      vO = Relay.Value                     'Column O value
      vD = .Range("D" & Relay.Row).Value  'Column D value
      vL = .Range("L" & Relay.Row).Value  'Column L Value
      
      
      If vL = RelayNumber And vD = "" And vO <> "" Then
         If Not Dict.Exists(vO) Then Dict.Add vO, 1
      End If
   Next Relay
   lstSquadNumber.List = Dict.Keys
 
Solution

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,274
Office Version
  1. 365
Platform
  1. Windows
Why are you using Len 3 times here?
VBA Code:
 If Len(vL) = RelayNumber And Len(vD) = 0 And Len(vO) > 0 Then
Shouldn't you be comparing the value in column L to the selected relay, not the length?
 

Larcen60

New Member
Joined
Sep 12, 2017
Messages
18
Thank you both so much! I'm such a bonehead, I completely missed that there shouldn't be a "Len" in front of vL. Completely simple mistake, thank you both for the time in pointing that out!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,774
Office Version
  1. 365
Platform
  1. Windows
Glad we could help & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,128,091
Messages
5,628,617
Members
416,329
Latest member
phxdan79

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top