Enable/Disable Insert related functions

This macro can be used to enable / dis-able insert and other relevant controls. TRUE is for enable, false is for diable

 

    Application.EnableEvents = True

    With Application

        .Caption = ""

       

        'Cut

        .CommandBars("Worksheet Menu Bar").Controls("Cut").Controls("Rows").Enabled = True

        .CommandBars("Row").Controls("Cut").Enabled = True

       

        .CommandBars("Column").Controls("Cut").Enabled = True

        .CommandBars("Worksheet Menu Bar").Controls("Cut").Controls("Columns").Enabled = True

       

        .CommandBars("Standard").Controls.Item("Cut").Enabled = True

        .CommandBars("Edit").Controls.Item("Cut").Enabled = True

        .CommandBars("Cell").Controls.Item("Cut").Enabled = True

 

        'Insert

        .CommandBars("Row").Controls("Insert").Enabled = True

        .CommandBars("Worksheet Menu Bar").Controls("Insert").Controls("Rows").Enabled = True

        .CommandBars("Worksheet Menu Bar").Controls("Insert...").Controls("Rows").Enabled = True

       

        .CommandBars("Column").Controls("Insert").Enabled = True

        .CommandBars("Worksheet Menu Bar").Controls("Insert...").Controls("Columns").Enabled = True

        .CommandBars("Worksheet Menu Bar").Controls("Insert").Controls("Columns").Enabled = True

 

        .CommandBars("Cell").Controls.Item("Insert...").Enabled = True

       

        'Delete

        .CommandBars("Row").Controls("Delete").Enabled = True

        .CommandBars("Worksheet Menu Bar").Controls("Delete").Controls("Rows").Enabled = True

        .CommandBars("Worksheet Menu Bar").Controls("Delete...").Controls("Rows").Enabled = True

 

        .CommandBars("Column").Controls("Delete").Enabled = True

        .CommandBars("Worksheet Menu Bar").Controls("Delete").Controls("Columns").Enabled = True

        .CommandBars("Worksheet Menu Bar").Controls("Delete...").Controls("Columns").Enabled = True

        .CommandBars("Cell").Controls.Item("Delete...").Enabled = True

        

        'Other Properties Set

        .WindowState = xlMaximized

        .DisplayFormulaBar = True

        .Calculation = xlAutomatic

        .CellDragAndDrop = True

        .EnableEvents = True

        .MaxChange = 0.001

    End With

 

Missing Native Excel functions

At times because of some macros some native excel functions would get disabled, if you would want to reset that, you need to do the following steps

 

Locate the users setting of excel, generally here is the path for my system

C:\Users\venu\AppData\Roaming\Microsoft\Excel

 

Delete all the contents of the folder

 

When excel is opened it will restore the settings.

 

IN my system Insert function button (ALT + I + R) was not working, and now its taken care.

 

 

Alt + I + R (Or) Alt + I + C insert function not working

You can apply the following macro to activate the nativity function of insert rows columns

 

Sub activateInsertfunctions()

Call Allow_InsertColumn(True)

Call Allow_InsertRow(True)

End Sub

 

Function Allow_InsertRow(Allow As Boolean)

Dim ctl As CommandBarControl

For Each ctl In Application.CommandBars.FindControls(ID:=296)

ctl.Enabled = Allow

Next ctl

End Function

 

Function Allow_InsertColumn(Allow As Boolean) 'Allow user to or prevent user from inserting columns

Dim ctl As CommandBarControl

For Each ctl In Application.CommandBars.FindControls(ID:=297)

ctl.Enabled = Allow

Next ctl

End Function

 

Credit: Jos Dijkstra