Posts by Craig Ottley

    Re: Automatically Run Macro After Pivot Table Refresh

    Are you actually trying to run this piece of code with the color tag?

    If so try changing it to

    Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable) 
         Application.Run "'1pivotbasedon2.xlsm'!test2"

    Or you can call the procedure

    Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable) 
         Call ModuleName.test2

    Re: Huge SQL Statement, VBA, Timeouts

    You can change the Timeout for the query but it requires you to change the Registry... Its something like HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\ODBC\QueryTimeout depending on how you're connecting to the data source.

    If you've got such a big sql statement you should consider creating a stored procedure in the database. Big statements are never good calling from excel, you're better off breaking it down into chunks in SQL Server

    This guy just doesn't listen to you when you ask to be removed from their mailing list!!

    [email protected]

    Good Morning,

    Thank you for your recent enquiry on Money Supermarket, we hope the service we provided was as you hoped and your insurance needs were met.

    We are now offering all Nunn McCreesh clients and referrals a free no obligation Pension review focusing on old and frozen pensions.

    A combination of poor advice and a lack of understanding in this complex area means that many people are paying way over the odds in charges and suffer from poor fund performance.

    Also, There may be money owed to you that you don't even know about relating to the governments S2P. Did you opt out of SERPS?

    Please reply with a contact number and the best time for me to call and we can have a quick 5 minute chat.

    Best Regards

    Phillip A Nunn | Senior Partner | Nunn McCreesh

    Website -

    T: 0161 234 6506 | M: 07538 280547 | F: 0161 210 2983

    Nunn McCreesh is an Appointed Representative of Sage Financial Services Limited which is authorised and regulated by the Financial Services Authority

    Re: Hide Cell in selection if not in listbox


    You could use the Scripting Dictionary to check if the value exists before you hide the row.

    Try the following example.



    Re: Autorecover Location

    You can normally set this in VBA.... Try something along the lines of the following, but make sure it's a valid path as excel will test the path.



    Re: Remote Desktop - vba pauses when "locked" or connection closed

    Hmmmm, if you're stuck with the task I'd start looking at different options.

    Try seeing if you can RDP, start running the add-in and just disconnecting the connection (IE Not logging off) reconnecting to see what happens.
    You might have some kind of Group Policy that interferes with the way RDP is working, is the user a local admin? Start checking things like this...

    There are come other tricks you can do by creating registry entries where you add a local user to the machine but this exposes the password for that user. You can then have some kind of VNC client watch the machine?

    Without being there I'm not that useful to you sorry.

    Do let me know how you get on though.


    Re: Convert Numbers to Words/Text - Explanation Needed

    Hi DohNuts,

    Welcome to OzGrid! The length variable is optionsal. See Object Explorer


    Optional; Variant (Long). Number of characters to replace. If omitted, all of string is used.

    As for the double zero, that's probably something to do with data types on conversion to preserve formatting.