Replace All fires WS_Change for every cell

[Paul_Hossler]

I have a WS_Change event to upper case entered text (does other things, but UC is easiest to demo)

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    
    Dim r As Range, c As Range
    
    If Intersect(Target, Range("A:B")) Is Nothing Then Exit Sub
    
    Set r = Intersect(Target, Target.Parent.UsedRange, Target.Parent.Range("A:B"))
    
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    
    For Each c In r.Cells
        c.Value = UCase(c.Value)
    
'        MsgBox "Stopped"
    
    Next

    Application.ScreenUpdating = True
    Application.EnableEvents = True

End Sub

If I do a [Replace All] to update a number of cells, the [Replace All] seems to do the global replace (screen updating is off), BUT then the event seems to do one cell as a time (the MsgBox keeps popping up) and there's screen flicker and it runs slow

Is there any way get the event to not do one cell at time after a [Replace All] ?

Paul

[Kenneth Hobs]

Set the application's calculation mode to manual and then back to what it was? See: http://vbaexpress.com/kb/getarticle.php?kb_id=1035

[Paul_Hossler]

thanks Ken, but that doesn't seem to be the issue

It appears that a [Replace All] files the WS_Change event of each cell being changed (which is reasonable I suppose)

In the attachment if you select Cols A and B, and then a manual control-H to replace all, each cell is changed and the event fires for each cell.

In my F&R macro I can control it, but a control-H is under Excel's control and the events get turned on and off for each cell

Some times there can be 1000's of affected cells.

Before the user doing a F&R EnableEvents = True and ScreenUpdating = True, so a UI initiated F&R starts in that state. I tried to replace or intercept the built-in F&R command to 'wrap' it and turn then off, but could get it working

Hope this is clearer

Paul

[snb]

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("A:B")) Is Nothing Then Exit Sub
    If Selection.Address = Cells(1).CurrentRegion.Resize(, 2).Address Then Exit Sub
    
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    
    For Each c In r.Intersect(Target, Cells(1).CurrentRegion.Resize(, 2)).SpecialCells(2, 1)
        c.Value = UCase(c.Value)
    Next
    Application.ScreenUpdating = True
    Application.EnableEvents = True
End Sub

The reamarkable thing is: the replacement takes place without triggering the worksheet_change event. After the replacement is complete the change-event will be triggered as many times as replacements have taken place.

[Paul_Hossler]

The remarkable thing is: the replacement takes place without triggering the worksheet_change event. After the replacement is complete the change-event will be triggered as many times as replacements have taken place.

Yea - I'd say Wierd.

With ScreenUpdating=False you can see that the WS is updated, and then the event fires for each cell that was updated

That's what was confusing me

Thanks for confirming

Paul

[Kenneth Hobs]

I'll have to think about this one. This is sort of interesting.

Private Sub Worksheet_Change(ByVal Target As Range)
    Debug.Print Target.Address
    
    Dim r As Range, c As Range
    
    If Intersect(Target, Range("A:B")) Is Nothing Then Exit Sub
    
    Set r = Intersect(Target, Target.Parent.UsedRange, Target.Parent.Range("A:B"))
    
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    
    For Each c In r.Cells
        c.Value = UCase(c.Value)
    Next

    Application.ScreenUpdating = True
    Application.EnableEvents = True
End Sub

[Paul_Hossler]

Ken -- any insights?

Sometimes I have to F&R all to 100's of lines, and it really takes a lot longer and the screen flicker is annoying

Paul

[Aflatoon]

I don't think there's much you can do besides intercepting the commands yourself. There's nothing the event can do about being called multiple times - other than maybe turn events off, then use OnTime to reinstate them a second or so later.

[Paul_Hossler]

I don't think there's much you can do besides intercepting the commands yourself.

I actually thought about that since I'd seen code for replacing Copy/Paste.

I gave up because there's so many ways a F&R can be started.

If you have any ideas, be glad to hear them

Paul

[Aflatoon]

You'd need Onkey for the Ctrl+H shortcut and the RibbonX would be something like:

<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui">	<commands>		<command idMso="ReplaceDialog" onAction="myReplace"/>	</commands></customUI>

which I think would override all Replace buttons, wherever they are located.

[SamT]

I gave up because there's so many ways a F&R can be started.

What besides the Edit Menu and Ctrl+H ?

