last day of the month (VBA)

[robertvdb]

Hope someone can help. When I rightclick on the first 30 cells in col A (these dates are NOT the last day of the month), then a Msgbox has to appear with "NO". But when the cell IS the last day of the month, then "YES" has to appear in the Msgbox.

See the image. Thanks in advance.

​

 

[Joe4]

Right-click on the sheet tab name at the bottom of the screen, select "View Code", and paste this code in the VB Editor window that pops up:

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)

    If Target.CountLarge > 1 Then Exit Sub
    
'   Make sure cell right-clicked is in column A and is a date
    If Target.Column = 1 And IsDate(Target) Then
'       Check to see if it is end of month date
        If Application.WorksheetFunction.EoMonth(Target.Value, 0) = Target.Value Then
            MsgBox "Yes"
        Else
            MsgBox "No"
        End If
    End If
        
End Sub

This should do what you want.

 

[Georgiboy]

Might also be worth adding the line:

Cancel = True

Under the line:

If Target.Column = 1 And IsDate(Target) Then

This will stop the default right click menu from popping up each time.

 

[Joe4]

Might also be worth adding the line:

Cancel = True

Under the line:

If Target.Column = 1 And IsDate(Target) Then

This will stop the default right click menu from popping up each time.

Yeah. I am not really sure why they are wanting to use right-click instead of the normal left-click/select, unless they purposely want the menu to open.

 

[robertvdb]

Thanks Joe and Georgiboy. Works fine !

 

[Joe4]

You are welcome.
Glad we were able to help!