Results 1 to 2 of 2
  1. #1
    Star Lounger
    Join Date
    Jun 2006
    Posts
    88
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Paste Special Macro (2000 (SR-1))

    Currently, the attached adds "Dawn Paste" to the right click menu on open and removes it upon closing. But the function itself, I'm struggling with.

    I would like the function to do a Paste special "Values & Transpose".

    Also, is there any way to temprorarily edit the shortcut menu to only include the standard 'Copy' and then 'Dawn Paste' and remove the other standard buttons?

    Help?!

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Paste Special Macro (2000 (SR-1))

    Here is the macro (created by recording a macro, with only minimal editing):

    Sub DawnPaste()
    Selection.PasteSpecial Paste:=xlPasteValues, Transpose:=True
    End Sub

    And here are versions of the Workbook event procedures that hide/show controls:

    Sub Workbook_Open()
    Dim ctl As CommandBarControl
    For Each ctl In Application.CommandBars("Cell").Controls
    ctl.Visible = (ctl.ID = 19)
    Next ctl
    With Application.CommandBars("Cell").Controls.Add(Type: =msoControlButton)
    .Caption = "Dawn Paste"
    .OnAction = "DawnPaste"
    .Visible = True
    End With
    End Sub

    Sub Workbook_BeforeClose(Cancel As Boolean)
    Dim ctl As CommandBarControl
    On Error Resume Next
    Application.CommandBars("Cell").Controls("Dawn Paste").Delete
    For Each ctl In Application.CommandBars("Cell").Controls
    ctl.Visible = True
    Next ctl
    End Sub

Posting Permissions

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