Solved

Visual Basic 6 search excel 2007 drop down list

Posted on 2013-01-20
5
470 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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

789 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