Application Error

[Nalani]

I am receiving an EXCEL.EXE - Application Error msg box with:

Application has generarated an exception that could not be handled.

Process ID=0x168c(5772, ThreadID=0x10a4(4260).

Click OK to terminate
Click CANCEL to debug

When I click CANCEL:
I get msg box saying - No registered JIT debugger was specified.

Click on Retry to have the process wait while attaching a debugger manually.
Click on Cancel to abort the JIT degug request.

Clicking on Retry takes forever to find anything that I just closed out Excel.
Clicking on Cancel will give me a msg box saying the program stopped working correctly; with a choice to Debug or Close. I closed.

This workbook is one that I made for a fellow employee's Church which has alot of macros and Userforms in it. It was working just fine until recently.

I think I have narrowed it down to a Worksheet_Change event:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng1 As Range, rng2 As Range
If Not Intersect(Target, Columns(2)) Is Nothing Then
        Set rng1 = Intersect(Target, Columns(2))
        Set rng2 = Intersect(ActiveSheet.UsedRange, rng1)
        On Error Resume Next
        For Each cell In rng2
            If cell.Formula <> "" Then
                cell.Formula = Format(StrConv(cell.Formula, vbProperCase))
            End If
        Next cell
    End If
End Sub

Whenever something is placed in column B, either through the UserForm that populates the row or manually, is when I have the msg box problems described above.

What I am trying to do is: Get Proper Case on all entries for that row.

I'm sure theproblem lies in the code above, but don't know where the fix is.

 

[Jerry Sullivan]

The problem might be that your Worksheet_Change code is triggering another Worksheet_Change Event while running.

If there is a possibility of that happening, it's best that your macro temporarily disable events and then re-enable them before ending.

You do this using the Application.EnableEvents Property.

Here is a revised version that also cleans up a few things...

Option Explicit
 
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rng1 As Range, c As Range
    If Not Intersect(Target, Columns(2)) Is Nothing Then
        Application.EnableEvents = False
        Set rng1 = Intersect(Target, Columns(2))
        On Error Resume Next
        For Each c In rng1
            If c.Formula <> "" Then
                c.Formula = StrConv(c.Formula, vbProperCase)
            End If
        Next c
        Set rng1 = Nothing
        Set c = Nothing
        Application.EnableEvents = True
    End If
End Sub

What I am trying to do is: Get Proper Case on all entries for that row.

The code in your post looks like it was intended to apply ProperCase only to items in Column B.

Is that what you want, or are you trying to apply that to all Cells in the Row?

Please explain that part in more detail if you would like some more help.

For example, if you copy-paste values into Cells(C4:F4),
do you want all the Cells on Row 4 to be Proper Case or just Cells(C4:F4)?

 

[Nalani]

Thanks for the reply JS411,

I was working on this last night and had probably turned off my computer just before you posted.

The problem might be that your Worksheet_Change code is triggering another Worksheet_Change Event while running.

That was not the problem, because I only had that one Change Event in the whole book. And only this book open.

Either way, I solved my problem with this (may be another way to write it, but it does work)

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rng1 As Range
    Dim cell As Range
    Application.EnableEvents = False
    Set rng1 = Application.Intersect(Target, Range("B:B, C:C, E:E, F:F, G:G"))
    If Not rng1 Is Nothing Then
        For Each cell In rng1.Cells
            If Len(cell.Value) > 0 Then _
               cell.Value = Application.Proper(cell.Value)
        Next cell
    End If
    Application.EnableEvents = True
End Sub

Which should answer your questions at the end of your post.

Thanks again for your post.

 

[Jerry Sullivan]

Glad to hear that you were able to solve this Nalani.
Inheriting legacy code can be very challenging!

The problem might be that your Worksheet_Change code is triggering another Worksheet_Change Event while running.

That was not the problem, because I only had that one Change Event in the whole book. And only this book open.

Just to clarify this point... Even if you only have one Event Procedure in all open workbooks,
you need to disable events if the excution of that Procedure might trigger another Event.

You can confirm that the hard way by taking away the Application.EnableEvents = False from your revised code...
it will cause a stack overflow when you enter a value in Column B.

To test this safely, you can add a counter to see that it will loop 100 times before stopping....

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rng1 As Range
    Dim cell As Range
 
    Application.EnableEvents = True
     If Range("A1") > 100 Then
          Exit Sub
     Else
          Range("A1") = Range("A1") + 1
     End If
 
    Set rng1 = Application.Intersect(Target, Range("B:B, C:C, E:E, F:F, G:G"))
    If Not rng1 Is Nothing Then
        For Each cell In rng1.Cells
            If Len(cell.Value) > 0 Then _
               cell.Value = Application.Proper(cell.Value)
        Next cell
    End If
    Application.EnableEvents = True
End Sub

This might have been the cause of your troubles described in your OP.

Good luck!