Allow editing of only blank cells

[Sugofinto]

Hello guys, first poster here as it's the first time I'm trying to do something similar.
I need to prevent accidental modify of values in the cells, in other words: users should be only allowed to modify blank cells, is it possible?
I'm trying to do that using chatgpt and copilot but their suggestions are not working, can any of you guys help me?

Thank you in advance!

[HansV]

Welcome to Eileen's Lounge!

That would require VBA, so all users should allow macros. And it will only work in the desktop version of Excel for Mac and Windows, not in the browser or on iOS and Android.
Would that be OK?

[Sugofinto]

Thank you HansV.
Sure, that is would be OK!

[ChrisGreaves]

Hello guys, first poster here as it's the first time I'm trying to do something similar.
I need to prevent accidental modify of values in the cells, in other words: users should be only allowed to modify blank cells, is it possible?
I'm trying to do that using chatgpt and copilot but their suggestions are not working, can any of you guys help me?

Thank you in advance!

Hi Sugofinto and welcome to Eileen's Lounge.
You have an answer to your question to work on, but I am curious about ChatGPT.

Might you post - as best as you can remember - the question(s) you sent to ChatGPT?

I for one would like to see how ChatGPT stumbled in its response.
Thanks, Chris

[Sugofinto]

That would be a really long post since I've tried to "tinker" his response for half a day. :)

I've started from the suggestion from copilot:

Spoiler

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range
Application.EnableEvents = False
On Error GoTo ErrHandler
For Each cell In Target
If cell.Locked = False Then
If Not IsEmpty(cell.Value) Then
cell.Locked = True
Me.Protect UserInterfaceOnly:=True
End If
End If
Next cell
Application.EnableEvents = True
Exit Sub
ErrHandler:
Application.EnableEvents = True
End Sub

It wasn't working so I've tried pasting it into chatgpt and telling it that the script wasn't I wanted. can't paste all conversation since it is really long.

Your message contains 29491 characters.
The maximum number of allowed characters is 7500.

It ended up like this anyway! :)

Spoiler

User
not working i can't insert data into blank cells
ChatGPT
Let's simplify the code and use the Worksheet_SelectionChange event to unlock all cells whenever a cell is selected. Then, we'll re-lock non-blank cells:

vba
Copy code
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim cell As Range
On Error GoTo ErrHandler
Application.EnableEvents = False
Me.Unprotect 'Unprotect the sheet to allow changes

For Each cell In Me.UsedRange
If cell.Value <> "" Then ' Check if cell is not blank
cell.Locked = True ' Lock the cell
Else
cell.Locked = False ' Unlock the cell if it's blank
End If
Next cell

Me.Protect UserInterfaceOnly:=True 'Re-protect the sheet
Application.EnableEvents = True ' Enable events again

ErrHandler:
If Err.Number <> 0 Then
MsgBox "An error occurred: " & Err.Description, vbExclamation
End If
End Sub
This code should unlock all cells when a cell is selected, and then re-lock non-blank cells, allowing only blank cells to be editable. Please replace your existing code with this revised version and see if it behaves as expected.

User
nothing trying to ask a forum
ChatGPT
It sounds like you're experiencing difficulty resolving the issue with the VBA code. Asking a forum for assistance could be a great idea. They might offer fresh perspectives or alternative solutions that could be helpful. If you need any further assistance or have any other questions, feel free to ask!

You have an answer to your question to work on

Where can I find it? I'm a little rusted on forum usage.

[HansV]

Step 1:

Start by selecting all the cells that the user should initially be allowed to edit.
Press Ctrl+1 to activate the Format cells dialog.
Activate the Protection tab.
Clear the Locked check box.
Click OK.

Step 2:

On the Review tab of the ribbon, in the Protect group, click Protect Sheet.
Use the check boxes to specify what the users will be allowed to do.
At the least, leave the check box 'Select unlocked cells' ticked.
If you wish, enter a password that will be needed to unprotect the sheet. Do not forget the password!
Click OK. If you entered a password, you will have to enter it again as confirmation.

Step 3:

Right-click the sheet tab.
Select 'View Code' from the context menu.
Copy the following code into the worksheet module:

Code: Select all

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Application.ScreenUpdating = False
    If ActiveCell.Value <> "" Then
        Me.Unprotect Password:="secret"
        Target.Locked = True
        Me.Protect Password:="secret"
    End If
End Sub

I used "secret" as password here; change it to the password you used, "" if none.
Switch back to Excel.

Step 4:

Save the workbook as a macro-enabled workbook (*.xlsm).

Sample workbook attached.

LockNonBlank.xlsm

[Sugofinto]

Nice, works like a charm.
Thank you so much guys for welcoming me and thank you Hans for the solution.