How do I pass a string of the Name of a combobox and then use it as a combobox?

    I'm using a class to get the name of the combobox for which the drop down was clicked. I then want to fill the combobox from an SQL query.


    Part of that process needs me to pass the combobox name as a combobox and not a string.


    This is what I've tried:


    Why don't you just pass the combobox as an argument, and calculate the row and column in the Container routine?


    Also, note that a worksheet does not have a Controls property. If the control is activex you can access it through the OLEObjects property, or if not, through the Dropdowns property.

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

    Quote from rory

    Why don't you just pass the combobox as an argument, and calculate the row and column in the Container routine?


    Also, note that a worksheet does not have a Controls property. If the control is activex you can access it through the OLEObjects property, or if not, through the Dropdowns property.


    How would I pass the control as an argument? In my above code all I can get is the string name of the control. No matter where I try to make this into a control it fails.

    Code
    1. Public Sub Container(cb as MSForms.Combobox, ws As Worksheet)


    then call it using:


    Code
    1. modDataValidation.Container Combo, R1


    and add the row/column calculations to the container routine using the cb variable.

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

    I'm still having an issue with this. There is one section that I still only have the control as a string. I read something about addressing worksheet Activex elements as an OLEObject. That method does use the object in string form (I think).


    I'm wondering if this is not possible or if I just have the syntax wrong. This is what I'm working with.

    Code
    1. If (CboRw = 33) Then
    2. 'Container
    3. R1.OLEObjects("cboContainer_R1_7").Object.Select
    4. R1.OLEObjects("cboContainer_R1_7").Object.Value = "Select Text"
    5. 'R1.cboContainer_R1_7.Activate
    6. SendKeys ("{ESC}")
    7. .......

    I can determine what worksheet I'm on and the name of the combobox as a string. After I select the combobox I'm loading data to it via a query. I need to use activex controls because the drop down will show two columns and also due to text formatting. I added the sendkeys to try and close the dropdown after the combobox was activated, that part is not working out either.


    If I use the commented out code on line 5, then I can activate the combobox. I added that just as a text to see how to activate the control.


    So to try and be clearer, I'll add a list of my major steps:

    1 Click Drop Down of class made combobox.

    2 Determine row and column of the combobox by parsing the name.

    3 Fill the combobox via a query.

    4 Make sure the dropdown is closed.


    Any insights will be appreciated.

    Can you please clarify why you can't pass it as an object?

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

    I'll try to explain.

    Starting from the combo drop down click. I'm getting the combobox as a string and I can't convert that into a combobox object.


    I can parse that string and because of how I labeled each combobox, I can get the worksheet and column number. The row number is tied to the type of object, so "Container" is always in row 33.


    I have not been able to actually load a combobox unless I use the actual combobox name and not just the string version of the name.

    Quote from jrdnoland

    I'll try to explain.

    Starting from the combo drop down click. I'm getting the combobox as a string and I can't convert that into a combobox object.

    You're getting the string using Combo.Name but Combo is the object itself, so pass that.

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why