Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Keep contents in clipboard
unlike several other posts about clipboard, which want to clear the
clipboard, or close the clipboard window, etc., my objective is the opposite. Problem: The way I describe it (which might not be technically accurate) Excel clears clipboard when a "intervening event" occurs after a paste and before the request for a paste. Perhaps because I have some VBA code (Worksheet Event Calculate, see below), I can't do consecutive pastes from an original copy. One paste is all I get out of the clipboard. Can I add anything in VBA to retain the last copied instance (may be many contiguous cells) in the clipboard? or get the most recent copied instance from the Office Clipboard back into the excel clipboard? Any other thoughts? Here is the only VBA code running in my spreadsheet: Private Sub Worksheet_Calculate() 'This code colors the cells in PROVIDER STATUS Column U (col 21 below) 'depending on the text in each cell (not case sensitive) 'If the column containing Provider Status moves because of inserted 'columns, then change "21" below to correspond to the new column Dim cell As Range Dim eRow As Long eRow = Cells(Rows.Count, 21).End(xlUp).Row Application.ScreenUpdating = False 'turn off triggering events during this routine Application.EnableEvents = False 'turn off screen refreshing during this routine For Each cell In Range(Cells(1, 21), Cells(eRow, 21)) Select Case UCase(cell.Value) 'set the test value to upper case Case "RED" cell.Interior.Color = vbRed cell.Font.Color = vbWhite cell.Font.Bold = True Case "YELLOW" cell.Interior.Color = vbYellow cell.Font.Bold = True Case "GREEN" cell.Interior.Color = RGB(46, 139, 87) cell.Font.Color = vbWhite cell.Font.Bold = True Case "BLUE" cell.Interior.Color = RGB(30, 144, 255) cell.Font.Color = vbWhite cell.Font.Bold = True Case "BLACK" cell.Interior.Color = vbBlack cell.Font.Color = vbWhite cell.Font.Bold = True Case "GREY" cell.Interior.Color = RGB(127, 127, 127) cell.Font.Color = vbWhite cell.Font.Bold = True Case "PURPLE" cell.Interior.Color = RGB(139, 0, 139) cell.Font.Color = vbWhite cell.Font.Bold = True 'If additional status text is used or addtl colors needed, insert before the next CASE ELSE stmt Case Else cell.Font.ColorIndex = xlColorIndexAutomatic cell.Interior.ColorIndex = xlColorIndexNone End Select Next Application.EnableEvents = True 'turn on event triggering Application.ScreenUpdating = True 'turn on screen refreshing End Sub Thanks in advance. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do I keep clipboard contents active in excel? | Excel Discussion (Misc queries) | |||
Clear clipboard contents | Excel Discussion (Misc queries) | |||
VBA: Answer YES to Contents of Clipboard | Excel Programming | |||
Set variable to clipboard contents? | Excel Programming | |||
Clipboard contents | Excel Programming |