Excel crashing

[johnohio]

I am trying to get Excel to run a macro whenever anything changes in column A from row 4 or below.
I had it working and then I added this line:
openi.Cells(myrow, 1) = UCase(openi.Cells(myrow, 1))
and now if no longer runs, usually it crashes Excel.

Here is the full code:


Private Sub Worksheet_Change(ByVal Target As Range)
Dim KeyCells As Range
Set KeyCells = Range("A4:A99999")

If Not Application.Intersect(KeyCells, Range(Target.Address)) _
Is Nothing Then

Dim openi As Worksheet
Set openi = Worksheets("OPEN")

myrow = ActiveCell.Row - 1
openi.Cells(myrow, 1) = UCase(openi.Cells(myrow, 1)) '<<<<<<<<<!!!!!!!!!
openi.Cells(myrow, 2) = "=IF(ISNA(VLOOKUP(A" & myrow & ",CUSTINFO!A:B,2,FALSE)),""??"",(VLOOKUP(A" & myrow & ",CUSTINFO!A:B,2,FALSE)))"

If openi.Cells(myrow, 2) = "??" Then
MsgBox ("Customer not found. If customer is new add them to the CUSTINFO tab")
Exit Sub

End If

openi.Cells(myrow, 3).Select

With Sheets("OPEN")
.Columns("A:A").HorizontalAlignment = xlCenter
.Columns("A:A").VerticalAlignment = xlCenter
End With

End If

End Sub





Thanks in advance for any help!

 

[NdNoviceHlp]

Don't know for sure but it seems...

myrow = ActiveCell.Row - 1
 openi.Cells(myrow, 1) = UCase(openi.Cells(myrow, 1))

...that if a Row 1 cell is selected then myrow = 0 which would mean that the cell row address ("myrow") is an impossibility. HTH. Dave

 

[Leith Ross]

Hello johnohio,

When using either the Worksheet_Change or the Worksheet_SelectionChange events, you can create what is known as a cascade failure. This happens when you write to a cell or select a different cell in your macro. To prevent this from happening you can suspend all worksheet events until your code has finished. You must restore the events before exiting your procedure at any point in your code.

Try this and see if it works...

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim KeyCells    As Range
    Dim myrow       As Long
    Dim openi       As Worksheet
    
        Application.EnableEvents = False
        
        Set KeyCells = Range("A4:A99999")


        If Not Application.Intersect(KeyCells, Range(Target.Address)) Is Nothing Then
            Set openi = Worksheets("OPEN")
            
            myrow = ActiveCell.row - 1
            
            openi.Cells(myrow, 1) = UCase(openi.Cells(myrow, 1)) '<<<<<<<< openi.Cells(myrow, 2) = "=IF(ISNA(VLOOKUP(A" & myrow & ",CUSTINFO!A:B,2,FALSE)),""??"",(VLOOKUP(A" & myrow & ",CUSTINFO!A:B,2,FALSE)))"
                If openi.Cells(myrow, 2) = "??" Then
                    MsgBox ("Customer not found. If customer is new add them to the CUSTINFO tab")
                    GoTo Finished
                End If


            openi.Cells(myrow, 3).Select


            With Sheets("OPEN")
                .Columns("A:A").HorizontalAlignment = xlCenter
                .Columns("A:A").VerticalAlignment = xlCenter
            End With
        End If


Finished: Application.EnableEvents = True


End Sub

 

[johnohio]

Hello johnohio,

When using either the Worksheet_Change or the Worksheet_SelectionChange events, you can create what is known as a cascade failure. This happens when you write to a cell or select a different cell in your macro. To prevent this from happening you can suspend all worksheet events until your code has finished. You must restore the events before exiting your procedure at any point in your code.

Try this and see if it works...

Nope, still now wokring

 

[Leith Ross]

Hello johnohio,

Can you post a copy of the workbook for review? It would make it easier to identify and correct the issue.