Posts by XenoCode

    You don't show a declaration for LastRow, so am assuming it is declared at the module level, if not then that code should error.

    But it is not a good idea to depend on widely scoped variables to define the target of an update. Much better to expressly go find the information just before the update using Find

    You can adapt the same logic to the Search. Much more efficient than looping.


    ... I have used Spy++ to find the button handle...

    However, the handle will change every time the program is executed.

    You need to use the FindWindow & FindWindowEx API functions to find the handles assigned to a currently executing application. FindWindow is used to find the application itself using either the Window caption or Class name, then FindWindowEx is used to find the child windows of that parent again using the Class name or Caption/text.

    You can use Spy to get the control names - these are static as they were assigned when the code was written and there's a good, simple intoduction to updating other applictions using FindWindow, FindWindowEx & SendMessage (which you'll also need) here:

    One thing to watch out for is the fact the control you want to update or click might not be a child of the userform, but in a container like a Frame - so you need to find the userform, then the topmost container, then other containers (if any) before you can find the control.

    You can create a local temporary table by naming it with a '#' as the first character

    SQL = "Select * into #TempTbl From...

    No way of checking code right now so this is untested, but a simple example of using SQL to return a recordset from an Excel.range

    strFile = ThisWorkbook.FullName
    strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strFile & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"  
    Set cn = CreateObject("ADODB.Connection")
    Set rs = CreateObject("ADODB.Recordset")  
    cn.Open strCon  
    strSQL = "SELECT * FROM [Sheet1$A7:A" & GrandTotal - 1)  

    The temp table will be dropped when the connection that created it is closed.

    Rough and ready - not meant as a solution just something to think about.

    With that number of values in the IN clause you are certainly hitting a limit somewhere - no idea where, but neither does Microsoft: See the 'Remarks' section;

    As you can run it in MS Query I guess the additional overhead of VBA with the ODBC driver is causing it to run out of resources/memory or something...

    You could try splitting the IN clause like ...Where (x in (1, 2, 3...) OR x in (1001, 1002, 1003...) but the usual suggestion is to create a temporary table containing the values and just use Where x in ("Select [field] from TempTbl") ...

    ...but seems to increment at random instead of when a 7 appears.....

    Most likely because when a 7 is 'rolled' and the cell is updated, the RANDBETWEEN function(s) will recalculate. RandBetween is a volatile function which means it will always recalc on every change when calculation is set to automatic.

    So every time a change is made to the worksheet new random numbers will be generated overwriting the '7' that triggered the code update in the first place.

    You can set the Cancel button 'TakeFocusOnClick' property to False to prevent the button pulling focus away from the text box and executing the code in the Exit event.

    However unloading the userform will cause the Exit event to fire if the textbox has focus when the 'X' (or the Cencel button) is clicked. The only way to avoid that is to have a form level variable which is set in the Userform QueryUnload event. The textbox Exit event then queries the value of this variable to check if the code should run, or not.

    As an experiment, add a new userform with 1 command button & 1 textbox keeping control names as the defaults but setting the button TakeFocusOnClick property to False, and paste the following code

    Edit the text in the textbox and use the Tab key to move focus to the button - you'll get the 'Textbox - Normal Exit' message. Tab back to the textbox and click the button with the mouse. You see the 'Form closing' message (Obviously you don't want a message - just as an example), Watch the order the events fire, the textbox Exit event is the last.