Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: Disabling items on the paste special menu

  1. #1
    New Member
    Join Date
    Mar 2002
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Can anyone tell me what the macro codes would be to disable all of the items on the paste special menu except the Values selection. When users cut and paste they overwrite my conditional formats.

  2. #2
    Board Regular
    Join Date
    Mar 2002
    Location
    Manchester, UK
    Posts
    95
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Do you really need to keep the single option?
    The easiest way would be to remove Paste Special completely:


    Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Application.CommandBars("Cell").Reset
    End Sub

    Private Sub Workbook_Open()
    Application.CommandBars("Cell").Controls("Paste Special...").Delete
    End Sub

  3. #3
    New Member
    Join Date
    Mar 2002
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-04-29 15:40, Mr Nick wrote:
    Do you really need to keep the single option?
    The easiest way would be to remove Paste Special completely:


    Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Application.CommandBars("Cell").Reset
    End Sub

    Private Sub Workbook_Open()
    Application.CommandBars("Cell").Controls("Paste Special...").Delete
    End Sub
    Thanks for your reply.
    Unfortunately the users demand a copy and paste function. I only want to protect my conditional formats so I thought that only giving the users a paste of only the values from the paste special would be the way to go. I already had all copy and paste functions removed and it almost caused a riot. If you have any other information that would be helpful please let me know.


    Thanks

  4. #4
    New Member
    Join Date
    Apr 2002
    Posts
    15
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I have the same issue. I can get to "paste special" within the code but I can't figure out how to disable the radio controls. Anyone here have any idea...This is what I have so far

    Set X = CommandBars("Worksheet Menu Bar").Controls("Edit").Controls("Paste &Special...")
    X.Controls("&Formulas").Checked = False

    but the checked = false blows up.

  5. #5
    Board Regular
    Join Date
    Mar 2002
    Posts
    363
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-04-29 15:29, kriccio wrote:
    Can anyone tell me what the macro codes would be to disable all of the items on the paste special menu except the Values selection. When users cut and paste they overwrite my conditional formats.
    I think you would have to create a user form with only the values selection then call up that user form when Paste Special is clicked.
    It's never too late to learn something new.

    Ricky

  6. #6
    New Member
    Join Date
    Apr 2002
    Posts
    48
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I'm not sure about how to disable the paste and paste special functions, but once you do that you could add the Paste Values function to the cell shortcut menu like this:

    Dim CBarItem As Object
    Set CBarItem = Application.CommandBars("Cell").Controls.Add(msoControlButton, 370, , 5, True)

    An incomplete answer, but I hope it helps.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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