ComboBox Selection

riya787

New member
Joined
Mar 10, 2012
Messages
17
Reaction score
0
Points
0
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.
 

Attachments

  • Book1.xls
    31.5 KB · Views: 66

maninweb

New member
Joined
Mar 22, 2011
Messages
88
Reaction score
0
Points
0
Location
Germany
Website
excel-translator.de
Excel Version(s)
2010, 2016, 2016 Insider
Hi riya787...

you have an error when adding the result to your listbox; please use...
Code:
ListBox1.AddItem val

Regards :)
 

riya787

New member
Joined
Mar 10, 2012
Messages
17
Reaction score
0
Points
0
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

New member
Joined
Mar 22, 2011
Messages
88
Reaction score
0
Points
0
Location
Germany
Website
excel-translator.de
Excel Version(s)
2010, 2016, 2016 Insider
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.
Code:
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

New member
Joined
Mar 10, 2012
Messages
17
Reaction score
0
Points
0
Hi maninweb. This is how I wanted. Thank You so much for your help :clap2:
 

riya787

New member
Joined
Mar 10, 2012
Messages
17
Reaction score
0
Points
0
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.
 

Attachments

  • test.xlsm
    17.5 KB · Views: 31

maninweb

New member
Joined
Mar 22, 2011
Messages
88
Reaction score
0
Points
0
Location
Germany
Website
excel-translator.de
Excel Version(s)
2010, 2016, 2016 Insider
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 :)
 

Attachments

  • test.xlsm
    19.6 KB · Views: 56

riya787

New member
Joined
Mar 10, 2012
Messages
17
Reaction score
0
Points
0
Hey, thanks. This is how i wanted it. :clap2:. 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

New member
Joined
Mar 22, 2011
Messages
88
Reaction score
0
Points
0
Location
Germany
Website
excel-translator.de
Excel Version(s)
2010, 2016, 2016 Insider
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

New member
Joined
Mar 10, 2012
Messages
17
Reaction score
0
Points
0
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
 

riya787

New member
Joined
Mar 10, 2012
Messages
17
Reaction score
0
Points
0
Can i Load the infromation from a different worksheet all together, rather thanusing the same excel sheet which has the vba userform?
 

riya787

New member
Joined
Mar 10, 2012
Messages
17
Reaction score
0
Points
0
Hi maninweb, The excel sheet which you had sent me, can i add more tables in the first column along with their variables and description and then can it be displayed in the combobox selection. because when i do it it is not getting displayed in the combobox dropdown.
 

maninweb

New member
Joined
Mar 22, 2011
Messages
88
Reaction score
0
Points
0
Location
Germany
Website
excel-translator.de
Excel Version(s)
2010, 2016, 2016 Insider
Hi riya787...

sorry to not answer quickier, but I really have so much work and unfortunately can not often visit forums.

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
Well, this does not work as the events = Subs launched on interactivity with the controls are private. Private Subs or Functions can not be accessed from outside the code module they are in. Please also take a look on the VBA Help, by searching for "private" and "public".

Can i Load the information from a different worksheet all together, rather than using the same excel sheet which has the vba userform?
Of course, this is possible.In the sample workbook, there is a case where the Combobox values are stored in another sheet.

The excel sheet which you had sent me, can i add more tables in the first column along with their variables and description and then can it be displayed in the combobox selection. because when i do it it is not getting displayed in the combobox dropdown.
If the tables names are not listed in your column with these names, then they won't appear in the Combobox.

I added some sample code to the workbook, including two new UserForms:


  • The UserForm "FRM_Default" corresponds to your previous UserForm.
  • The UserForm "FRM_Alternative" externalizes the function to fill the CombobBox. However, as I don't know the goals of your project, I don't know if this approach makes sense (perhaps it's an option to tell us more about your project).
  • And the UserForm "FRM_AllInOne" scans the data sheet (Colum A with the tables names) for all table names without needing an extra sheet for the table names.

Hope this helps.

Regards :)
 

Attachments

  • Riya787.xlsm
    33.4 KB · Views: 33

riya787

