Forum Discussion

Phil Cox's avatar
Phil Cox
Copper Contributor
Apr 03, 2021
Solved

Macro button

Hi all. I'm new here. I would like to create a button macro that when I click it, it will go to a cell according to the time (each cell represents a 15 min time) and increase the number in that cell...
excel
Formulas and Functions
Macros and VBA
office 365
  • HansVogelaar's avatar
    HansVogelaar
    Apr 04, 2021

    Phil Cox 

    Near the end of the macro, change the part

        With wsData.Cells(r, TimeCol + x)
        .Value = 1
        .Interior.Color = vbYellow
    End With

    to

        With wsData.Cells(r, TimeCol + x)
        .Value = .Value + 1
        .Interior.Color = vbYellow
    End With

    And near the beginning, the line

        If Time > TimeSerial(18, 30, 0) And Time < TimeSerial(5, 30, 0) Then

    should be

        If Time > TimeSerial(18, 30, 0) Or Time < TimeSerial(5, 30, 0) Then

    See the attached version with the modified version of Subodh_Tiwari_sktneer's code.

Phil Cox's avatar
Phil Cox
Copper Contributor
Apr 04, 2021

Phil Cox 

One more thing. The count on the data sheet only goes to one. I would like to increase what ever number is there by 1 each time I click the bottom. So if I click it four times in one 15 min period  it will show 4.

What I am trying to achieve is to track how many employees and veterans I pick up in my shuttle bus at each of my shuttle stops.

Thanks for your help.

Phil

HansVogelaar's avatar
HansVogelaar
MVP
Apr 04, 2021

Phil Cox 

Near the end of the macro, change the part

    With wsData.Cells(r, TimeCol + x)
        .Value = 1
        .Interior.Color = vbYellow
    End With

to

    With wsData.Cells(r, TimeCol + x)
        .Value = .Value + 1
        .Interior.Color = vbYellow
    End With

And near the beginning, the line

    If Time > TimeSerial(18, 30, 0) And Time < TimeSerial(5, 30, 0) Then

should be

    If Time > TimeSerial(18, 30, 0) Or Time < TimeSerial(5, 30, 0) Then

See the attached version with the modified version of Subodh_Tiwari_sktneer's code.

  • Subodh_Tiwari_sktneer's avatar
    Subodh_Tiwari_sktneer
    Silver Contributor
    Apr 04, 2021

    HansVogelaar 

    Thanks for pointing out the error in logical condition and thanks for tweaking the code. That was a silly mistake, my bad.

Resources