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
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