Posts by Eric Golf

    Re: Control Order Of Class Object Events


    Hi,


    1. As far as I can tell from what you have written object_1 & object_2 are reacting to the same event so there are no multiple like events to order?


    2. You say

    Quote

    The objects are entirely independent; they know nothing about each other.

    if this is the case then they cannot wait or go before something else they do not know about?


    3. Presuming both the above to be correct then to enable ordering of object_1, object_2 and possibly object_x's reaction to an event they either need to know each other exist or have them as part of a collection that is aware of all of them that then controls their reaction?


    Hope this makes sense and is of some help.


    Eric.

    Re: Creating Consecutive Numbers After Text


    Hi,


    Presuming your list starts in A1 and allowing for more than 99 occurences of any word then try the below in B1 & fill down.


    [COLOR="Blue"]=A1&TEXT(COUNTIF($A$1:A1,A1),"000")[/COLOR]


    Hope this helps,


    Eric.

    Re: Oleobject, Trigger Event, Not Firing


    Hi,


    I believe the click event for a button called ItemButton1 must be called ItemButton1_Click() therefor if this event exists and you then rename the button as ItemButton77 then it will no longer be associated with ItemButton1_Click()


    i.e. Your thought below is correct.


    Quote

    I think it has to do with the fact that a deleted button isn't replaced by a newly created button of the same name - that the new buttons don't absorb the same 'link' (?) to the click event.


    Don't understand why you are deleting all the buttons just to recreate them anyway?


    Eric

    Re: Disable & Re-Enable Fill Handle & Drag-and-Drop


    Hi,


    Dave - As Wigi mentions that code is already in there, though not as directly. e.g.


    Quote

    Private Sub Workbook_Deactivate()
    Call ToggleCutCopyAndPaste(True)
    End Sub




    ssimon2008 - Could this bit of code be being skipped due to how "the user exits Excel". e.g. If Excel is crashing or they close via the top right x?


    Possibly if you put a msgbox in near the start of the ToggleCutCopyAndPaste sub to check that it is being run when closing, this may help narrow down the cause?


    Eric


    Edit - Another possibility is that other code in the workbook is switching off events?

    Re: Track Changes


    Hi,


    Glad I could help and thanks for the feedback.


    Sorry, I cant help with the 'out of memory' error, not my area. Best if you start a new thread, and I suggest you use a more relevant title which states your problem. Remember to include the exact text & number of the error in the body text.


    Good luck.


    Eric

    Re: Track Changes


    Hi,


    Option Explicit needs to appear before any of the other code.


    Take:-




    and place above


    Quote

    Dim vOldVal 'Must be at top of module


    Untested but this should help


    Eric

    Re: Color All Cells With TRUE


    Hi,


    Grrim - Are you trying to highlight cells which contain an IF statement where the "logical_test" evaluates to TRUE.


    e.g. =IF(1=1,"FOO","BAR") should be Red
    whereas =IF(1=2,"BAR","FOO") would not be Red


    Just hoping to clarify things.


    Eric

    Re: Error Handling In Multiple Nested Loops


    Hi,


    Add in a boolean to show error found then if it is true use "Exit For"



    Sorry about the tabbing, seems to have been lost in copy / paste.


    Untested, but hope this helps.


    Eric


    Edit - Forgot to include you will have to reset ErrorFound to False near the beginning of the outside loop

    Re: Could Not Save All the Data and Formatting Recently Added


    Hi,


    There is a flaw in your count logic -



    Your check for cellCount = 1022 is inside an IF statement while the cellCount = cellCount + 1 is outside it. That is if at the point that cellCount = 1022 if foundCell.Validation.Value is NOT true then cellCount will not reset to 0 and never be 1022 again.


    Suggest either move the check for cellCount = 1022 outside the IF statement or change the check to


    Code
    If cellCount [B]>[/B] 1022 Then


    untested but hope this helps.


    Eric.

    Hi,


    I am copying a sheet out to a new workbook in order to save as a CSV file. I was wondering if there was a better way of doing this than the code below which uses ActiveWorkbook to determine the newly copied sheet.



    What I would like to do is replace


    Code
    sDD.Copy
       Set OutputFile = ActiveWorkbook


    with something like the commented out line


    Code
    Set OutputFile = sDD.Copy


    but I get a compile error: Expected Function or variable, when I try this, with .Copy being highlighted.


    Basically I am trying to avoid using ActiveWorkbook where possible.


    Hope this makes sense.


    Eric.