New member
Joined
Mar 10, 2012
Messages
17
Reaction score
0
Points
0
Hey thanks a lot maninweb, i wanted it in AllInOne Userform. Thanks again.. and its ok if you reply late. Really glad that you can help me on my project. Thanks again
 

riya787

New member
Joined
Mar 10, 2012
Messages
17
Reaction score
0
Points
0
Hi maninweb, Can i pass elements of an array to a sql query in vba i.e. the tables and the variables to create a new table ,and the size of the array keeps changing at runtime.Can you tell me how to do it?
 

maninweb

New member
Joined
Mar 22, 2011
Messages
88
Reaction score
0
Points
0
Location
Germany
Website
excel-translator.de
Excel Version(s)
2010, 2016, 2016 Insider
Hi riya787...

in the following, one method for creating, filling and resizing arrays. The sample code build a SELECT statement.
Keywords are Redim, Preserve, the declaration of the array with brackets without a size.
Code:
  Public Function BuildSQLQuerySample() As String
  
    Dim strResult As String
    Dim lngIndex  As Long
    
    Dim arrData() As String
    
'   Create Array...
    
    ReDim arrData(1 To 4)
    
'   Add some items...
    
    arrData(1) = "One"
    arrData(2) = "Two"
    arrData(3) = "Three"
    arrData(4) = "Four"
    
'   Test...
    
    MsgBox Join(arrData, " - ")
    
'   Sample...
    
    strResult = "SELECT * FROM [myTable] WHERE "
    
'   Loop...
    
    For lngIndex = LBound(arrData) To UBound(arrData)
      
      strResult = strResult & "[myField]='" & arrData(lngIndex) & "' OR "
      
    Next
    
'   Test...
    
    MsgBox strResult
    
'   Add array items and preserve existing items...
    
    ReDim Preserve arrData(1 To 8)
    
'   Add some items...
    
    arrData(5) = "Five"
    arrData(6) = "Six"
    arrData(7) = "Seven"
    arrData(8) = "Eight"
    
'   Loop again...
    
    For lngIndex = 5 To UBound(arrData)
      
      strResult = strResult & "[myField]='" & arrData(lngIndex) & "' OR "
      
    Next
    
'   Test...
    
    MsgBox strResult
    
'   Remove last OR and spaces...
    
    strResult = Trim(strResult)
    strResult = Trim(Left(strResult, Len(strResult) - Len("OR")))
    
'   Test...
    
    MsgBox strResult
    
'   Result...
    
    BuildSQLQuerySample = strResult
    
  End Function
Of course, you may adapt the code to your situation.
Regards :)
 

riya787

New member
Joined
Mar 10, 2012
Messages
17
Reaction score
0
Points
0
Hey thanks again maninweb..your post are a great help for me..:clap2:
I have written code which stores the variables selected in a array.The array are getting the variables from the ListBox2 which has all the selected variables from the user. These variables are of teh form tbl_1.id or tbl_2.name. In order to extract the table names I am using the split function and storing it in a table_array but i am not able to get the tables Properly. The code is as Follows:
Code:
Option Explicit
Public variable_array() As String
Public table_array() As String


Public Sub Get_Variable()
With ThisWorkbook.Worksheets("Data")




Dim ListBox2  As ListBox
Dim index As Variant
Dim variable As Variant
Dim temp As String
Dim j As Integer




ReDim variable_array(25)


'Storing the tables in an array
For index = 0 To (Data_Extraction.ListBox2.ListCount - 1)
variable_array(j) = Data_Extraction.ListBox2.List(index)
j = j + 1
Next index


ReDim table_array(10)
'Getting the tables which have been selected from the Selected list of Variables
For index = 0 To (Data_Extraction.ListBox2.ListCount - 1)
temp = variable_array(index)
table_array() = Split(temp, ".")
Next index


For index = 0 To (Data_Extraction.ListBox2.ListCount - 1)
MsgBox table_array(index)
Next index




End With
End Sub
 

maninweb

New member
Joined
Mar 22, 2011
Messages
88
Reaction score
0
Points
0
Location
Germany
Website
excel-translator.de
Excel Version(s)
2010, 2016, 2016 Insider
Hi riya787...

