Excel crashing

johnohio

New Member
Joined
May 12, 2005
Messages
22
Office Version
  1. 2016
Platform
  1. Windows
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!
 

Some videos you may like

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

NdNoviceHlp

Well-known Member
Joined
Nov 9, 2002
Messages
2,723
Don't know for sure but it seems...
Code:
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

Well-known Member
Joined
Mar 17, 2008
Messages
1,874
Office Version
  1. 2010
  2. 2007
Platform
  1. Windows
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...

Code:
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

New Member
Joined
May 12, 2005
Messages
22
Office Version
  1. 2016
Platform
  1. Windows
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

Well-known Member
Joined
Mar 17, 2008
Messages
1,874
Office Version
  1. 2010
  2. 2007
Platform
  1. Windows
Hello johnohio,

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

Watch MrExcel Video

Forum statistics

Threads
1,122,564
Messages
5,596,875
Members
414,106
Latest member
Tigretto

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top