VBA weekday function

[Trudzia84]

Hi
In cell A1 I have a date. I want to write a simple VBA code which will give the warning message box if the date in A1 is changed to weekend day. The code I wrote is not working and I am not sure what is wrong with it. Any suggestions?

 

[duggie33]

Hi *Trudzia84,

You need to use the "If not intersect" code.

Check out this link: If not intersect code

I hope that helps,

Doug

 

[Joe4]

Welcome to the Board!

The way you have written it, it thinks "A1" is a variable.
Try this:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$A$1" And Weekday(Target.Value, vbMonday) > 5 Then
        MsgBox "ggggg"
    End If
End Sub

Also, in the future, please do not post images of your code. We cannot copy that to our side. See here for how to post your code:

How to Post Your VBA Code

Post your VBA code.

www.mrexcel.com

 

[Trudzia84]

Welcome to the Board!

The way you have written it, it thinks "A1" is a variable.
Try this:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$A$1" And Weekday(Target.Value, vbMonday) > 5 Then
        MsgBox "ggggg"
    End If
End Sub

Also, in the future, please do not post images of your code. We cannot copy that to our side. See here for how to post your code:

How to Post Your VBA Code

Post your VBA code.

www.mrexcel.com

The code works as I wanted. There is only one problem I noticed: now if I change any cell value in the spreadsheet from numer to text I get VBA run time error 13, Type mismatch. Is there a way to fix this?

 

[Joe4]

Break up your IF into 2 so that it does not evaluate the date unless you are in cell A1, i.e.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$A$1" Then
        If Weekday(Target.Value, vbMonday) > 5 Then
            MsgBox "ggggg"
        End If
    End If
End Sub