View Full Version : CommandButton1_automatically update the date in ("A") column
Mawar5530
06-16-2020, 08:51 PM
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
Paul_Hossler
06-17-2020, 06:48 AM
1. Range("A4:A14") always exists and hence will never be Nothing
2. Target is not defined so On Error Resume Next will just keep on going
3. Format("HH:mm:ss") doesn't have anything to format
I'm guessing that you wanted something like this (to at least get you started)
Option Explicit
Private Sub CommandButton1_Click()
Dim r As Range, r1 As Range
On Error Resume Next
Set r = Range("A4:A14").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
Mawar5530
06-17-2020, 08:49 PM
Hi Paul.
Yeah, thank you so much. Its run well.
However 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.
Paul_Hossler
06-18-2020, 07:13 AM
All cells on the WS are Locked=False, except for B4:E14 which are Locked = True
More that that, you'll have to figure out the other specifics
Option Explicit
Private Sub CommandButton1_Click()
Dim r As Range, r1 As Range
On Error Resume Next
Set r = Range("A4:A14").SpecialCells(xlCellTypeConstants, xlNumbers)
On Error GoTo 0
If r Is Nothing Then Exit Sub
Me.Unprotect "abc"
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
Me.Protect Password:="abc", UserInterfaceOnly:=True
End Sub
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.