How to disable ability to insert row and column in Excel?
Normally, we can insert rows and columns between existing data quickly and easily, if you prevent others inserting columns or rows in a worksheet, may be you can protect sheet to disable the insert rows or columns function. But, how could you disable the insert row and column function without protecting sheet?
Prevent others inserting rows and columns with VBA code
Prevent others inserting rows and columns with VBA code
The following VBA code can help you to disable the inserting row and column function in Excel workbook, please do as this:
1. Press Alt + F11 to open a Microsoft Visual Basic for Application window.
2. Click Insert > Module to open a Module window, then copy the following VBA to the window.
VBA code: prevent inserting rows and columns:
Sub NoInsert()
'Updateby Extendoffice
Dim I As Integer
Dim cbStr As String
Dim cbCtrl As CommandBarControl
Application.ScreenUpdating = False
For I = 1 To 2
If I = 1 Then
cbStr = "row"
Else
cbStr = "column"
End If
For Each cbCtrl In Application.CommandBars(cbStr).Controls
If cbCtrl.ID = 3183 Then
cbCtrl.Enabled = False
End If
Next
Next
Application.ScreenUpdating = True
End Sub
3. Press F5 key to run this code, now when you right click to going to insert rows or columns, the Insert option cannot be applied in the context menu. See screenshot:
Notes:
1. This VBA will disable the Insert function in the context menu in all workbooks when you right click to insert rows or columns.
2. To restore the Insert command in the context menu, please apply the following code:
Sub NoInsert()
'Updateby Extendoffice
Dim I As Integer
Dim cbStr As String
Dim cbCtrl As CommandBarControl
Application.ScreenUpdating = True
For I = 1 To 2
If I = 1 Then
cbStr = "row"
Else
cbStr = "column"
End If
For Each cbCtrl In Application.CommandBars(cbStr).Controls
If cbCtrl.ID = 3183 Then
cbCtrl.Enabled = True
End If
Next
Next
Application.ScreenUpdating = True
End Sub
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
