PDA

View Full Version : Solved: Code is clearing clipboard



mdmackillop
08-10-2007, 04:18 AM
I'm using the following code to highlight row and column headers. When I try to copy and paste, the clipboard is being emptied, so no Paste is available. Any thoughts?

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
Columns(3).Interior.ColorIndex = xlNone
Rows(2).Interior.ColorIndex = xlNone
Cells(Target.Row, 3).Interior.ColorIndex = 6
Cells(2, Target.Column).Interior.ColorIndex = 6
End Sub

rory
08-10-2007, 04:36 AM
I don't think you can have both - you could check Application.CutCopyMode and skip the colouring in if there's a cut/copy in progress?

rory
08-10-2007, 05:05 AM
On second thought, if you set a reference to the Microsoft Forms library, you could try something like this:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim objData As MSForms.DataObject
If Target.Cells.Count > 1 Then Exit Sub
If Application.CutCopyMode = xlCopy Or Application.CutCopyMode = xlCut Then
Set objData = New MSForms.DataObject
objData.GetFromClipboard
End If
Columns(3).Interior.ColorIndex = xlNone
Rows(2).Interior.ColorIndex = xlNone
Cells(Target.Row, 3).Interior.ColorIndex = 6
Cells(2, Target.Column).Interior.ColorIndex = 6
If Not objData Is Nothing Then objData.PutInClipboard
End Sub

mdmackillop
08-10-2007, 05:08 AM
Works a treat.
Thanks Rory.