Click shape to move forward and back in cells of the same row

Question

I have a table with data in date format (day/month/year) distributed in row 6 and in several columns that go from E6 to NE6.

That is, E6 = 01/01/2025 and NE6 = 12/31/2025.

To advance between these dates, I created two images (arrows), to which I want to assign a macro when I click:

• In the "Right Arrow" image, when I click, it should advance to the next day 01 of the following month.

• In the "Left Arrow" image, when I click, it should return to the day 01 of the previous month.

With what VBA code can I advance or go back through the cells in the same row using buttons?

I tried this code:

'MOVE CELL RIGHT ARROW

Sub Move_right_forward()

Dim ActiveLine As Integer, ActiveCollection As Integer
ActiveLine = ActiveCell.Row
ActiveCollection = ActiveCell.Column

If ActiveLine < 4 Then
MsgBox "You must select a cell that contains data!"
ExitSub
End If

If ActiveLine > LastLine Then
MsgBox "You must select a cell that contains data!"
ExitSub
End If

Cells(ActiveLine, ActiveCollection + 1).Select

End Sub

Answer 1

Put the below code into a standard module.
Add to the sheet two shapes (a left arrow and a right arrow).
Right click each and then click Assign Macro.
In the popup window select the corresponding macro. Then click OK.

Sub leftarrow()
Dim ws As Worksheet, cell As Range
Set ws = ActiveSheet
lastRow = ws.Cells(ws.Rows.count, "A").End(xlUp).Row
Set cell = ActiveCell
If cell.Row < 4 Or cell.Row > lastRow Then MsgBox ("Selected cell is out of range!"): Exit Sub
mo = Month(ws.Cells(6, cell.Column))
If mo = 1 Then
  mocol = ws.Rows(6).Find(DateSerial(2025, 1, 1)).Column
  ws.Cells(cell.Row, mocol).Select
Else
  mocol = ws.Rows(6).Find(DateSerial(2025, mo - 1, 1)).Column
  ws.Cells(cell.Row, mocol).Select
End If
End Sub

Sub rightarrow()
Dim ws As Worksheet, cell As Range
Set ws = ActiveSheet
lastRow = ws.Cells(ws.Rows.count, "A").End(xlUp).Row
Set cell = ActiveCell
If cell.Row < 4 Or cell.Row > lastRow Then MsgBox ("Selected cell is out of range!"): Exit Sub
mo = Month(ws.Cells(6, cell.Column))
If mo = 12 Then
  mocol = ws.Rows(6).Find(DateSerial(2025, 12, 1)).Column
  ws.Cells(cell.Row, mocol).Select
Else
  mocol = ws.Rows(6).Find(DateSerial(2025, mo + 1, 1)).Column
  ws.Cells(cell.Row, mocol).Select
End If
End Sub

If you are in the first or last month of the year the activecell will move to the actual month's (Jan or Dec) first day.

Answer 2

Assuming row 6 is formatted with the desired date format, the script will determine the first day of the next/previous month.

If the script doesn't resolve the issue, please share a sample of your worksheet data and its layout.

Sub GetNextMth() ' for Right Arrow button
    Const RNG_REF = "E6:NE6"
    Dim c As Range
    For Each c In Range(RNG_REF).Cells
        If IsDate(c) Then
            c.Value = DateSerial(Year(c), Month(c) + 1, 1)
        End If
    Next
End Sub

Sub GetPreMth() ' for Left Arrow button
    Const RNG_REF = "E6:NE6"
    Dim c As Range
    For Each c In Range(RNG_REF).Cells
        If IsDate(c) Then
            c.Value = DateSerial(Year(c), Month(c) - 1, 1)
        End If
    Next
End Sub