Prevent user to insert cells in sheet but allow him to insert whole rows or columns

Question

I use Excel 2010. Is there a way to prevent a user to insert cells in sheet (and shift the cell down or somewhere else) but allow him to insert whole rows or columns (in Excel 2010). A solution in VBA is also welcome. I have tried all the possible ways in Excel 2010 (and VBA) but could not find any solution. Thanks in advance.

Answer 1

Please, copy the next code in the sheet code module where insertion of cells is not allowed, but entire rows or columns is:

Option Explicit

Private rowsNo As Long, colsNo As Long

Private Sub Worksheet_Change(ByVal Target As Range)
   Dim lastR As Long, lastCol As Long
   lastR = Me.UsedRange.Rows.count
   lastCol = Me.UsedRange.Columns.count

   If Target.cells.count <> Me.Columns.count And Target.cells.count <> Me.Rows.count Then
        If rowsNo < Me.UsedRange.Rows.count Or _
                 colsNo < Me.UsedRange.Columns.count Then
            Application.EnableEvents = False
               Application.Undo                    'UnDo the inserting
            Application.EnableEvents = True
        End If
   Else
        If rowsNo < lastR Then
            rowsNo = rowsNo + Target.Rows.count    'increment rowsNo with the number of inserted rows
        End If
        If colsNo < lastCol Then
            colsNo = colsNo + Target.Columns.count 'increment colsNo with the number of inserted columns
        End If
   End If
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    rowsNo = Me.UsedRange.Rows.count
    colsNo = Me.UsedRange.Columns.count
End Sub

It will work only if (at least) a selection has been changed before making the insertion. The code can be adapted to also handle such a situation, but it will become much more complicated...