UtterAccess Discussion Forums > VBA Code Prevents Pasting

UtterAccess Discussion Forums > Microsoft® Office > Microsoft Excel

kespicer

Feb 15 2007, 01:27 PM

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?

KingMartin

Feb 15 2007, 02:54 PM

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

This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.