Andrew has attended:
Access Introduction course
Excel VBA
Hi,
I posted the VBA at the bottom of the screen... it works for a while then stops... now it only works on row one and none of the rest..
I followed this website https://www.extendoffice.com/documents/excel/3778-excel-lock-cell-after-data-entry-input.html
regards
Andrew
Dim mRg As Range
Dim mStr As String
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Not Intersect(Range("A1:I1000"), Target) Is Nothing Then
Set mRg = Target.Item(1)
mStr = mRg.Value
End If
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
Dim xRg As Range
On Error Resume Next
Set xRg = Intersect(Range("A1:I1000"), Target)
If xRg Is Nothing Then Exit Sub
Target.Worksheet.Unprotect Password:="HICA"
If xRg.Value <> mStr Then xRg.Locked = True
Target.Worksheet.Protect Password:="HICA"
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Range("A1:I1000"), Target) Is Nothing Then
Set mRg = Target.Item(1)
mStr = mRg.Value
End If
End Sub
RE: Excel VBA
Hi Andrew
I've tried to recreate your scenario so the below is based on best guess.
When the macro is ran the formatting PROTECTION LOCKED is changed for all the cells back to ON.
When resetting the macro and sheet I manually switched the PROTECTION off for the first row only.
I then filled in the first row successfully after which I got the standard error message - locked cells - and then the first row was LOCKED.
So I'm guessing you need to check that you have removed the LOCKED option off all the cells you want to populate before attempting to rerun the macro.
step 1 from the link you sent.
I hope this helps.
Regards
Dennis