using vba code on two cell columns

[helwel]

i googled some vba code for what i want to do. but in trying to put a few commands together. what i want to do doesnt work.
i have a 5 column worksheet, one page worth. 1- i want to lock column C when data is entered. i also want to ask a question whether the data is okay before locking. 2- when the 4 columns are filled in , i want to automatically record user with date and time in the 5th column.

the below asks me the question for every column field , not just column C that i want to lock.
and the user/date fills in for all lines 7 thru 44 in column e , not just the current line i am entering on.
and then the excel freezes

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cel_c As Range
Dim cel_e As Range
On Error Resume Next
Set cel_c = Intersect(Range("c7:c44"), Target)
Set cel_e = Intersect(Range("e7:e44"), Target)
For Each cel_c In Target


If cel_c.Value <> "" Then
check = MsgBox("Is this entry correct? This cell cannot be edited after entering a value.", vbYesNo, "cell lock definition")
If check = vbYes Then
Target.Worksheet.Unprotect Password:="secret"
cel_c.Locked = True
cel_e = Format(Date, "mm/dd/yyyy") & " at " & Format(Time, "hh:mm AMPM") & " by " & Application.UserName
Else
cel_c.Value = ""
Target.Worksheet.Protect Password:="secret"
End If
End If
Next cel
End Sub

 

[Logit]

.

and for the rest of the rows to be untouched till something keyed in .

Your comment implies that somehow you are by-passing the message box that appears after you enter a value in Col C and Tab out of the cell.
How are you by-passing the message box ? The choices are either YES or NO, and then Col C is locked again.

in seeing the offset command, then i dont need cel_e defined? cel_e was for the userdate&time

Correct. That was left over test code that should have been removed before I sent it to you.



Overall, I believe the macro functions as you wanted it to without any changes you've implemented (except for the Offset 2 which is perfectly fine).
I'm not certain I understand your goal in Post #10 .

 

[helwel]

.


please excuse my new-ness to macro. i am a programmer, but just not used to some of these commands.

i think one of my main problem was as i kept testing and re-setting my sheet a few times somehow my format -> protection -> locked had a check in it. i made sure testing today that it stayed unchecked as i tested.
i reset my code to what was given to me. the first line of entry (row 7) works fine if i say YES to column C data ; it locks column C and when it puts timestamp in column E that also locks. (i added the lock statement for column E to the code). if i say NO , i can reenter and upon YES the two columns lock.

but the rest of the sheet does not lock . i go to row 8 and enter, i am asked yes/no for column c . if i say yes, i get the timestamp but no locks and if no; it works as it should. the same with the rest of the lines.

is it something quirky that just the first row of entry works with locking?

also , i have seen a few examples and can you tell me the difference between using Target.Worksheet.Protect and ActiveSheet.Protect i am not sure how each works ?

 

[Logit]

.
I don't understand why the code is functioning, as it is, for you. The workbook available for download works, as coded, as expected here.

If you want to post your version for download, I can look it over for you.


I am not familiar with "Target.Worksheet.Protect" as I've never used it. Of course "Activesheet.Protect" references the worksheet that is presently in view to the user.
I could surmise "Target.Worksheet.Protect" would refer to any sheet not in view to the user ?

 

[helwel]

this is what i copied from this forum thread.
i really don't understand the reason of the function and the first sub but i have been working with the third main sub to get my macro to work.
thanks for your help.

Option Explicit




Function InRange(Range1 As Range, Range2 As Range) As Boolean
' returns True if Range1 is within Range2
Dim InterSectRange As Range
Set InterSectRange = Application.Intersect(Range1, Range2)
InRange = Not InterSectRange Is Nothing
Set InterSectRange = Nothing
End Function








Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If InRange(ActiveCell, Range("c7:c44")) Then
ActiveSheet.Unprotect "secret"
Else
Exit Sub
End If
End Sub


' make sure format->protection -> locked not checked
' to get into this editor you can hit ALT F11
Private Sub Worksheet_Change(ByVal Target As Range)
Dim column_c As Range
'Dim column_e As Range
Dim cell As Range
Dim check As Variant
On Error Resume Next




Set column_c = Intersect(Range("c7:c44"), Target)
'Set column_e = Intersect(Range("e7:e44"), Target)




If Not (column_c Is Nothing) Then
For Each cell In column_c
If column_c.Value <> "" Then
check = MsgBox("Is this entry correct? This cell cannot be edited after entering a value.", vbYesNo, "cell lock definition")

If check = vbNo Then
column_c.Value = ""
column_c.Offset(0, 2).Value = ""
column_c.Locked = True
'Target.Worksheet.Protect Password:="secret"
Exit Sub
End If

If check = vbYes Then
column_c.Offset(0, 2).Value = Format(Date, "mm/dd/yyyy") & " at " & Format(Time, "hh:mm AMPM") & " by " & Application.UserName
column_c.Locked = True
'column_e.Locked = True
column_c.Offset(0, 2).Locked = True
Target.Worksheet.Protect Password:="secret"
End If

End If
Next cell
End If
End Sub

 

[Logit]

.
In your code :

If check = vbNo Then
column_c.Value = ""
column_c.Offset(0, 2).Value = ""
column_c.Locked = True
[B]'Target.Worksheet.Protect Password:="secret"[/B]
Exit Sub
End If

You have this line commented out, which prevents replacing the password and locking the sheet again after the user clicks NO : 'Target.Worksheet.Protect Password:="secret"

Uncomment that line by removing the single quote in front of it : Target.Worksheet.Protect Password:="secret"


Also, thank you for running into this problem because it made me look closer at the code and I discovered another way of circumventing the protection.
If the user double clicks in C Col ... doesn't enter anything but simply clicks on any other cell in the sheet, they can enter data in the other cell. Not good.

Add the following new lines of code to the top of the macro as shown. Note that I've shown more than just the new code so you can clearly see where to enter the code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cel_c As Range
Dim cel_e As Range
Dim cell As Range
Dim check As Variant
On Error Resume Next


Set cel_c = Intersect(Range("c7:c44"), Target)


    If Not cel_c Is Nothing Then
        For Each cell In cel_c
        
[B]            If cel_c.Value = "" Then[/B]
[B]                    cel_c.Locked = True[/B]
[B]                    Target.Worksheet.Protect Password:="secret"[/B]
[B]                    Exit Sub[/B]
[B]            End If[/B]
            
            If cel_c.Value <> "" Then
            check = MsgBox("Is this entry correct? This cell cannot be edited after entering a value.", vbYesNo, "cell lock definition")

 

[helwel]

uncommenting out the Target.worksheet that i had tried to comment. didnt make any effect.
i added the code for the double click, and i was still able to re-key my column C row 7.
still the only row that locked was row 7. all other rows are not locking the two columns of data when entered.

 

[Logit]

.
Im so sorry. There is no solution apparently.

It works here fine.

Perhaps someone else can assist.

Cheers.

 

[helwel]

thanks alot for your help. maybe its an excel2010 thing. i think what its doing is after the first lock, its resetting the protection - Locked checkmark. at the beginning i can do Cntrl 1 and uncheck the locked. but after first entry , i can't check this flag unless i review-> unprotect sheet. then check Ctrl 1 works and i see flag is set again. i think this resetting of the Locked flag is the cause of my problems. i will keep digging. thanks for your help.