Suppresing Insert Row functionality

[John]

Hi,

Using the Change Event, how can i stop users inserting columns when the
Cellpointer is in column C please?

Thanks

John

 

[Mike H]

John,

I'm not sure about the worksheet_change event but it can be done with the
selection_change event by disbaling the insert rows option every time column
3 is the active column:-

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target
As Range)
If ActiveCell.Column = 3 Then
With Application.CommandBars("Worksheet Menu Bar").Controls("&Insert")
.Controls("&Rows").Enabled = False
End With
Else
With Application.CommandBars("Worksheet Menu Bar").Controls("&Insert")
.Controls("&Rows").Enabled = True
End With
End If
End Sub

Mike

 

[Mike H]

John,

Forgot the righ-click method of insert so add this:-

Private Sub Workbook_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target
As Range, Cancel As Boolean)
If ActiveCell.Column = 3 Then Cancel = True
End Sub

Mike