VBA - Lock Specific Cell Ranges After Data is entered

wcm69

Board Regular
Joined
Dec 25, 2016
Messages
112
I have a spreadsheet which is password protected when initialized. I have managed to create a shape button macro which unlocks the following range of cells to allow the user to enter data.

Unlocked Cells:- G8:G1000, H8:H1000, S8:S1000 and T8:T1000

What I need is - the code which after data is entered into the (unlocked) cell has a message telling the user that specific cell will then be locked and no further data can be entered into that cell.

I have searched on line and seen this question asked and various examples posted which I have tried adapting for my purposes but none of my attempts have worked.

I have a basic understanding of VBA but not enough knowledge required to solve this one.
I’m hoping that someone would be kind enough to help me out on this.

The last (unsuccessful) code I tried looks like this:

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False
Unprotect Password:="12345"

If Target.Range("G8:G1000;H8:H10000;S8:S1000;T8:T1000") Then

confirm = MsgBox("Do you wish to confirm entry of this data?" _
& vbCrLf & "You will not be allowed to change it!", vbYesNo, "confirm Entry")

Select Case confirm
Case Is = vbYes
Dim Cell As Range
With ActiveSheet
.Unprotect Password:="12345"
.Cells.Locked = False
For Each Cell In ActiveSheet.UsedRange
If Cell.Value = "" Then
Cell.Locked = False

Else

Cell.Locked = True
End If
Next Cell
.Protect Password:="12345"
End With
Case Is = vbNo
Application.Undo

End Select
End If

Application.EnableEvents = True

End Sub

As usual, any help is always appreciated.

Thanks in advance :)
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

Forum statistics

Threads
1,223,901
Messages
6,175,277
Members
452,629
Latest member
SahilPolekar

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top