• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Msgbox if Cells(ActiveCell.row, "K") falls on Weekend?

Excelnoub

Excelnoub

Member
I have the code to restric the dates if they fall on a weekend in a Userform but what about on an activecell?

I have the following code in my Private Sub Worksheet_Change(ByVal Target As Range)

Code: 
If Not Intersect(Target, Range("I5:I20")) Is Nothing Then
      Application.EnableEvents = False
      For Each rcell In Intersect(Target, Range("I5:I20")).Cells
        If Len(rcell.Value) > 0 Then
            Cells(ActiveCell.row, "K").FormulaR1C1 = "=IF(RC9="""","""",RC9+40)"
            Cells(ActiveCell.row, "J").Value = Cells(ActiveCell.row, "K").Value
            Cells(ActiveCell.row, "K").Value = Cells(ActiveCell.row, "J").Value
        Else
        'If nothing is in the active cell I, then it will remove anything that is in the Active Row, Column K and J
            Cells(Target.row, "K").ClearContents
            Cells(Target.row, "J").ClearContents
        End If
      Next rcell
      Application.EnableEvents = True
  End If

I need that if I add a date in the ActiveCell.Row I, to look in my ActiveCell, "K" and let me know if the date inserted falls on a Weekend, If yes then to have a Msgbox. But if not to continue the code as it should (Exiting sub I presume).

The problem is a do not know how to get this code to work... and where to add this. Should this go after the fact or in the code above?

I have searched all over the net but I need it for my if Cells(ActiveCell.row, "K") falls on Weekend.

Something like this:

Code: 
If Weekday() = vbSaturday Or Weekday() = vbSunday Then
        MsgBox "Date cannot fall on a weekend, please try again"
End If
 
Hi Excelnoub,

Not sure about VBA (definitely some body will reply) but the data validation can be achieved with below formula in Custom Data Validation .

=SUMPRODUCT((WEEKDAY(H14,2)<>6)*(WEEKDAY(H14,2)<>7))

Extend the same to whole column. Here I started in H14.

Regards,
 
Many ways to do it via VB, but if you want a simple function:
Code: 
Function IsWeekday(d As Date) As Boolean
IsWeekday = (WorksheetFunction.Weekday(d, 2) < 6)
End Function

Then, anywhere in your code you needed to test, you would do something like:
Code: 
If IsWeekday(Cells(Target.Row,"A").Value) Then
'It's a weekday, back to work...
Else
'It's the weekend, party time!
End If
 
This will work :D

Thank you so much Luke M

Code: 
  If Not Intersect(Target, Range("I5:I20")) Is Nothing Then
      Application.EnableEvents = False
      For Each rcell In Intersect(Target, Range("I5:I20")).Cells
        If Len(rcell.Value) > 0 Then
            Cells(ActiveCell.row, "K").FormulaR1C1 = "=IF(RC9="""","""",RC9+40)"
            Cells(ActiveCell.row, "J").Value = Cells(ActiveCell.row, "K").Value
            Cells(ActiveCell.row, "K").Value = Cells(ActiveCell.row, "J").Value
                If IsWeekday(Cells(Target.row, "K").Value) Then
                Else
                    MsgBox "Actual Date cannot fall on a weekend, please try again"
                    Cells(ActiveCell.row, "J").Select
                End If
        Else
        'If nothing is in the active cell I, then it will remove anything that is in the Active Row, Column K and J
            Cells(Target.row, "K").ClearContents
            Cells(Target.row, "J").ClearContents
        End If
      Next rcell
      Application.EnableEvents = True
  End If
 
You must log in or register to reply here.
Back
Top