Resetting CutCopyMode

**foxguy** · 06-07-2009, 03:38 PM

On occasion I manually copy a selection. When I then select another cell, my SelectionChange event changes the Font.ColorIndex of a button (this is by design), which sets the CutCopyMode to false, which removes the copy border, so I can't paste the clipboard.

Resetting the CutCopyMode back to xlCopy does not replace the copy border.

I cannot find any way to replace the copy border so I can paste it.

Is there some way to prevent the copy border from being removed? Or resetting it?

**Leith Ross** · 06-07-2009, 04:08 PM

Hello Foxguy,

You should post your workbook.

**foxguy** · 06-07-2009, 06:04 PM

Here's a simple workbook, to demonstrate the problem.

**Leith Ross** · 06-07-2009, 07:28 PM

Hello Foxguy,

Here is the updated macro code. It has already been installed in the attached workbook.

Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

  Dim CopyCells As Boolean
  Dim oButton As Object, lColorIndex As XlColorIndex
  Static PrevSelection As Range
    
    Application.EnableEvents = False

    If Application.CutCopyMode Then
       CopyCells = True
    End If
    
    On Error Resume Next
    Set oButton = Sheet1.Buttons("Button 1")
    '
    If Not oButton Is Nothing Then
         With oButton
            lColorIndex = .Font.ColorIndex
            .Font.ColorIndex = IIf(lColorIndex = 3, 15, 3)
         End With
    End If
    
   If CopyCells Then
       PrevSelection.Copy
    Else
       Set PrevSelection = Target
    End If
    
    Application.EnableEvents = True

End Sub

**foxguy** · 06-07-2009, 07:38 PM

Leith;

Nice idea. I never thought of using a static variable.
I take it that you can't think of any way to read the copy border?

**Leith Ross** · 06-07-2009, 07:59 PM

Hello Foxguy,

Sorry I don't follow you on this...

I take it that you can't think of any way to read the copy border?

The copy border or "marching ants" as it is sometimes called is present after you you type CTRL+C. Did you want to remove the border after the data is pasted?

**foxguy** · 06-07-2009, 08:28 PM

Did you want to remove the border after the data is pasted?

No, I know that I can set cutcopymode=False to do that. I was hoping to be able to read the range that is surrounded by the marching ants and then reset it after they were removed by the macro. Then I wouldn't have to use a static variable. I just have an unreasonable fear of static variables.