Hi,

I have following code in my workbook:

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Call Excel.ActiveSheet.UsedRange.ClearContents
Call Excel.ActiveSheet.UsedRange.ClearFormats
Excel.ActiveSheet.Cells(1, 1).Value = "test"
End Sub

When I copy a value from Sheet1 and try to Paste it on Sheet2 the paste
doesn't work. For some reason both ClearContents and ClearFormats clear
the clipboard as well.

Does anybody know a workaround for this so that the clipboard is still
available?

Thx,
Reto

Write clearing-contents/format code before copying and try...


--
ilyaskazi
------------------------------------------------------------------------
ilyaskazi's Profile: http://www.excelforum.com/member.php...o&userid=23969
View this thread: http://www.excelforum.com/showthread...hreadid=493644

Unfortunately I cannot do this. On a tab change I want to clear the
entire sheet (content and formatting) and intialize cells using VBA. So
no change in sequence possible. At the moment the only way I see is
that I manually must clean the sheet. This way it won't clear the
clipboard.

ws.UsedRange.Value = ""
ws.UsedRange.Interior.ColorIndex = xlNone
.......

Regards,
Reto
www.collaboral.com

Since I'm dependent on calling ClearContents and ClearFormats I found a
pretty nice solution. All what I do is I remember the clipboard
manually. I store it when a sheet gets deactivated and read it back
after the new worksheet was activated. On save I clear the clipboard.

Works perfect!

Cheers,
Reto
www.collaboral.com

Private Declare Function CloseClipboard Lib "user32" () As Long
Private Declare Function EmptyClipboard Lib "user32" () As Long
Private Declare Function OpenClipboard Lib "user32" (ByVal hwnd As
Long) As Long

Public Sub GetClipboard()
On Error Resume Next

m_clipboard.GetFromClipboard
m_clip = m_clipboard.GetText
End Sub

Public Sub SetClipboard()
On Error Resume Next

m_clipboard.SetText m_clip
m_clipboard.PutInClipboard
End Sub

Public Sub ClearClipboard()
On Error Resume Next

OpenClipboard 0&
EmptyClipboard
CloseClipboard
End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
' Clear the clipboard to maintain the standard behavior of the
Excel clipboard which gets cleared after a save
ClearClipboard
End Sub

Private Sub Workbook_SheetActivate(ByVal Target As Object)
Call Excel.ActiveSheet.UsedRange.ClearContents
Call Excel.ActiveSheet.UsedRange.ClearFormats

SetClipboard
End Sub

Private Sub Workbook_SheetDeactivate(ByVal Target As Object)
GetClipboard
End Sub

a little bug fix...


Public Sub SetClipboard()
On Error Resume Next

If m_clip < "" Then
m_clipboard.SetText m_clip
m_clipboard.PutInClipboard
End If
End Sub

Public Sub ClearClipboard()
On Error Resume Next

m_clip = ""
OpenClipboard 0&
EmptyClipboard
CloseClipboard
End Sub

a little bug fix...


Public Sub SetClipboard()
On Error Resume Next

If m_clip < "" Then
m_clipboard.SetText m_clip
m_clipboard.PutInClipboard
End If
End Sub

Public Sub ClearClipboard()
On Error Resume Next

m_clip = ""
OpenClipboard 0&
EmptyClipboard
CloseClipboard
End Sub