Unlocked Cell Needs to be Protected

[VB_Noob]

Hi All,

I have bunch of cells that contains formula and its value changes based on user input of other cells. Therefore, I will not be able to lock those formula active cells.

How can I disable select of those formulated cells using VBA? I've tried to use the protect sheet feature within Excel. But that feature only works with "locked" cell.

Please help! Thanks

 

[Chihiro]

Wait, cells contain formula? Then you should by all means "Lock" the cell. While user won't be able to edit the data in locked cell, value will update whenever referenced cell is updated.

"Locked" does not mean, cell values are locked. But rather, it is locked from manual data input. Formula will automatically recalculate (as long as you have calculation mode set to auto).

 

[VB_Noob]

Sorry. I was not clear in my first post. Basically I have VBA writing data to those cells. So it gives me error message if I lock those cells. So the locking those cells will not work with my VBA.

Anyway I am trying to prevent the cells being selected by the user using VBA.

I have the following simple codes below which I am preventing those two cells being selected by the user. But how can I change the codes that will prevent multiple cells being selected by the user? Instead of single cell "A1" or "B1". Can I change it to "A1:A20"? I tried and it doesn't work.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address = "$A$1" Or Target.Address = "$H$3" Then Target.Offset(1, 0).Select
End Sub

 

[Chihiro]

You can just use vba to unprotect before updating cell and protect after it's done and keep cells locked.

It'd cause far less issue than trying to prevent user from selecting range.