Posts by browncoat

    I've been working on this workbook for about a year. Most of the functionality is done but I have issues with random crashes and occasional subroutines that get triggered for no apparent reason, i.e. I will be following along the code as it runs, line by line, but it will randomly jump to some other subroutine and run through it, even after I've gone through and removed every instance it is called.

    Additionally, the entire workbook seems to crash randomly when manipulating tables but it is inconsistent and not easy to replicate -- sometimes it happens, most of the time it doesn't. Not sure what to do at this point.

    I have a table and I want to get the value of a cell based on multiple criteria. Here is my currently formula

    =([@[Spec Mean]]-((XLOOKUP("BSL",[v.Con],[Spec Mean]))))/ (XLOOKUP("BSL",[v.Con],[Spec Mean]))

    The XLOOKUP function is returning the value for the ROW with "BSL" in the [v.Con] column but I have instances where there are multiple rows in that column with "BSL" and I need a second column to further specific the correct row. How do I include a second column requirement?


    I am importing records into excel by iterating through the field names and values and inserting into a table. Fields are strings, numbers, and dates. The problem is that I have a list of values that are comma separated and I want to insert them into a table as a string but excel keeps converting the cell to number, removing all the commas, and turning the string into one very large number. How can I prevent excel from doing this without affecting other cell formatting for fields that need to be number or date?

    Oh good to know, thanks, I've switched it to .Column. How is this different/more efficient, out of curiosity?

    for the combobox, boundcolumn is 2, textcolumn is 1. columncount is 2

    this is the function I use to get results from the database

    this is simplified for brevity but is the gist of what the code does that causes the problem. This worked find when I used a named range to populate the combobox.

    Public Sub ChangeChemList()
    Dim sampleid as Integer
    sampleid = 337
    SetupForm.ChemList.value = sampleid
    End Sub

    So if the item in the list has the id 337 and I do msgbox(SetupForm.Chemist.Value) it will display 337. But if I try to select this option by setting the value to 337 I get that error.

    A activex combobox on a userform has been working with two columns, with the first column showing an item name and the second displaying the item code. The second column is set as bound and the item code is used elsewhere in the form. I populated it by setting the .RowSource = tablerange and the combobox displays the first column when an item is selected but the code in the bound column is what sets the value.

    I recently switched to populating it with a SQL query to an Access database rather than from a table in the excel file and use .List = Application.Transpose(var) using SQL query results , but this has caused it to stop working. The combobox populates fine but I can't select a value from VBA code. Previously, I could make one of the options become selected in the combobox by setting the value of the combobox (e.g. Combobox.Value = 123) to a number that was listed in the bound column (2) and it worked fine. Now, it gives me a run time error '380' "could not set the value property. Invalid property rule". However, if I select the item in the combobox and have the combobox.value returned (e.g., msgbox(Combobox.value)), it shows the value I am trying to select. So the value is there but for some reason I can't select the item using the code with VBA. I've tried changing the variable type of the value I'm trying to set to the combobo to variant and integer but still the same problem. Help is greatly appreciated!