My Worksheet_Change is only partially working and I don't know why...

jmpatrick

Active Member
Joined
Aug 17, 2016
Messages
362
Office Version
  1. 365
Platform
  1. Windows
The macro that is supposed to run when data in the range CalendarFloorsInvoiceAmountColumn (FloorOrderNumber) only works if I delete the contents of a cell, not when I enter data in an empty cell in the named range. I can't figure out why but I'm sure it'll be embarrassing.

Here's my code:

VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count > 1 Or Target.HasFormula Then Exit Sub

    On Error Resume Next

    If Not Intersect(Target, Range("ElevationColumn")) Is Nothing Then

        Application.EnableEvents = False

        Target = UCase(Target)

        Application.EnableEvents = True

    End If
    
    If Target.Cells.Count > 1 Or Target.HasFormula Then Exit Sub

    On Error Resume Next

    If Not Intersect(Target, Range("SubLotColumn")) Is Nothing Then

        Application.EnableEvents = False

        Target = UCase(Target)

        Application.EnableEvents = True

    End If

    On Error GoTo 0

    Dim KeyCells As Range
    Set KeyCells = Range("DateColumn")
    If Not Application.Intersect(KeyCells, Range(Target.Address)) Is Nothing Then
    
    Application.EnableEvents = False
    
    Call ChangeDate
    
    Application.EnableEvents = True
    
    End If
       
    Set KeyCells = Range("CalendarFloorsInvoiceAmountColumn")
    If Not Application.Intersect(KeyCells, Range(Target.Address)) Is Nothing Then
    
    Application.EnableEvents = False
    
    Call FloorOrderNumber
    
    Application.EnableEvents = True
    
    End If
    
End Sub
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
2,828
Office Version
  1. 2013
Platform
  1. Windows
It works for me. To find out the cause set a breakpoint on your event handler en step through the code after entering data in an empty cell.
 

Marc L

Banned User
Joined
Apr 5, 2021
Messages
2,030
Office Version
  1. 2010
Platform
  1. Windows
First remove all On Error codelines in order you can see where the logic may fail …​
 

jmpatrick

Active Member
Joined
Aug 17, 2016
Messages
362
Office Version
  1. 365
Platform
  1. Windows
I figured this out! I was triggering the macro by entering data. When I hit enter it selected the next cell down as the starting point for the code, which wasn't what I wanted. Easy fix!

Thanks for your help.
 

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
2,828
Office Version
  1. 2013
Platform
  1. Windows
You are welcome and thanks for letting us know (y)
 

Forum statistics

Threads
1,181,427
Messages
5,929,828
Members
436,697
Latest member
sunnypl

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