Macro Button for adding time

[DC18]

Hi,


I am quite new to Excel and just finished two beginners courses at work.
Everything is going fine and there are more courses to come.

My problem now lies with two buttons i would like to add in my spreadsheet.
I know i am in way over my head, but i was wondering if someone here might be able to help me.

I am trying to add buttons, which will add time to a certain cell.
Said cell would start every day at 00:00 (hh:mm) now the buttons would add time to that every time when pressed.
I would make 2 different buttons, one that adds 00:03 and one that adds 00:05.

Example: i press the 00:05 button twice and the 00:03 button once i get 00:13 min in total.

I know how to make a button how to record and assign a macro, but im quite lost at what i have to do to get this thing working.

I've included a basic image of what i'm trying to accomplish here.

Thanks.

 

[AC PORTA VIA]

select cell first
one to add 0.05 use

ActiveCell.Value = ActiveCell.Value + TimeValue("00:05:00")

one to add 0.03 use

ActiveCell.Value = ActiveCell.Value + TimeValue("00:03:00")

 

[shknbk2]

Welcome to the forum. I have a suggestion that doesn't use buttons. It should be easier to implement without having to make additional buttons every time a new row of data is entered.

This is my data. Columns D and E are formatted with underline and blue text to look like hyperlinks, but they are just plain text. You could change them to be whatever you wanted.

The idea for this is to create a BeforeDoubleClick event in the worksheet code. This type of event fires every time there is a double-click on the sheet but before actually doing anything (like entering into the Edit mode with the cursor blinking in the cell as is the normal default mode). The procedure checks to see if any of the cells in D or E were the ones double-clicked. If not, the normal double click procedure is allowed to happen (previous parenthetical).

Otherwise, if D or E was double-clicked, the code checks to see if there is only one cell (I'm not sure that more can actually be double-clicked, but it might be good to check anyway) and whether that cell is in D or E. If in col D, 3 minutes are added to Col B in the same row, or 5 minutes are added if cell is in col E. In order to not have the follow-up default behavior happen (e.g., to avoid going into the edit mode), Cancel is set to True, which stops any post BeforeDoubleClick behavior. Notice that if the D or E cell is blank, the normal edit mode is allowed to happen because there wouldn't be any corresponding time in col B to add time to, in theory.

The VBA code goes in the code area for the Worksheet. For example, right-click on the sheet tab and choose View Code.

Book1
	A	B	C	D	E
1	SR1	00:45		Add 3 min	Add 5 min
2	SR2	00:11		Add 3 min	Add 5 min
3	SR3	00:26		Add 3 min	Add 5 min
4	SR4	00:20		Add 3 min	Add 5 min
Sheet1

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Dim rIntersect As Range
    
    If Target.Cells.Count = 1 Then
        If Target.Value <> "" Then
            Set rIntersect = Intersect(Target, Range("D:D"))
            If Not rIntersect Is Nothing Then
                Cells(Target.Row, 2).Value = Cells(Target.Row, 2).Value + 3 / 24 / 60
                Cancel = True
            Else
                Set rIntersect = Intersect(Target, Range("E:E"))
                If Not rIntersect Is Nothing Then
                    Cells(Target.Row, 2).Value = Cells(Target.Row, 2).Value + 5 / 24 / 60
                    Cancel = True
                End If
            End If
        End If
    End If
End Sub

This assumes that the data in the time column B is formatted to show the hour and minutes (e.g., "hh:mm"). For information, a Date and or Time format can be treated as a Double format where the integer value is the number of days since Dec. 31, 1899 and the decimal value is the time of day. 1am for example, is 1/24 (i.e., 0.041666667). Therefore, 1 minute is 1/24/60. That's why the code uses 3/24/60 for 3 minutes. Since the time part is the only relevant part to be shown, it doesn't matter whether the number value is 0.041666667 for 1am in 1899 or 44592.041666667 for today at 1am.

Note, however, that if you keep adding time, the value will loop back to 00h after 23h in the displayed cell even though the integer value would be increased by 1 to show that a full day has passed.

 

[DC18]

Welcome to the forum. I have a suggestion that doesn't use buttons. It should be easier to implement without having to make additional buttons every time a new row of data is entered.

This is my data. Columns D and E are formatted with underline and blue text to look like hyperlinks, but they are just plain text. You could change them to be whatever you wanted.

The idea for this is to create a BeforeDoubleClick event in the worksheet code. This type of event fires every time there is a double-click on the sheet but before actually doing anything (like entering into the Edit mode with the cursor blinking in the cell as is the normal default mode). The procedure checks to see if any of the cells in D or E were the ones double-clicked. If not, the normal double click procedure is allowed to happen (previous parenthetical).

Otherwise, if D or E was double-clicked, the code checks to see if there is only one cell (I'm not sure that more can actually be double-clicked, but it might be good to check anyway) and whether that cell is in D or E. If in col D, 3 minutes are added to Col B in the same row, or 5 minutes are added if cell is in col E. In order to not have the follow-up default behavior happen (e.g., to avoid going into the edit mode), Cancel is set to True, which stops any post BeforeDoubleClick behavior. Notice that if the D or E cell is blank, the normal edit mode is allowed to happen because there wouldn't be any corresponding time in col B to add time to, in theory.

The VBA code goes in the code area for the Worksheet. For example, right-click on the sheet tab and choose View Code.

Book1
	A	B	C	D	E
1	SR1	00:45		Add 3 min	Add 5 min
2	SR2	00:11		Add 3 min	Add 5 min
3	SR3	00:26		Add 3 min	Add 5 min
4	SR4	00:20		Add 3 min	Add 5 min
Sheet1

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Dim rIntersect As Range
   
    If Target.Cells.Count = 1 Then
        If Target.Value <> "" Then
            Set rIntersect = Intersect(Target, Range("D:D"))
            If Not rIntersect Is Nothing Then
                Cells(Target.Row, 2).Value = Cells(Target.Row, 2).Value + 3 / 24 / 60
                Cancel = True
            Else
                Set rIntersect = Intersect(Target, Range("E:E"))
                If Not rIntersect Is Nothing Then
                    Cells(Target.Row, 2).Value = Cells(Target.Row, 2).Value + 5 / 24 / 60
                    Cancel = True
                End If
            End If
        End If
    End If
End Sub

This assumes that the data in the time column B is formatted to show the hour and minutes (e.g., "hh:mm"). For information, a Date and or Time format can be treated as a Double format where the integer value is the number of days since Dec. 31, 1899 and the decimal value is the time of day. 1am for example, is 1/24 (i.e., 0.041666667). Therefore, 1 minute is 1/24/60. That's why the code uses 3/24/60 for 3 minutes. Since the time part is the only relevant part to be shown, it doesn't matter whether the number value is 0.041666667 for 1am in 1899 or 44592.041666667 for today at 1am.

Note, however, that if you keep adding time, the value will loop back to 00h after 23h in the displayed cell even though the integer value would be increased by 1 to show that a full day has passed.

I did not know that this was an option.
This works wonders, thank you very much!

 

[DC18]

select cell first
one to add 0.05 use

ActiveCell.Value = ActiveCell.Value + TimeValue("00:05:00")

one to add 0.03 use

ActiveCell.Value = ActiveCell.Value + TimeValue("00:03:00")

Thank you!