current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 1 down vote favorite

I have a VBA macro that is intended to format a specified range of cells automatically for the user and it does so correctly. However when the user tries to delete a row in the specified range it triggers the error message I built in as an infinite loop.

The code looks like this:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rTimeCells As Range

    Set rTimeCells = Range("D2:G15")

    Application.EnableEvents = False


    If Not Application.Intersect(rTimeCells, Range(Target.Address)) Is Nothing Then
            Call Time_Format(Range(Target.Address))
    End If

    Application.EnableEvents = True

    End Sub

    Private Sub Time_Format(rCells As Range)
    Dim RegEXFormat1 As Object
    ...
    Dim RegEXFormatX As Object

    Set RegEXFormat1 = CreateObject("VBScript.RegExp")    
    ...
    Set RegEXFormatX = CreateObject("VBScript.RegExp")


    RegEXFormat1.Global = True
    ...
    RegEXFormatX.Global = True


    RegEXFormat1.IgnoreCase = True
    ...
    RegEXFormatX.IgnoreCase = True

    RegEXFormat1.Pattern = "..."
    ...
    RegEXFormatX.Pattern = "..."


    For Each cell In rCells
        Dim sValue As String
        Dim sMonth As String
        Dim sDay As String
        Dim sYear As String
        Dim bCorrectFormat As Boolean

        bCorrectFormat = True
        sValue = CStr(cell.Text)

        If (RegEXFormat1.test(sValue)) Then
        ...
        ElseIF(RegEXFormatX.test(sValue) Then
        ...
        Else
            If (sValue = "" Or sValue = "<Input Date>") Then
            Else
                MsgBox ("Please Input the date in correct numeric format")
                cell.value = "<Input Date>"
        End If
    Next

The user insists that they need the ability to delete rows of data without sending this macro into a loop. How can I modify what I have here to allow for this occurrence?

(I clearly modified the code and left a lot out that I dont feel was necessary here and to stop my post from being pages and pages long.)

share|improve this question
    
+1 "The user insists that they need the ability to delete rows of data without sending this macro into a loop. " –  Brad Apr 7 at 18:55
add comment

1 Answer

up vote 1 down vote accepted

Instead of this:

If Not Application.Intersect(rTimeCells, Range(Target.Address)) Is Nothing Then
     Call Time_Format(Range(Target.Address))
End If

you probably want to limit the range you pass to Time_Format using something like this:

Dim rngTime as Range
Set rngTime = Application.Intersect(rTimeCells, Target)
If Not rngTime Is Nothing Then
     Call Time_Format(rngTime)
End If

Note: Range(Target.Address) is equivalent to Target

share|improve this answer
    
I like that, it is a better way to check for intersects. However, it does not solve my problem. –  Dead_Jester Apr 8 at 13:28
    
Since you're suspending event handling during processing the change, where is your loop coming from? Maybe I'm missing something in your question... –  Tim Williams Apr 8 at 15:03
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.