CODEWITHSUNDEEP
vbaexcel
Remi
Remi

Reputation: 169

How do I change my macro into a Worksheet_Change event Excel VBA

I have a macro that I call when the workbook closes. It checks the columns in two tables on separate worksheets and assigns row numbers based on what it finds.

Worksheet_Change handler is located on the sheet with Projects range. Database range is located on another worksheet in the same workbook.

Whenever I call the macro anywhere else, it either generates an error or causes an usual bug where excel is partially frozen (anyone know what the hell this is?!?!?!).

Anyway, my last resort before giving up is to change the macro into a worksheet change event and I was wondering if I could get some help creating this.
The original macro: 

Sub FindRow()
'This module verifies row numbers in the database by matching them to the opportunities in the Projects
'worksheet. It then assigns row numbers in the Projects worksheet.

    Application.ScreenUpdating = False
    Dim LastRow As Long

    LastRow = Application.ThisWorkbook.Sheets("Projects").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

    Dim rng As Range
    Dim foundRng As Range
    For Each rng In Sheets("Projects").Range("B2:B" & LastRow)
        Set foundRng = Sheets("Database").Range("C:C").Find(rng, LookIn:=xlValues, lookat:=xlWhole)
        If Not foundRng Is Nothing Then
            rng.Offset(0, -1) = foundRng.Row
        End If
    Next rng
    Application.ScreenUpdating = True
End Sub

My proposed change:

 Public Sub Worksheet_Change(ByVal Target As Range)
     Dim Records As Range

        Set Records = Range("Records")
        If Not Application.Intersect(Records, Range(Target.Address)) Is Nothing Then
        Application.ScreenUpdating = False
        Dim LastRow As Long    
        LastRow = Application.ThisWorkbook.Sheets("Projects").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

        Dim rng As Range
        Dim foundRng As Range
        For Each rng In Sheets("Projects").Range("B2:B" & LastRow)
            Set foundRng = Sheets("Database").Range("C:C").Find(rng, LookIn:=xlValues, lookat:=xlWhole)
            If Not foundRng Is Nothing Then
                rng.Offset(0, -1) = foundRng.Row
            End If
        Next rng
        Application.ScreenUpdating = True
End If
    End Sub

However, I keep getting an error on the line that defines the variable LastRow.
I get an application-defined error even though everything is defined properly before.

Thanks in advance.

Upvotes: 1

Views: 66

Answers (2)

Mathieu Guindon
Mathieu Guindon

Reputation: 71187

Worksheet_Change handles is located on the sheet with Projects range. Database range is located on another worksheet in the same workbook. – Remi 1 min ago

This means rng is also on the Projects sheet:

For Each rng In Sheets("Projects").Range("B2:B" & LastRow)

(BTW Me.Range("B2:B" & LastRow) would have been much less ambiguous here)

You're handling a Worksheet_Change event on the Projects sheet, which Excel fires whenever a cell value changes on the Projects sheet. Then inside that handler, you do this:

rng.Offset(0, -1) = foundRng.Row

With rng being a range on the Projects sheet, you're entering a recursive cycle of sheet changes, and that is likely what's crashing your code.

When you make worksheet changes while handling worksheet changes, you need to tell Excel "it's okay, I got this", by preventing it from re-firing the Worksheet.Change event every time:

Application.EnableEvents = False

'...code...

Application.EnableEvents = True

Moreover, when you toggle Application.ScreenUpdating = False, you're telling Excel "don't repaint yourself until I say so" - that can speed things up considerably, in a lot of cases, however it also means you need to toggle it back on manually if something bad happens.

You can avoid this by implementing an error handler - here's the principle:

Sub DoSomething()
    On Error GoTo CleanFail
    Application.EnableEvents = False
    Application.ScreenUpdating = False

    '...code...

CleanExit:
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    Exit Sub
CleanFail:
    Debug.Print Err.Description
    Stop
    Resume CleanExit
    Resume 'F8 takes you to the error-throwing statement
End Sub

Upvotes: 3

Davesexcel
Davesexcel

Reputation: 6984

You have not indicated what sheet is the activesheet that has the worksheet_change event.

Any way, here is a code to find the last row in column B sheet "Projects"

    Dim LastRow As Long, sh As Worksheet

    Set sh = Sheets("Projects")

    LastRow = sh.Cells(sh.Rows.Count, "B").End(xlUp).Row
    MsgBox LastRow & " is the last row in Column B Sheet Projects!"

Upvotes: 0

Related Questions