Results 1 to 3 of 3

Thread: Worksheet_Change: Caused by User or VBA?

  1. #1
    Acolyte Nick Burns's Avatar
    Join Date
    May 2017
    Posts
    99
    Articles
    0
    Excel Version
    Office 365

    Worksheet_Change: Caused by User or VBA?



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Is there a way to determine if the User is editing the worksheet or if VBA is manipulating it?
    Both actions cause the Worksheet_Change event to fire, but I want certain actions to happen when it's being changed by the user vs VBA.

    Thanks
    Oh... by the way, YOU'RE WELCOME!

  2. #2

  3. #3
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,742
    Articles
    0
    Excel Version
    365
    I thought that Application.Caller might come to the rescue but it doesn't.
    My first thoughts on this are to use a workaround involving a global variable:
    You get the vba which is about to do something on a sheet to set a global variable (let's call it VBAisDoingThis) to True, then when it has finished making changes to the sheet set it to False.
    In the Sheet_Change event handler, include a statement which looks at the global variable VBAisDoingThis to determine whether VBA is doing this or a user is doing it.

    I have used this sort of thing to be more selective than the blanket Application.EnableEvents=False when deciding what event handlers do.

Posting Permissions

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