1

I've got three combo boxes in a workbook that I want to daisy chain together. Item lists for each combo box refresh when you hit the down arrow on the keyboard after clicking the drop down button on the combo box. The second combo box list is dependent on the selection made in the first combo box. I've built these using scripting dictionaries.

strCustComboBox is the value in the previous combo box that the current combo box should be dependent on.

rngProject is looking at a range with lots of quote IDs in it. I offset from this column to the column where the values for the previous combo box are held and if this value is equal to strCustComboBox then add rngCompany value to the scripting dictionary

I'm running into a problem in the loop where I am trying to de-duplicate the rngCompany values written into the scripting dictionary that is used to build the list to be shown in the combo box. My code is below.

Sub UpdateComboBox1FromDashData()
Dim strCustComboBox As MSForms.ComboBox
Dim strComboBox As MSForms.ComboBox
Dim rngCompany As Range
Dim rngProject As Range
Dim d As Object, c As Variant, i As Long

Worksheets("QuoteEditor").Unprotect "xxxx"

Application.ScreenUpdating = False

Set strCustComboBox = ThisWorkbook.Worksheets("QuoteEditor").ComboBox4
Set strComboBox = ThisWorkbook.Worksheets("QuoteEditor").ComboBox1

If strCustComboBox = "" Then

      MsgBox "Please select a project first", vbOKCancel

Else
End If

ThisWorkbook.Worksheets("DashboardData").Select

Call FindLastRow("A", "10")

Set d = CreateObject("Scripting.Dictionary")
c = Range("A10:A" & strLastRow)


Set rngProject = ThisWorkbook.Worksheets("DashboardData").Range("A10:A" & strLastRow)

i = 1

For Each rngCompany In rngProject

    If UCase(rngCompany.Offset(, 7).Value) = UCase(strCustComboBox) Then

         If d.exists(rngCompany) = True Then

         Else
             d.Add rngCompany, i
             i = i + 1

         End If

     Else

     End If

Next rngCompany

For Each Item In d

     strComboBox.AddItem (Item)

Next Item

I think where I am using d.exists(rngCompany) is wrong but I'm not sure. When the subroutine finishes I still get duplicate data return to the combo box list.

I've also tried the code below as per the suggested duplicate thread:

With d

    For Each rngCompany In rngProject

        If UCase(rngCompany.Offset(, 7).Value) = UCase(strCustComboBox) Then

            If Not .exists(rngCompany) Then

                d.Add rngCompany, Nothing

            Else
            End If
        End If
    Next rngCompany
End With

Can anyone see where either of these are going wrong?

Improve this question
5
  • Possible duplicate of Only unique records in a Combobox (VBA) Commented Oct 11, 2018 at 10:23
  • @eirikdaude I've tried this. Unless I'm implementing their method incorrectly, which I do not believe I am, the approach doesn't work for what I'm trying to achieve. Commented Oct 11, 2018 at 10:51
  • 2
    You declare Dim d as Object but never initialize/assign it. I'd expect an error when trying to access a member of an empty object... Do d.Exists or D.Add work as you expect? Commented Oct 11, 2018 at 11:14
  • @Inarion Apologies I redacted some of the code I deemed irrelevant to the problem I am having. I've edited the question. Even though I put in steps to check for duplicates, I still get duplicate date in the drop down list when the subroutine has finished running. Commented Oct 11, 2018 at 12:27
  • How I'd approach the problem: You've got duplicate values you did not expect. What are the values? They have to be in that worksheet. Compare them. Are they actually duplicates (i.e. binary identical)? Step through your code - do they both get added to the dictionary as separate keys? Are you sure about that? Have a look at the entries in d in the locals window when execution has finished the For Each rngCompany In rngProject loop. Commented Oct 11, 2018 at 14:14

1 Answer 1

Reset to default
4

You hid the answer to this in your own question (emphasis mine):

where I am trying to de-duplicate the rngCompany values

There is no way for d.Exists(rngCompany) to return true the way that you have this written, because you are keying the Dictionary on the range, not its contents. Since the items you are testing are part of the iteration For Each rngCompany In rngProject, you are guaranteed to have only distinct ranges.

The solution is trivial - you need to explicitly call the default member of rngCompany:

If Not d.Exists(rngCompany.Value) Then
   d.Add rngCompany.Value, i
   i = i + 1   
End If
Improve this answer
Sign up to request clarification or add additional context in comments.

9 Comments

Whoa... I thought just the dictionary's Items could be objects. I did not expect it to also accept them as Keys. As such, when asked, I'd have said that VBA would likely access the Range's default member for the key. The more you know... +1
@Inarion This is precisely why you shouldn't get in the habit of relying on implicit default member access. ;-)
Thanks @Comintern I completely missed this. I've made the amendment, but unfortunately it's still not working - I still end up with duplicates so I think I'm missing something in the steps.
@AndrewBuchanan Can you verify these are actually duplicates? An additional space (or any other symbol) will make for a different entry. Can you fabricate an example that exhibits this behavior and which you can show us?
@AndrewBuchanan Did you change the d.Add rngCompany, i to d.Add rngCompany.Value, i too?
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.