ComboBox Selection

[riya787]

Hi, the following attachment is my workbook on which i am working on. The code which i have written is not giving the output which i want and it is printing 0. I know for a list of variables to be entered in the listbox i should use some other function other than VLookup. But i don't know which one i should use so that it gives me the variables according to the table name which i have mentioned in the drop down box. I hope you could help me with that.

[maninweb]

Hi riya787...

you have an error when adding the result to your listbox; please use...

ListBox1.AddItem val

Regards :-)

[riya787]

Hey thanks it worked. but only one variable is getting displayed, I want all the variables to be displayed.Could you help me with that..

[maninweb]

Hi riya787...

as the Worksheet function from VBA works like the Excel LOOKUP() function, I think in this case it would be better to iterate through the entries and add them if the key matches.

Private Sub ComboBox1_Change()

  Dim lngIndex    As Long
  Dim lngLastRow  As Long
  Dim strValue    As String
  
' With...
  
  With ThisWorkbook.Worksheets("Sheet1")
  
'   Get some values...
    
    strValue = ComboBox1.Value
    lngLastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
    
'   Check length...
    
    If Len(strValue) > 0 Then
    
'     Clear Listbox...
      
      ListBox1.Clear
      
'     Loop...
      
      For lngIndex = 2 To lngLastRow
        
'       Check...
        
        If Not strValue <> .Cells(lngIndex, 1).Value Then
          
          ListBox1.AddItem .Cells(lngIndex, 2).Value
          
        End If
      
      Next lngIndex
    
    End If
    
  End With
  
End Sub

PS: if I had understood well your request.

Regards :-)

[riya787]

Hi maninweb. This is how I wanted. Thank You so much for your help

[riya787]

Hi,I need your help in the ComboBox selection, I want to select only unique values rather than duplicate values. In the row source i have given the name of the column which should get displayed in the Drop down Box. I don't want to create a another column and give its name in the Rowsource for the ComboBox. Please if you could help me with it.

[maninweb]

Hi riya787...

I am not sure, if I had really understood your request, but I attached a file with
some modifications. Hope this helps.

Regards :-)

[riya787]

Hey, thanks. This is how i wanted it. . And I had two more questions.
1]Can i load all this information from a different worksheet i.e. the information that i have stored in the excel sheet that is the table and variable information.
2]can i have macros for each and every function and then can i call them using Application.Run("Macro Name")

[maninweb]

Hi riya787...

glad, I could help :-)

a) Yes, this is possible, you may adapt the sheet name in the With Statement(s) and also the row and column values/indexes, depending where your data is.
b) It's possible to create globally accessible functions stored in a module, but, is there a specific reason you want to run them per Application.Run?

Regards :-)

[riya787]

Hi, thanks again.....
There is no specific reason that i want to use Application.Run, Actually in the code when i was giving Call ("Macro Name"), it was not working. So thought of using Application.Run