+ Reply to Thread
Results 1 to 8 of 8

Event Change Macro - Improve my Code?

  1. #1
    Registered User
    Join Date
    12-21-2018
    Location
    England
    MS-Off Ver
    Office 365
    Posts
    6

    Event Change Macro - Improve my Code?

    Hi All,

    Hope all is well.

    Intermediate VBA writer here looking for some fine tuning on my Event Change code if anyone would be willing?

    Unfortunately the code seems to make data entry in the worksheet slower - 1-2 seconds between inputting.

    The code is meant to keep an audit trail of changes to two specific columns and check for duplicates between these columns on the same row.

    Please Login or Register  to view this content.

    A general question as well please - can you code an Event Change macro to only run if certain cells are changed in a worksheet rather than any cell - another approach to eliminate the issue!


    Many thanks in advance for any advice - and Merry Christmas all!

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,086

    Re: Event Change Macro - Improve my Code?

    can you code an Event Change macro to only run if certain cells are changed in a worksheet rather than any cell
    Not as far as I know. The change event handler will fire for every change. You have to monitor the cells you are interested in manually. You already have the code you need ... perhaps you just need to move it to the start of the module.
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Registered User
    Join Date
    12-21-2018
    Location
    England
    MS-Off Ver
    Office 365
    Posts
    6

    Re: Event Change Macro - Improve my Code?

    Quote Originally Posted by TMS View Post
    Not as far as I know. The change event handler will fire for every change. You have to monitor the cells you are interested in manually. You already have the code you need ... perhaps you just need to move it to the start of the module.
    Hi TMS. Thanks for the quick response. You not moving the code to the start of the module - can you clarify please? Not sure I follow!

    Thanks :-)

  4. #4
    Forum Expert mikerickson's Avatar
    Join Date
    03-30-2007
    Location
    Davis CA
    MS-Off Ver
    Excel 2011
    Posts
    6,229

    Re: Event Change Macro - Improve my Code?

    One thing that I noticed is that the msgbox "authorizations duplicated" is inside your loops. That means that if you have multiple instances you will get multiple msgboxes.

    But, the msgbox is only to inform you of a duplication, the extra boxes aren't needed.

    You could set a variable and use that to trigger one message, no matter how many duplicates there are

    Please Login or Register  to view this content.
    _
    ...How to Cross-post politely...
    ..Wrap code by selecting the code and clicking the # or read this. Thank you.

  5. #5
    Forum Expert
    Join Date
    10-06-2017
    Location
    drevni ruchadlo
    MS-Off Ver
    old
    Posts
    2,143

    Re: Event Change Macro - Improve my Code?

    Quote Originally Posted by puppet_master2501 View Post
    ... some ... tuning on my Event Change code ...
    Using mikerickson's solution and shortening the duplicate code ("If Not Intersect ...", "For ... Each ... Next"), it could look like this:
    Please Login or Register  to view this content.
    ... but, how the "change" procedure can be started at all, if the worksheet is protected and you can not enter anything into its cells before you unlock/unprotect the worksheet ? What "mechanism" allows you to perform the "change" procedure ? Do you have any cells there that are not blocked ?
    Last edited by mjr veverka; 12-21-2018 at 09:37 PM.

  6. #6
    Registered User
    Join Date
    12-21-2018
    Location
    England
    MS-Off Ver
    Office 365
    Posts
    6

    Re: Event Change Macro - Improve my Code?

    Quote Originally Posted by porucha vevrku View Post
    Using mikerickson's solution and shortening the duplicate code ("If Not Intersect ...", "For ... Each ... Next"), it could look like this:
    Please Login or Register  to view this content.
    ... but, how the "change" procedure can be started at all, if the worksheet is protected and you can not enter anything into its cells before you unlock/unprotect the worksheet ? What "mechanism" allows you to perform the "change" procedure ? Do you have any cells there that are not blocked ?

    Hi porucha vevrku - many thanks for the detailed code.

    In the worksheet only certain cells are protected - including a range permission restricting users ability to input into cells unless they are named in the permissions list.

    The code is a safety and audit measure to check whom is inputting into the permission controlled range so that - if for any reason someone other than those named could populate into it, the system creates a stamp so it can be investigated.


    In regards to your code I will go away and test - I have not seen
    Please Login or Register  to view this content.
    before!


    Thanks all for your input - if you have any more or alternative code it would be interesting to see the approaches!


    EDIT: To check should the above code have a
    Please Login or Register  to view this content.
    and
    Please Login or Register  to view this content.
    lines above the errorhandler: code?

    EDIT: Also I have note come across defining variables like this before:
    Please Login or Register  to view this content.
    - could you please explain this?

    EDIT: Lastly think
    Please Login or Register  to view this content.
    needs to be changed to
    Please Login or Register  to view this content.
    for the duplication check to work correctly.

    PASS123 - Copy.xlsm
    Last edited by puppet_master2501; 12-22-2018 at 05:52 AM.

  7. #7
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,086

    Re: Event Change Macro - Improve my Code?

    Thanks for the rep.

  8. #8
    Forum Expert
    Join Date
    10-06-2017
    Location
    drevni ruchadlo
    MS-Off Ver
    old
    Posts
    2,143

    Re: Event Change Macro - Improve my Code?

    Quote Originally Posted by puppet_master2501 View Post
    1. ... To check should the above code have a "Application.EnableEvents = True" and "Exit Sub" lines above the "errorhandler:" code?

    2. ... variables like this ... "Dim col%, c As Range, dupCount%: dupCount = 0" ... could you please explain this?

    3. ... "If Target.Column = 1 Then col = 5 Else col = -5" needs to be changed to "If Target.Column = 1 Then col = 5 Else col = -4" for the duplication check to work correctly ...
    Ad 1. No ... what for ?
    If the macro works without error, then all locks/blockades, made at the outset, will be unlocked at the end of the macro.
    The error message will not appear because there is a "If Err.Number <> 0 ..." clause.
    If at the end, but before "errorhandler:", there was "exit sub", then the locks/blockades would not be removed and, for example: "Worksheet_Change" would not work. It would be necessary to use additional "GoTo" and expand/enlargement the code.

    Ad 2. Dim col% (i.e. As Integer) - needed for "If Target.Column = 1 Then col = 5 Else col = -5"
    Dim c As Range ... ... it was "your declaration" for "For Each c In Target"
    Dim dupCount%: dupCount = 0 - counter declaration for "duplicates" with initial value = 0.
    ... each variable (and constant) should be declared.

    Ad 3. Ok ... if it's necessary ... in your code it was:
    If c.Value = c.Offset(0, 5).Value Then ... for Columns(1)
    If c.Value = c.Offset(0, -5).Value Then ... for Columns(5)
    "that and I left it" ...

+ 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. Need to change sample code columns for subtotals and the column for a change event
    By JoeVanGeaux in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-09-2017, 09:02 PM
  2. How to improve this word-macro code
    By IonutC in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-29-2016, 09:28 AM
  3. [SOLVED] Change Event Code too long - Need to combine 2 events into 1 macro
    By Sc0tt1e in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-15-2014, 03:57 AM
  4. [SOLVED] Code working as change event, but not as a macro.
    By Nitefox in forum For Other Platforms(Mac, Google Docs, Mobile OS etc)
    Replies: 20
    Last Post: 12-21-2013, 06:25 PM
  5. [SOLVED] Stop Worksheet change event from interfering with macro code
    By BeachRock in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-10-2013, 09:11 PM
  6. Request macro code - when cell change event
    By Rhey1971 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-01-2006, 06:55 AM
  7. Replies: 5
    Last Post: 06-23-2005, 06:05 PM

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