eQuation78
New Member
- Joined
- May 28, 2015
- Messages
- 1
Dear Excellers,
First, let me start by saying that I am new here and my native language is not English, so please don't mind some spelling errors
I am quit new with vb and I wrote simple workaround for locking cells in a Worksheet when a value is met.
So when the user types, in this case a date, into a cell in Q it will automatically put the word "FILLED" in K on the same row. The script then locks the complete row from A to R. This works just fine.
The thing is, I would like to have a control question asked with a msgbox that asks the user if he/she is sure that the date entered in i.e. Q2 is correct, since they can not change it once the put it in.
When answered 'no' it should delete the date in Q2 and loop back. When answered 'Yes' it should execute the script as shown above.
I tried different thing and I am not getting there, can somebody help me out?
Thank you in advance.
First, let me start by saying that I am new here and my native language is not English, so please don't mind some spelling errors
I am quit new with vb and I wrote simple workaround for locking cells in a Worksheet when a value is met.
Code:
Sub Worksheet_Change(ByVal Target As Range)
Dim i As Variant
If Not Intersect(Target, Range("Q2:Q2000")) Is Nothing Then
ActiveSheet.Unprotect Password:="password"
For i = 2 To 2000
If Range("K" & i).Value = "FILLED" Then
Range("A" & i, "R" & i).Locked = True
End If
Next i
ActiveSheet.Protect Password:="password"
End If
End Sub
So when the user types, in this case a date, into a cell in Q it will automatically put the word "FILLED" in K on the same row. The script then locks the complete row from A to R. This works just fine.
The thing is, I would like to have a control question asked with a msgbox that asks the user if he/she is sure that the date entered in i.e. Q2 is correct, since they can not change it once the put it in.
When answered 'no' it should delete the date in Q2 and loop back. When answered 'Yes' it should execute the script as shown above.
I tried different thing and I am not getting there, can somebody help me out?
Thank you in advance.