Solved

Visual Basic 6 search excel 2007 drop down list

Posted on 2013-01-20
5
469 Views
Last Modified: 2013-01-31
I have a Visual Basic 6 program that opens up an existing Excel spreadsheet using OLE automation.

The spreadsheet has a number of drop down lists that are pre populated with values.

I am looking for a way to find two matching values in the drop down list (two parts of the same exel drop down list item) and select them using OLE commands from Visual basic.

For example: I have two search terms -  make of car and year of production, for example:

'Holden' and '1973'. The make of car and year of production will change depending upon user selection within the VB6 application.

Excel has prepopulated drop down lists.

Unfortunately, when the lists were created, they were of a non standard format (the lists cannot be altered to a standard format as they are centrally set by the organisation). The format for the items in the list can range from :

Holden - 1973
Ford    - 1960
Ferrari -     1959

As stated I need the ole automation commands for Visual basic that will scan for 'Holden' and '1973' in any of the excel drop down list items (ignoring the number of spaces and hyphen between) and select this item from the drop down list programattically.

There are a number of these (non standardly formatted lists) that must be searched in the same excel spreadsheet.

Many thanks for any assistance!
0
Comment
Question by:dwknight
  • 3
  • 2
5 Comments
 
LVL 46

Expert Comment

by:Martin Liss
ID: 38799642
Are you looking to see if for example "Holden" is in the set of possible valid values for a cell, or are you looking to determine if the user has selected "Holden"?
0
 

Author Comment

by:dwknight
ID: 38799730
Thanks for the clarification query.

I am looking to see if 'Holden' is in the set of possible valid values in the excel list. The Visual Basic front end has ensured that valid values have been entered before passing them through to excel to search the existing lists.
0
 
LVL 46

Accepted Solution

by:
Martin Liss earned 500 total points
ID: 38799768
    Dim strValidationRange As String
    Dim rngValidation As Range
    Dim rngItem As Range
    
    ' B3 is a cell affected by the validation 
    strValidationRange = Range("B3").Validation.Formula1
  ' Sheet1 is the sheet where the valid values reside
    Set rngValidation = Sheets("Sheet1").Range(strValidationRange)
     
    For Each rngItem In rngValidation.Cells
        If rngItem.Value = "Holden" Then
            MsgBox "Found"
        End If
    Next

Open in new window

0
 

Author Closing Comment

by:dwknight
ID: 38841976
Thanks MartinLiss,

Your suggested answer got me pretty much answered my question - with only a slight modification required.

I attempted the exact syntax suggested above, but it excel would not search through the listbox in the cell containing the lookup list without an error occurring.

I had to directly reference the lookup list on another sheet in the same workbook - I modified the range variable to directly reference the lookup list.

From this minor mod, I was able to obtain the exact text that allowed me to set the value of the entry in the listbox!

Thanks for your help - you have saved me a lot of time!
0
 
LVL 46

Expert Comment

by:Martin Liss
ID: 38841992
You're welcome and I'm glad I was able to help.

Marty - MVP 2009 to 2012
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question