Posts by jrdnoland

    I'm running Excel vba 2016 code to make a Named Range from a query on SQL SERVER that is performed on a view. I have multiple tables in SQL SERVER that I'm doing this with. All of the ones that are run directly on a Table seem to work OK. I decided to make a few Views on data that would change more often thinking that the View would be updated when the data changed.


    I'm not sure this is what's causing the issue, but it's the only thing that has changed.


    Here are two subs that query the SQL SERVER, one for a table query the other for a View query.


    This works fine:

    Code
    1. Public Sub Package_Type()
    2. Dim SQL As String
    3. SQL = ""
    4. SQL = SQL & "SELECT DISTINCT [Package Type]"
    5. SQL = SQL & " From rdLab.tblPackage_Type"
    6. SQL = SQL & " WHERE (Package_Type_Is_Active = 1)"
    7. SQL = SQL & " ORDER BY [Package Type]"
    8. Call modDataValidation.GetDataFromSQL_SERVER("O2", "O", SQL, 12, "Package_Type", 1)
    9. End Sub


    This won't expand the Named Range:


    Code
    1. Public Sub Container()
    2. Dim SQL As String
    3. 'viewContainers is a view made from dbo.BomInfo
    4. SQL = ""
    5. SQL = SQL & "SELECT DISTINCT MATERIAL_COMPONENT, MATERIAL_DESCRIPTION_COMPONENT"
    6. SQL = SQL & " From dbo.viewCONTAINERS"
    7. SQL = SQL & " ORDER BY MATERIAL_COMPONENT"
    8. Call modDataValidation.GetDataFromSQL_SERVER("P2", "Q", SQL, 13, "Containers", 2)
    9. End Sub



    Here is the sub that runs the query and changes the Named Range:



    I'm not sure what the issue is. I have also tried first deleting the existing Named Range with no success.


    Thanks,

    Jeff

    I have two userform listboxes and I want to transfer the selected row from one to the other listbox. Both are two column listboxes set to frmmultiSelectMulti.


    I have seen several posts on various other listbox techniques, but not many on transferring from one to another. I also want to remove the transferred items from the first box as they are added to the second box. I get invalid array index when I try running the code below.


    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.

    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.

    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.

    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:


    That sort of works. What I'm seeing is that the classes are able to give me the name of the control, but it appears to loose the "Hooks" and I have to rerun the hook before I can click on a combobox that is in a cell. I used activex comboboxes mostly just becasue I could set the font to bold. I can't do the with combo form control.


    I need to make it known when a combobox or optionbutton was clicked. This can be in two separate classes. What I have tried is this:

    MODERATOR NOTICE: This topic has also been posted on other sites and may already have an answer elsewhere. Please take this into consideration when answering this question

    I'm trying to get this formula to work correctly, not sue what I'm missing? Perhaps a fresh set of eyes will help.



    Here is the formula commented out, have also attached a spreadsheet.


    '=IF(AND(C44<=2,OR(NOT(COUNTIF(D44:49,">0.9")),NOT(COUNTIF(D44:49,"<2")),E44<=2,OR(NOT(COUNTIF(F44:F49,">0.9")),NOT(COUNTIF(F44:F49,"<2")),G44>=2,H44>=4.5))),"Pass","Fail")


    The formula seems correct but if a value in one of the ranges is out of the bounds set the formula is incorrect. Note the formula looks like it picked up a space in this context.


    Cross posted on vbax http://www.vbaexpress.com/foru…TIF-now-working-correctly


    I have not gotten any answers from vba express yet.


    Thanks,


    Jeff