Page 2 of 3 FirstFirst 1 2 3 LastLast
Results 11 to 20 of 28

Thread: ComboBox Selection

  1. #11


    Register for a FREE account, and/
    or Log in to avoid these ads!

    Can i Load the infromation from a different worksheet all together, rather thanusing the same excel sheet which has the vba userform?

  2. #12
    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.

  3. #13
    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 :-)
    Attached Files Attached Files

  4. #14
    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

  5. #15
    Hi riya787, thanks

  6. #16
    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?

  7. #17
    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 :-)

  8. #18
    Hey thanks again maninweb..your post are a great help for me..
    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

  9. #19
    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 :-)

  10. #20


    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi, thanks maninweb for your post and will keep your tips in mind.Thanks again...

Page 2 of 3 FirstFirst 1 2 3 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •