How to allow Autofill but disable direct editing for specific cells

[onurubu]

I am learning to make use of protected cells for the spreadsheet of my research project. Currently through using protected cells and protecting the sheet, I can disable editing of any kind in specific cells.
However I was wondering if there was a way to disable directly typing into cells, yet still allow for me to autofill formulas from previous entries in the column as well. Currently protecting the cell disables autofilling formulas as well.

[joshua4]

As a possiblity, to protect from bad entries you could use a macro to check that an entry exists already in the column and give a warning if it does not, rather than protect the cells in that column. You would attach that macro as an event handler for the sheet Content Changed event (right-click sheet’s tab, Sheet Events…, click Content Changed, click Macro…, etc.).

Here’s a quick macro that does that with no bells or whistles:

Option Explicit

Sub CheckEntries
	Dim SampleRangeAddr As New com.sun.star.table.CellRangeAddress
	Dim CurrentRangeAddr As Variant
	Dim SampleSheet As Object
	Dim SampleRange As Object
	Dim Samples As Variant
	Dim Sample As Variant
	Dim SampleIsRepeat As Boolean
	Dim CurrentRange As Object
	Dim CurrentValue As Variant
	
	
	CurrentRange = ThisComponent.CurrentController.Selection
	If Not CurrentRange.supportsService("com.sun.star.sheet.SheetCell") Then Exit Sub
	CurrentRangeAddr = CurrentRange.getCellAddress
	If CurrentRangeAddr.Row = 0 Then Exit Sub
	SampleSheet = ThisComponent.Sheets.getByIndex(CurrentRangeAddr.Sheet)

	CurrentValue = CurrentRange.DataArray(0)(0)

	With SampleRangeAddr
		.StartColumn = CurrentRangeAddr.Column
		.EndColumn = .StartColumn
		.StartRow = 0
		.EndRow = CurrentRangeAddr.Row - 1
		SampleRange = SampleSheet.getCellRangeByPosition(.StartColumn, .StartRow, .EndColumn, .EndRow)
	End With
	
	Samples = SampleRange.getDataArray(0)

	SampleIsRepeat = False
	For Each Sample in Samples
		If CurrentValue = Sample(0) Then
			SampleIsRepeat = True
			Exit For
		EndIf
	Next
	
	If Not SampleIsRepeat Then MsgBox "Warning: Unknown value in edit"
	
End Sub

The upload is a primitive implementation.

ProtectedEntry.ods (11.6 KB)

[LeroyG]

Add a Validity criteria. validity.ods (9.4 KB)
This way you can’t type new values | formulas, but you can paste them. Once pasted, you are allowed to type them.
There is no need to protect the sheet, but cells content can be deleted.