I can't find my CommandBars Reference workbook, but I thimk that you wold only have to change the "command" for the Replace Button on the Edit CommandBar to call your own F&R code.

[Paul_Hossler]

What besides the Edit Menu and Ctrl+H ?

I can't find my CommandBars Reference workbook, but I think that you would only have to change the "command" for the Replace Button on the Edit CommandBar to call your own F&R code.

@SamT -- Ok, Ok, Ok .. so maybe it's only 2 places

If you can find theCommandBars reference AND a bit of code that would be useful ... please

@Aflatoon -- I sort of considered the CustomUI approach, but didn't do too much experimenting

1. Replace Excel's with my Sub in the CustomUI
2. If on the correct sheet, .EnableEvents = False, show Application.Dialogs(xlDialogFormulaReplace), do it, and turn events on afterwards
3. If not on the correct sheet, just do Application.Dialogs(xlDialogFormulaReplace)

The Application.Dialogs(xlDialogFormulaReplace) dialog is different than the Control-H and the Excel version (no [Options] button) but I guess I could live with that

Sub MyFR()
    Dim dglFindReplace As Dialog

    'find_text, replace_text, look_at, look_by, active_cell, match_case, match_byte

    Set dglFindReplace = Application.Dialogs(xlDialogFormulaReplace)
    dglFindReplace.Show
End Sub

Thoughts?

Paul

PS: still doesn't explain Excel's Replacing a number of cells, and then firing the event for each cell, one at a time

[Aflatoon]

If you wanted, you could probably set a windows timer in the Ribbon command callback that disables events, then waits for the replace dialog and sets another timer waiting for the message box that appears after the replace operation (or hook the message box), to then reset events. Could be fun.

I can ask why it behaves that way but I'd be surprised if I got an answer to be honest.

[ZVI]

Put any volatile function in the cell like =TODAY() and try this code:

Dim IsOk As Boolean
 
Private Sub Worksheet_Calculate()
  IsOk = False
End Sub
 
Private Sub Worksheet_Change(ByVal Target As Range)
 
  If IsOk Then Exit Sub
 
  ' Your code is here
  ' ...
 
  IsOk = True
 
End Sub

[SamT]

@SamT -- Ok, Ok, Ok .. so maybe it's only 2 places:*)

Dang, I thought I was gonna learn a secret

[snb]

All macros in the codemodule of sheet1:

Sub M_snb()
    Application.OnKey "^h", "sheet1.vervang"
End Sub

Sub vervang()
    Application.EnableEvents = False

    Selection.Name = "snb_002"
    [snb_002] = [index(substitute(snb_002,"qq","zz"),)]
    ThisWorkbook.Names("snb_002").Delete

    Application.EnableEvents = True
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
    MsgBox "kkk"
End Sub

Without the application.enableevents=false it triggers the worksheet_change event only once.

[Paul_Hossler]

@ZVI & snb -- thanks but I don't think that's getting to the problem

Per #1, I have a WS_Change event to make inputs into upper case. That works fine (paste in or control-enter, it runs on cell at a time also)

If the user does a F&R on that sheet, the cells get updated and THEN the event fires for every cell that was changed, one cell at a time

events off, make upper case, events on

repeat for each cell

Paul

[snb]

I think it does: it replaces the values in a selected range without triggering the change event.
The result however will be exactly the same as that resulting form using the builtin find/replace facility.

[Paul_Hossler]

The result however will be exactly the same as that resulting form using the builtin find/replace facility.

That part I understand but the F&R parameters are not in the code.

The issue is that with the WS_Change event loaded and active, when the user uses the ribbon button or Ctrl-H to enter "Find: cat, Replace: dog, [Replace All] then the changes are applied apparently all at once, and then the event fires for each cell

Changing using keyboard one or two cells is tolerable, but a [Replace All] with 100's or 1000's to update takes a long time

the cells get updated and THEN the event fires for every cell that was changed, one cell at a time
events off, make upper case, events on
repeat for each cell

Paul

[snb]

I posted the code to illustrate how to avoid the triggering of the change event.

Sub vervang()
    Application.EnableEvents = False
    
    c00 = InputBox("Find", "Find & Replace")
    c01 = InputBox("Replace " & c00 & " by", "Find & Replace")
    Selection = Evaluate("index(substitute(" & selection.address & ",""" & c00 & """,""" & c01 & """),)")
     
    Application.EnableEvents = True
End Sub

I wasn't able to retrieve the dialogs(130) arguments.