Hi,

Need your help,

I have two sheets named as Workings & Database

In the sheet named as Database, i have two columns

Product : Vendor

The Product can be same but Vendor can be different.

In the sheet named as Workings, I would use List Option to select
Select Product. As soon as I select any Specific Product the second
list should show me only those list of vendors who deals into the
specific product.

Pls help me how to populate this in MS Excel.

Rgds

Akash Maheshwari

Akash,

This is a little complex, but seemed to work for my test book. The general idea was to use SQL to query the "Database" tab for the relevant information and populate the data validation lists accordingly. There are two bits of code he one to create the list and the other to trigger a change to the lists.

First, add the macro called "ValidationList" to Module1 of your project. This code assumes that your Products are in column A of the "Database" sheet and that Vendors are in column B of the same. You will need to adjust the "SELECT CASE" section of the code if these assumptions are incorrect for your data.

Next, add the "Worksheet_SelectionChange" macro to the "Workings" sheet code module. This macro assumes that the Product list will be in cell B5 and the Vendor list in cell B6. Again, change these as appropriate.

If all goes according to plan, you can simply select the cell and it will automatically find the appropriate list for you.

Hope this helps,

Ben

'Place the code below in Module1

Sub ValidationList(sType As String, rCell As Range, Optional sProduct As String)
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim strFile As String
Dim strCon As String
Dim strSQL As String
Dim vArray As Variant

strFile = ThisWorkbook.FullName
strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strFile _
& ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"

Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")

cn.Open strCon

Select Case sType

Case "Product"
strSQL = "SELECT DISTINCT * FROM [Database$A:A]"
Case "Vendor"
strSQL = "SELECT DISTINCT Vendor FROM [Database$A:B] WHERE Product = " & _
Chr(34) & sProduct & Chr(34)
Case "AllVendors"
strSQL = "SELECT DISTINCT * FROM [Database$B:B]"
Case Else
cn.Close
Exit Sub

End Select

rs.Open strSQL, cn

With rCell.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:=rs.GetString(adClipString, , ",", ",")
End With

cn.Close

End Sub



'''''''''''
'Place the code below in the "Workings" Sheet's VBA module

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim rProduct As Range, rVendor As Range

Set rProduct = Range("B5")
Set rVendor = Range("B6")

If Target.Count 1 Then Exit Sub 'Exit if multiple cells selected

'Populate product cell or vendor cell as applicable
If Not Intersect(rProduct, Target) Is Nothing Then
Application.EnableEvents = False
ValidationList "Product", rProduct
ElseIf Not Intersect(rVendor, Target) Is Nothing Then
Application.EnableEvents = False
If rProduct < "" Then
ValidationList "Vendor", rVendor, rProduct.Value
Else
ValidationList "AllVendors", rVendor
End If
End If

Application.EnableEvents = True

End Sub