if I understand it well, you have a list values (tbl_1.id, tbl_2.name) which you want to store in an array and split these values for getting the table names in another array. I added some code here, the first Sub Get_Variable() gets all tables with (!) duplicates. The second Get_Variable_Uniques() gets the tables without (!) duplicates.
Code:
  Option Explicit
  
  Public variable_array() As String
  Public table_array()    As String
  
  Public Sub Get_Variable()
    
    Dim lngIndex    As Long
    Dim lngCurrent  As Long
    Dim lngCount    As Long
    Dim strValue    As String
    
'   Items...
    
    lngCount = Data_Extraction.ListBox2.ListCount - 1
    
'   Redim to Listbox size
    
    ReDim variable_array(0 To lngCount)
    ReDim table_array(0 To lngCount)
    
'   Storing the tables in an array
    
    For lngIndex = 0 To lngCount
      
'     Value...
      
      strValue = Data_Extraction.ListBox2.List(lngIndex, 0)
      
'     Variables array...
      
      variable_array(lngIndex) = strValue
      table_array(lngIndex) = Split(strValue, ".")(0)
      
    Next lngIndex
    
'   Testing...
    
    For lngIndex = LBound(table_array) To UBound(table_array)
      
      MsgBox table_array(lngIndex)
      
    Next lngIndex
    
  End Sub
  
  Public Sub Get_Variable_Uniques()
    
    Dim lngIndex    As Long
    Dim lngCurrent  As Long
    Dim lngCount    As Long
    Dim strValue    As String
    Dim objUniques  As Collection
    
'   Errors...
    
    On Error Resume Next
    
'   Initialize...
    
    lngCount = Data_Extraction.ListBox2.ListCount - 1
    
'   Redim
    
    ReDim variable_array(0 To lngCount)
    ReDim table_array(0 To lngCount)
    
'   Collection...
    
    Set objUniques = New Collection
    
'   Storing the tables in an array
    
    For lngIndex = 0 To lngCount
      
'     Value...
      
      strValue = Data_Extraction.ListBox2.List(lngIndex, 0)
      
'     Variables array...
      
      variable_array(lngIndex) = strValue
      
'     Clear...
      
      Err.Clear
      
'     Split...
      
      strValue = Split(strValue, ".")(0)
      
'     Add...
      
      objUniques.Add strValue, strValue
      
'     Errors...
      
      If Not Err.Number <> 0 Then
        
'       Data...
        
        table_array(objUniques.Count - 1) = strValue
        
      End If
      
    Next lngIndex
    
'   Resize...
    
    If objUniques.Count > 0 Then
      
      ReDim Preserve table_array(0 To objUniques.Count - 1)
    
    End If
    
'   Collection...
    
    Set objUniques = Nothing
    
'   Testing...
    
    For lngIndex = LBound(table_array) To UBound(table_array)
      
      MsgBox table_array(lngIndex)
      
    Next lngIndex
    
  End Sub
You may test the code (I have only done a quick test) and choose the appropriate one.

In the first Sub Get_Variable() there is no need to do two loops, as both arrays have the same size. Assuming that all value contains a dot, when Splitting with Spilt(), you may get the first element of the result from Split().

Filtering the duplicates in Get_Variable_Uniques() is a little bit more complex. For doing this, I used a collection, an object holding items with a unique key. If an already existing item is added to a collection, a runtime error is thrown. So, if the runtime error is ignored (that's why On Error Resume Next is on the top) but also checked per code, then the unique items can be filtered and added to the table array. The last step is to downsize the table array to the real count which corresponds to the count of collection items.

I have also some tipps for you: please indend the code when writing, this makes the code much more comfortable to read. If you declare variables, for example a counter, please use the appropriate type. For example Dim index As Long is better than Dim index As Variant as in this case you are using the variable for iterating through a loop.

Hope this helps.

Regards :)
 

riya787

New member
Joined
Mar 10, 2012
Messages
17
Reaction score
0
Points
0
Hi, thanks maninweb for your post and will keep your tips in mind.Thanks again...
 
Top