CommandButton1_automatically update the date in ("A") column

[mawar5530]

Hi,

i have try to code as below, but seem not reflecting.

I want column A14 to a44, if any value in number for this column then the "B" will update the time.
Next will lock the cell that has the value and color yellow.

I need help on this.

Private Sub CommandButton1_Click()

If Range("A14:A44") Is Nothing Then

On Error Resume Next

If Target.Value = "" Then

Target.Offset(0, 1) = ""

Else

Target.Offset(0, 1).Value = Format("HH:mm:ss")


End If
End If

End Sub

 

[DanteAmor]

Do you want it to work with the button or would you prefer it to be automatic when you modify the cell in A then update the cell in B, also lock cell in A and highlight yellow.

 

[mawar5530]

i can figure it out now, but the locked is not functioning, can please help.
i try to do via the format cell , then the macro not working with the cell locked.

Private Sub CommandButton2_Click()

Dim r As Range, r1 As Range

On Error Resume Next
Set r = Range("A14:A44").SpecialCells(xlCellTypeConstants, xlNumbers)

On Error GoTo 0

If r Is Nothing Then Exit Sub

For Each r1 In r.Cells
If Len(r1.Offset(0, 1).Value) = 0 Then
r1.Offset(0, 1).Value = Format(Now, "HH:mm:ss")
r1.Offset(0, 1).lOCKED = True
r1.Resize(1, 5).Interior.Color = 13434879

End If
Next

End Sub

 

[DanteAmor]

Do you have the protected sheet?
Try the following, change "abc" for your password.

Private Sub CommandButton2_Click()
  Dim r As Range, r1 As Range
  
  On Error Resume Next
  ActiveSheet.Unprotect "abc"
  Set r = Range("A14:A44").SpecialCells(xlCellTypeConstants, xlNumbers)
  On Error GoTo 0
  If r Is Nothing Then
    ActiveSheet.Protect "abc"
    Exit Sub
  End If
  
  For Each r1 In r.Cells
    If Len(r1.Offset(0, 1).Value) = 0 Then
      r1.Offset(0, 1).Value = Format(Now, "HH:mm:ss")
      r1.Offset(0, 1).Locked = True
      r1.Resize(1, 5).Interior.Color = 13434879
    End If
  Next
  ActiveSheet.Protect "abc"
End Sub

 

[mawar5530]

Do you have the protected sheet?
Try the following, change "abc" for your password.

Private Sub CommandButton2_Click()
  Dim r As Range, r1 As Range
  
  On Error Resume Next
  ActiveSheet.Unprotect "abc"
  Set r = Range("A14:A44").SpecialCells(xlCellTypeConstants, xlNumbers)
  On Error GoTo 0
  If r Is Nothing Then
    ActiveSheet.Protect "abc"
    Exit Sub
  End If
  
  For Each r1 In r.Cells
    If Len(r1.Offset(0, 1).Value) = 0 Then
      r1.Offset(0, 1).Value = Format(Now, "HH:mm:ss")
      r1.Offset(0, 1).Locked = True
      r1.Resize(1, 5).Interior.Color = 13434879
    End If
  Next
  ActiveSheet.Protect "abc"
End Sub

PERFECT !

Thank you so much DanteAmor

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.