+ Reply to Thread
Results 1 to 3 of 3

Enable events on the worksheet's code page

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    03-05-2007
    Location
    Falmouth, VA now, Palm Bay, FL for 2 yrs, was Colorado Springs, CO for ten years; Cedark Park, TX; Zeeland, MI; Wilmette, IL; Princeton Junction, NJ; NY, NY
    MS-Off Ver
    365
    Posts
    615

    Enable events on the worksheet's code page

    I suspect I am running into a problem of either not understanding the limitations of Application.EnableEvents and/or a conflict with placing some of my code in the worksheet's code page instead of a module.

    With the following, if I don't have EnableEvents set to false, the writes to the same page trigger the change event (which I am using each time the target cell is changed to update a group of displayed values).

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim intCtr As Integer, intRow As Integer, intTblTopRow As Integer
    Dim rngLow As Range, rngHigh As Range
    Set rngLow = Worksheets("Rates").ListObjects("tblRateA").ListColumns("Low").DataBodyRange
    Set rngHigh = Worksheets("Rates").ListObjects("tblRateA").ListColumns("High").DataBodyRange
    intTblTopRow = Worksheets("Rates").ListObjects("tblRateA").HeaderRowRange(1, 1).Row
    
    Application.EnableEvents = False
    'Determine which row number we are working with
    If Target.Address = Worksheets("Calculate").Range("nmCost").Address And Target.Value > 0 Then
        For intCtr = 1 To rngLow.Row
            If Target.Value >= rngLow.Cells(intCtr, 1) And Target.Value <= rngHigh.Cells(intCtr, 1) Then
                intRow = intCtr
            End If
        Next
        Worksheets("Calculate").Range("D7") = Worksheets("Rates").ListObjects("tblRateA").DataBodyRange.Cells(intTblTopRow + intRow, 3)
        Worksheets("Calculate").Range("D8") = Worksheets("Rates").ListObjects("tblRateA").DataBodyRange.Cells(intTblTopRow + intRow, 4)
        Worksheets("Calculate").Range("D9") = Worksheets("Rates").ListObjects("tblRateA").DataBodyRange.Cells(intTblTopRow + intRow, 5)
        Worksheets("Calculate").Range("D10") = Worksheets("Rates").ListObjects("tblRateA").DataBodyRange.Cells(intTblTopRow + intRow, 6)
    End If
    
    Application.EnableEvents = True
    End Sub
    Unfortunately using Application.EnableEvents = False prohibits the lines
        Worksheets("Calculate").Range("D7") = Worksheets("Rates").ListObjects("tblRateA").DataBodyRange.Cells(intTblTopRow + intRow, 3)
        Worksheets("Calculate").Range("D8") = Worksheets("Rates").ListObjects("tblRateA").DataBodyRange.Cells(intTblTopRow + intRow, 4)
        Worksheets("Calculate").Range("D9") = Worksheets("Rates").ListObjects("tblRateA").DataBodyRange.Cells(intTblTopRow + intRow, 5)
        Worksheets("Calculate").Range("D10") = Worksheets("Rates").ListObjects("tblRateA").DataBodyRange.Cells(intTblTopRow + intRow, 6)
    from functioning. I thought that Application.EnableEvents = False would only prevent Events from running, not prevent code from posting values!

    I must be doing something fundamentally wrong here - Thoughts?

    Thank-you!

  2. #2
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: Enable events on the worksheet's code page

    I find that sheet specific macros struggle to make changes on another sheet.

    So I use a sheet specific macro to call a sub routine in a normal macro, and that subroutines makes my changes.
    My General Rules if you want my help. Not aimed at any person in particular:

    1. Please Make Requests not demands, none of us get paid here.

    2. Check back on your post regularly. I will not return to a post after 4 days.
    If it is not important to you then it definitely is not important to me.

  3. #3
    Forum Contributor
    Join Date
    03-05-2007
    Location
    Falmouth, VA now, Palm Bay, FL for 2 yrs, was Colorado Springs, CO for ten years; Cedark Park, TX; Zeeland, MI; Wilmette, IL; Princeton Junction, NJ; NY, NY
    MS-Off Ver
    365
    Posts
    615

    Re: Enable events on the worksheet's code page

    Thank-you!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Application.Enable Events Property stopping Workbook_Open() Event
    By Sc0ut in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-07-2016, 05:09 PM
  2. VBA code for ADD and CHANGE events for Combo Box on worksheet
    By carlliebenberg in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 05-18-2014, 06:16 AM
  3. Excel Vba Code To enable protected worksheet
    By hetal_upadhyay in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-19-2014, 09:25 AM
  4. [SOLVED] Application Enable Events question
    By kstrick99999 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 12-08-2012, 04:47 PM
  5. [SOLVED] Saving a worksheet without the page code
    By BrianSells in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-21-2012, 02:03 PM
  6. [SOLVED] Worksheet events and a little bit extra code
    By tinkerbellsmyhoe in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-27-2006, 10:00 AM
  7. combobox change event is running when enable events is false
    By tysop in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-24-2006, 10:20 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1