Excel "Application.OnRepeat" is buggy RRS feed

  • Question

  • Did ever anyone used tried to use Application.OnRepeat !?
    It seems to be buggy since ever ! - I tried it on 
    Office 2000 2003 and 2007 and everywhere the same problem:

    Application.OnRepeat "Repeat VB Procedure", "myReDoTest"

    It does nothing !!!

    Well in case redo was enabled the normal way and I run the code above

    • Redo will not be deactivated
    • when I click beside it, it still stays unchanged like let's say in the label
      "Redo Typing '123'"

    However there's one small change:

    • When I click on Redo now - nothing will happen(no redo / no error)

    Some hints/ideas on implement ya own undo.

    1. Create a second Workbook (with mysheet.copy)
      ->with (Windows(wb.Name).Visible = False you may hide it
      ->myUndo_WB.Saved = True you'll discard any changes / avoid that it get's saved to disk or the user get's asked
    2. create Undo_save() before the tab-change is apply
      -> that will copy/Backup the sheet in the undo workbook
      -> Pile up/store the sheet in a collection/stack object 
      -> install OnUndo
    3. for Undo just swap the sheets from ya current workbook and the undo workbook
      -> implementing SheetsSwap( WS_Src, ws,dest) maybe a little trickier than you might though at first
       if don't need a re-undo a SheetMove() will also do
      -> ReInstalling the UndoHandler like this

    Public Sub Undo_Do()
       ' ....   
       Application.OnUndo "Undo one more", "Undo_Do"
    End Sub

    might bring the next challenging suprise.

    After execution passes 'End Sub' the Undo- Button will be grey out again.
    (Oh dear what dumbass implemented that event handler ? Normally M$ are real smart ppl - however if it comes to VB there are beside nice and solid working stuff to really weird and bizarre stupidies. So as here. Deleting onUndo before the event might be okay - but to clean it afterwards again is nasty)

    Well that's my 'workaround':

       Application.OnTime Now, InstallOnUndo

    Public Sub InstallOnUndo()
       Application.OnUndo "Undo one more", "Undo_Do"
    End Sub

    Sunday, November 11, 2012 3:21 PM