Solved

Visual Basic 6 search excel 2007 drop down list

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

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 45

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 45

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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

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 code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

708 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now