Solved

Visual Basic 6 search excel 2007 drop down list

Posted on 2013-01-20
5
479 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 48

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 48

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 48

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

631 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