0
votes

I would like to stop users from selecting specific columns in a worksheet (say columns H:S) without having to protect the sheet? I'm thinking this could be achieved though VBA i.e. if the user selects any cells in the specified columns, the cursor defaults to cell A1.

I have a table set up with some formulas in it which I would like to prevent being changed/overwritten. If I try to do this using locked cells and protecting the worksheet, the table stops functioning (i.e. when adding data below the table, it doesn't get included in the table). If the worksheet isn't protected, the table functions as normal, allowing me to enter data that gets added to the table automatically.

Edit:

Solution provided below by JvdD, but with my tweaks:

 Private Sub Worksheet_SelectionChange(ByVal Target As Range)
 If ActiveCell.Column >= 8 And ActiveCell.Column <= 20 Then 
 ActiveSheet.Range("A1").Select
 End Sub
2

2 Answers

2
votes

Here you go, code for the entire workbook:

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)

If ActiveCell.Column > 8 And ActiveCell.Column < 20 Then Activesheet.Range("A1").Select

End Sub
1
votes

Should really use the Target object like below

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target  
As Range)
Set Sh = ActiveSheet
If Target.Column >= 8 And Target.Column <= 20 Then Sh.Range("A1").Select
End Sub