Populating Dropdown boxes using dynamic data

[0writer]

Hi everyone,

I'm really struggling at the moment and am hoping someone can help.

I have an excel workbook currently with one sheet. The data on this sheet is sucked in from an application using an ODBC link.

There are three relevant columns, which are CustomerName, RefNumber and FullyInvoiced.

I need to populate a couple of dropdown boxes using the data in these columns. The CustomerName could be the same on multiple lines, RefNumber is always unique and FullyInvoiced is either set to 0 or 1.

I would like to populate the first dropdown with a unique list using the information from CustomerName. This should then determine what appears in dropdown2, which should list all of the RefNumber items relating to that customer, but only where FullyInvoiced equals 0.

That's the goal.

The data coming in via ODBC will always fit the correct column names above but will change, potentially multiple times a day.

I'd be really grateful for any help, advice or input.

Thanks in advance.

 

[AlphaFrog]

What type of dropdown boxes do you want? There are different ones used in different situations and each has their own set of requirements and limits.

Dropdown box types;

• ComboBox on a VBA UserForm
• ActiveX type (Toolbox toolbar) ComboBox on the sheet
• Forms type (Form toolbar) ComboBox on the sheet
• Data Validation drop down list
• Autofilter drop down lists
• Pivot Table drop down lists

...there may be others.

Autofilter would be the easiest to set up.
Excel Filters: Excel 2007 AutoFilter Basics

 

[Gary McMaster]

If you don't really need the drop down boxes you can just use the AutoFilter mechanism.

You'll have the unique list of customer names in one filter list. After picking a customer name just pick 0 from the invoiced list and your done.

Search for "Filtering" in Excel help.

Gary

 

[0writer]

Thanks for the quick replies. I've already looked at AutoFilter but don't know how to link the dropdown boxes as mentioned in my post.

I do need the dropdown boxes.

I'm creating a document that is going to be used to perform a work order. A user will select the customer, select the job and then manually complete a list of tasks that need to be completed for the job. That will then get printed and given to someone to complete the work.

Does that info help in determining what type of dropdown would be best suited?

 

[0writer]

Attached is an exmaple of the spreadsheet I'm working with.

The dropdown boxes have to be intelligent using IF statements I think as I don't really want the using having to filter the data first.

Any help would be really appriciated.

http://www.mediafire.com/?94of201qjbsr547

 

[Gary McMaster]

I couldn't do anything with your sheet. I don't have 2007 available. Also, the file extension was not .xlsm so I probably would not have been able to access your code anyways.

I've done stuff like this before using a dictionary. The procedure below only needs to be run once each time your query is refreshed. It may be a little slow in creating the dictionary (depending on the size of your database) but it should be extremly fast in reloading combo boxes and accessing the numbers associated with each customer after that.

You must add a reference to "Microsoft Scripting Runtime" (scrrun.dll) in your project. There are many resources on the web that will explain how the dictionary works if you decide to go this route.

Hope it helps.

Gary

Public Sub Test()

'Requires reference to "Microsoft Scripting Runtime", scrrun.dll

Dim oDict As Dictionary
Dim oCustomer As Range
Dim oName As Range
Dim vNode As Variant

'Change column "A" (next line) to the column containing the "Customer Names" with duplicates
Set oCustomer = ActiveSheet.Range("A2:A" & Range("A" & Rows.Count).End(xlUp).Row)

Set oDict = New Dictionary

For Each oName In oCustomer
    'If the customer name is not already in the dictionary then add it
    'also add the order number or reference number as the associated data
    If Not oDict.Exists(oName.Text) Then
        oDict.Add oName.Text, oName.Offset(0, 1).Text & ","
        
    'If the customer is already in the dictionary then append the order number or
    'reference number for this line to the already existing string of numbers
    'use a delimiter that will never appear in the order or reference number
    'to seperate the order/reference numbers (comma used below)
    Else
        'Check for other conditions that warrant order/reference number addition
        'If oname.offset (0,2) <> 0 then 'Whatever the offset is to "Fully Invoiced"
            'Concatenate order/reference numbers associated with this customer delimit with comma
            oDict(oName.Text) = oDict(oName.Text) & oName.Offset(0, 1).Text & ","
        'End if
    End If
Next oName

For Each vNode In oDict
    Debug.Print vNode
    'Load the dropdown list for customer names with vNode here ...Additem vNode
Next vNode

'Show the customer keys and concatenated order numbers for debug only
For Each vNode In oDict
    Debug.Print vNode & vbTab & oDict(vNode)
Next vNode

Dim vNumberlist As Variant
Dim sCustomerName As String

sCustomerName = "INTERSCAN_SE" ' This customer name would be that selected from the dropdown list

MsgBox oDict(sCustomerName)

'Get rid of the trailing comma (or whatever delimiter) added when loading the dictionary
If vNumberlist <> "" Then
    vNumberlist = Left(vNumberlist, (Len(vNumberlist) - 1))
End If

'Split the list of numbers into an array using the comma as the delimiter
vNumberlist = Split(oDict(sCustomerName), ",")

Dim lCount As Long

For lCount = LBound(vNumberlist) To UBound(vNumberlist)
    Debug.Print vNumberlist(lCount)
    'Load the array of numbers into the number dropdown list here ... AddItem vNumberlist(lCount)
Next lCount

End Sub

 

[0writer]

Thanks for the reply Gary.

I've tried to get the code working but all I get wehn I run it is a small msgbox with no text content.

Here is a copy of the workbook saved in an earlier version.

http://www.mediafire.com/?tsc2lhxxyckl1n7

Would you mind taking another look for me and see what I'm doing wrong?

Thanks,

 

[Gary McMaster]

Please send me a private message containing your email address and I'll send back a copy of your book with working code.

Please don't post your email address on the board. If one of the moderators does not remove your email address you'll get spammed to death.

Gary

 

[0writer]

Just sent, thanks Gary.