Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Visual Basic 6 search excel 2007 drop down list

Posted on 2013-01-20
5
Medium Priority
?
487 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 49

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 49

Accepted Solution

by:
Martin Liss earned 2000 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 49

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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

916 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