My Assistant
Site Message
(Message will auto close in 2 seconds)
  |
 VBA Code Prevents Pasting
|
|
 Feb 15 2007, 01:27 PM
Post
#1
|
|
|
UtterAccess Enthusiast Posts: 87 From: Ft Worth TX  |
I have a worksheet with related data on each Odd-Even pair of rows. When clicking anywhere on this sheet, the paired row is highlighted (bold, blue font) by means of the following VBA code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range) Me.Cells.Font.Bold = False Me.Cells.Font.Color = vbBlack ActiveCell.EntireRow.Font.Bold = True ActiveCell.EntireRow.Font.Color = vbBlue If Target.Row Mod 2 = 0 Then Target.Offset(-1).Resize(2).EntireRow.Font.Bold = True Target.Offset(-1).Resize(2).EntireRow.Font.Color = vbBlue Else Target.Resize(2).EntireRow.Font.Bold = True Target.Resize(2).EntireRow.Font.Color = vbBlue End If End Sub The Copy button works on this sheet, but the Paste button is disabled. If I delete the code, the Paste button is enabled. Why is the code disabling Paste, and how do I fix this? |
 |
 
|
|
Feb 15 2007, 02:54 PM
Post
#2
|
|
|
Retired Moderator Posts: 10,959 From: Prague,CZ / Kiev,UA |
Hello,
easy question, not so easy answer. For example I am struggling with Change event disabling Undo and I still didn't find a good workaround. Simply the code in the event, as soon as it executes the first formatting, it wipes the clipboard. Your only (???) option may be to build a custom menu (I attached a sample workbook) This goes into a normal module: CODE [color="blue"]Public[/color] [color="blue"]Const[/color] CB_TITLE [color="blue"]As[/color] [color="blue"]String[/color] = "My Menu" [color="green"]'[/color] [color="blue"]Public[/color] [color="blue"]Sub[/color] CreateToolbar() [color="green"]'[/color] [color="blue"]On[/color] [color="blue"]Error[/color] [color="blue"]Resume[/color] [color="blue"]Next[/color] CommandBars(CB_TITLE).Delete [color="blue"]On[/color] [color="blue"]Error[/color] [color="blue"]GoTo[/color] 0 [color="green"]'[/color] [color="blue"]With[/color] CommandBars.Add(Name:=CB_TITLE) [color="blue"]With[/color] .Controls.Add(Type:=msoControlButton) .OnAction = "CustomCopy" .FaceId = 19 .TooltipText = "Copy this" [color="blue"]End[/color] [color="blue"]With[/color] [color="blue"]With[/color] .Controls.Add(Type:=msoControlButton) .OnAction = "CustomPaste" .FaceId = 22 .TooltipText = "Paste" [color="blue"]End[/color] [color="blue"]With[/color] .Visible = [color="blue"]True[/color] [color="blue"]End[/color] [color="blue"]With[/color] [color="green"]'[/color] [color="blue"]End[/color] [color="blue"]Sub[/color] [color="green"]'[/color] [color="blue"]Sub[/color] CustomCopy() Application.EnableEvents = [color="blue"]False[/color] [color="blue"]On[/color] [color="blue"]Error[/color] [color="blue"]GoTo[/color] Error_Handler Selection.Copy ExitHere: [color="blue"]Exit[/color] [color="blue"]Sub[/color] Error_Handler: MsgBox Err.Description & [color="blue"]String[/color](2, vbLf) & "The Copy method failed." Application.EnableEvents = [color="blue"]True[/color] [color="blue"]GoTo[/color] ExitHere [color="blue"]End[/color] [color="blue"]Sub[/color] [color="green"]'[/color] [color="blue"]Sub[/color] CustomPaste() [color="blue"]With[/color] Application .EnableEvents = [color="blue"]True[/color] .Dialogs(xlDialogPasteSpecial).Show [color="blue"]End[/color] [color="blue"]With[/color] [color="blue"]End[/color] [color="blue"]Sub[/color] And this goes into ThisWorkbook class module: (You might want to use also Activate and Deactivate events) CODE [color="blue"]Private[/color] [color="blue"]Sub[/color] Workbook_BeforeClose(Cancel [color="blue"]As[/color] [color="blue"]Boolean[/color]) Application.EnableEvents = [color="blue"]True[/color] [color="green"]'On Error Resume Next[/color] Application.CommandBars(CB_TITLE).Delete [color="blue"]End[/color] [color="blue"]Sub[/color] [color="blue"]Private[/color] [color="blue"]Sub[/color] Workbook_Open() CreateToolbar [color="blue"]End[/color] [color="blue"]Sub[/color] (attachment) Martin
Attached File(s)
|
|
|
|
 |
|
Jump To Forum:
|
Go to Top · Lo-Fi Version | Time is now: 20th June 2013 - 06:35 AM |