To protect and lock cells after entry of weight in col. A, time of data entry will be recorded, comapare value, and set counter?

TKKal

New Member
Joined
Mar 3, 2020
Messages
4
Office Version
  1. 2016
Platform
  1. Windows
Private Sub Worksheet_Change(ByVal Target As Range)

Dim tasksheet As Worksheet
Dim i As Integer
Dim T2 As Integer
Dim Lr As Integer

ActiveSheet.Unprotect Password:="123"
Target.Locked = False

Set tasksheet = ThisWorkbook.Sheets("Sheet1")
Lr = tasksheet.Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To Lr
If tasksheet.Cells(i, "A").Value <> "" And tasksheet.Cells(i, "B").Value = "" Then

tasksheet.Cells(i, "B").Value = Time
tasksheet.Cells(i, "B").NumberFormat = "hh:mm"
tasksheet.Cells(i, "A").Font.ColorIndex = 5
End If
If tasksheet.Cells(i, "A") < 18.9 Then
T2 = T2 + 1
tasksheet.Cells(i, "A").Font.ColorIndex = 3
End If

Next

Cells(5, 10).Value = T2

ActiveSheet.Protect Password:="123"


End Sub

Whenever I ran the code, I receive a run time error 1004, on line (tasksheet.Cells(i, "B").NumberFormat = "hh:mm"). If password protection line is removed the code run with no problem
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
Hi and welcome to MrExcel!

The sheet where the code is and the sheet "Sheet1" are different sheets?
Which sheet is the one with the password?

You want the event to be triggered when you modify a cell in a specific range, that is, for example, that the event is triggered when you modify a cell in column A.
Or in any cell on sheet?

You can explain a little, what do you want to do with the macro.
 
Upvote 0
Hello
The sheet I need to protect is sheet1,
To summarize What I need
1- Enter Data in Column A,
2- Time should be recorded in Column B
3- Cells must be locked after data entry by password ( Cells value can not be change without password)
4- If the value in Column A is less than (18.9) red font applied and counter is set
The problem this code is running fine as long as there is no sheet protection, once sheet protection line is applied, I receive Error 1004 (Run time).
 
Upvote 0
I'm sorry, but I didn't understand your answer. Is the code and sheet1 the same sheet?

And another question, why do you need the For loop?
 
Upvote 0
Try this
Add EnableEvents instruction

VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)

Dim tasksheet As Worksheet
Dim i As Integer
Dim T2 As Integer
Dim Lr As Integer
application.EnableEvents = false
ActiveSheet.Unprotect Password:="123"
Target.Locked = False

Set tasksheet = ThisWorkbook.Sheets("Sheet1")
Lr = tasksheet.Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To Lr
If tasksheet.Cells(i, "A").Value <> "" And tasksheet.Cells(i, "B").Value = "" Then

tasksheet.Cells(i, "B").Value = Time
tasksheet.Cells(i, "B").NumberFormat = "hh:mm"
tasksheet.Cells(i, "A").Font.ColorIndex = 5
End If
If tasksheet.Cells(i, "A") < 18.9 Then
T2 = T2 + 1
tasksheet.Cells(i, "A").Font.ColorIndex = 3
End If

Next

Cells(5, 10).Value = T2
Application.EnableEvents = true
ActiveSheet.Protect Password:="123"


End Sub
 
Upvote 0
I'm glad to help you. Thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,273
Members
448,559
Latest member
MrPJ_Harper

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