+ Reply to Thread
Results 1 to 6 of 6

ClearContents or ClearFormats also clears Clipboard. How can I keep the clipboard?

  1. #1

    ClearContents or ClearFormats also clears Clipboard. How can I keep the clipboard?

    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


  2. #2
    Forum Contributor
    Join Date
    06-02-2005
    Location
    India
    MS-Off Ver
    2007
    Posts
    138
    Write clearing-contents/format code before copying and try...

  3. #3

    Re: ClearContents or ClearFormats also clears Clipboard. How can I keep the clipboard?

    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


  4. #4

    Re: ClearContents or ClearFormats also clears Clipboard. How can I keep the clipboard?

    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


  5. #5

    Re: ClearContents or ClearFormats also clears Clipboard. How can I keep the clipboard?

    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


  6. #6

    Re: ClearContents or ClearFormats also clears Clipboard. How can I keep the clipboard?

    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


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1