vba code to clear contents of cell

**codesRus** · 07-16-2012, 11:38 AM

I would like the vba code that clears the content everytime a date is deleted from column D. Here is the code I have so far and would like to add the clear contents automatically for column G if column D (the date) is removed.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Cell As Range
If Not Intersect(Range("D:D"), Target) Is Nothing Then
If Target.Count > 1 Then Exit Sub
   If IsDate(Target.Value) And IsDate(Target.Offset(0, -1).Value) And IsEmpty(Target.Offset(0, 3)) Then
       Target.Offset(0, 3).Value = GetWorkDays(Target.Offset(0, -1).Value, Target.Value)
End If
End Sub
Function GetWorkDays(StartDate As Long, EndDate As Long) As Long
' returns the count of days between StartDate - EndDate minus Saturdays and Sundays
Dim d As Date, dount As Long
   For d = StartDate To EndDate
       If Weekday(d, vbMonday) < 6 Then
           dCount = dCount + 1
       End If
   Next d
   GetWorkDays = dCount
End Function

**Leith Ross** · 07-16-2012, 12:16 PM

Hello CodesRus,

Here is the amended macro. When you are working with either the Change or Selection Change events, you must disable Events and re-enable Events before exiting the macro if the code changes a a cell value or selects another cell. If you do not then Excel will most likely crash do to a cascade stack overflow error.


Private Sub Worksheet_Change(ByVal Target As Range)

    Dim Cell As Range
    
        If Target.Count > 1 Then Exit Sub
        
        If Not Intersect(Range("D:D"), Target) Is Nothing Then
            Application.EnableEvents = False
            
            If IsDate(Target.Value) And IsDate(Target.Offset(0, -1).Value) And IsEmpty(Target.Offset(0, 3)) Then
                Target.Offset(0, 3).Value = GetWorkDays(Target.Offset(0, -1).Value, Target.Value)
            ElseIf IsEmpty(Target) Then
                Target.Offset(0, 3) = ""
            End If
            
            Application.EnableEvents = True
        End If
        
End Sub

vba code to clear contents of cell

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

vba code to clear contents of cell

Re: vba code to clear contents of cell

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions