6

I'm populating a spreadsheet with Database values. The cells that I fill data with, I want them to be read-only to the users. How can I do this?

Community
  • 1
  • 1
gizgok
  • 7,303
  • 21
  • 79
  • 124

5 Answers5

5

Depends on how you want to select the range. This is just one cell. If you go back to J1 and change the value, you should get prompted.

Private Sub Worksheet_Change(ByVal Target As Range)
  Range("J1").Select
  Selection.Locked = True
  ActiveSheet.Protect Contents:=True
  Range("K1").Select

End Sub

Cells are not locked until the worksheet is protected. By default all cells are set to Locked, so you'll have to unlock cells you want users to be able to change.

JeffO
  • 7,957
  • 3
  • 44
  • 53
  • If I want to make this work for a rang eof cells,How can I do that.Pardon my lack of knowledge,I'm new to programming. – gizgok Aug 24 '10 at 19:59
  • This is making all cells readonly..am I making some mistake. Private Sub Worksheet_Change(cellrange As String) Range(cellrange).Select Selection.Locked = True ActiveSheet.Protect Contents:=True End Sub – gizgok Aug 25 '10 at 11:08
  • Tried this code but other cells not in the defined range are also made readonly. How can we restrict the readonly feature to exactly the range J1? – ibexy Dec 01 '18 at 15:34
5

In these circumstances I often find the best way is to lock the sheet, but only for the user by using the UserInterfaceOnly argument which still allows unrestricted programmatic interaction with the sheet.

ActiveSheet.ProtectUser InterfaceOnly:=True

There are various other arguments that can be set that will still allow the user to filter, sort etc. should this be required, the Help file has a full list.

Lunatik
  • 3,838
  • 6
  • 37
  • 52
1

You can try protecting cells. Or you can code it yourself using a SelectionChange handle event... : 

   Private Sub Worksheet_SelectionChange(ByVal Target As Range)
      If Not Intersect(Selection, Range("A24:A50")) Is Nothing Then
        Range("B1").Select
      End If
    End Sub
froadie
  • 79,995
  • 75
  • 166
  • 235
0

This page offers a small sub to protect a range of cells.

Excerpt

Private Sub Worksheet_SelectionChange(ByVal Target As Range) 
    If Not Intersect(Target, Range("H1:H10")) Is Nothing Then 
        Target.Offset(0, 1).Select 
        MsgBox "you may not enter that cell" 
    End If 
End Sub

To protect H1:H10. When the user wants to change a cell within H1:H10 a warning message says that the cell may not be modified, and the neighbor cell in the next column (I) is selected instead.

Déjà vu
  • 28,223
  • 6
  • 72
  • 100
0

In excel you can go to the Protection menu and specify which cells would require a password to modify. You can specify multiple ranges as well.

Hope this is what you were looking for.

DeaconnFrost
  • 101
  • 3