-1

You know how sometimes you copy a cell in Excel, and then forget you copied it, and try to insert a new column/row, and you end up pasting the value from that cell over the entire new column/row? If you do, you know that this is super annoying!!! Especially when dealing with a big file or if the copied cell has a formula since it has crashed my excel many times.

Anyone have an idea of how to write a macro that warns before adding a new row/column if a cell is already copied into clipboard (i.e., would paste that cell's content in all cells of new column/row)?

branden
  • 40
  • 2
  • 10
  • 1
    I'm sure it's *poosible*, but that doesn't make it easy. There isn't an event that would get you anywhere near knowing if a row was about to be inserted. You'd likely have to hook the Excel menus. Compared to that, examining the clipboard state isn't nearly as difficult. – Comintern Sep 15 '16 at 12:42
  • I'm sure it'll involve using `Application.CutCopyMode <> False`. The event is the question. – Kevin Sep 15 '16 at 14:12
  • Obviously Ctrl+Z gets us there 90% of the time... the bigger issue is when dealing with a massive spreadsheet with 20k+ rows, copying a lookup formula cell, and then adding a new row => Crashed Excel – branden Sep 15 '16 at 14:15

1 Answers1

2

As far as a VBA solutions is concerned, below is a worksheet event.

It will not message the user before you paste what is in your Excel clipboard but will do so if you're about to select multiple rows/columns/cells. From a user experience standpoint, this is likely good enough.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Application.CutCopyMode <> False And Target.Cells.Count > 5000 Then
        MsgBox "Excel is in cut/copy mode..."
    End If
End Sub

This would need to go into each worksheet you want the warning to occur.

EDIT: OR! Use the below in ThisWorkBook

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    If Application.CutCopyMode <> False And Target.Cells.Count > 5000 Then
        MsgBox "Excel is in cut/copy mode..."
    End If
End Sub

EDIT2: The above only works on the active workbook. For using it in a personal workbook, Pearson did a good job of explaining how something like this could work for all workbooks (even non macro files), here. The big change I expect is when they explain the below. I can't test it right now, but I expect Workbook_SheetSelectionChange(...) to be the thing to make it work.

Private XLApp As CExcelEvents

Private Sub Workbook_Open()
     Set XLApp = New CExcelEvents
End Sub
Community
  • 1
  • 1
Kevin
  • 453
  • 2
  • 10
  • This is great - Thanks! but does not seem to work in "personal" thisworkbook, only individual files - is there a way to get it to work in "personal" so it would apply to every file? – branden Sep 15 '16 at 14:49
  • It is possible to get events to pass from individual workbooks to personal.xlsm. Don't have the code off hand (at work). I'll add a link which can help explain how it'd work if I find it. Adding the link to Answer. – Kevin Sep 15 '16 at 14:53