+ Reply to Thread
Results 1 to 16 of 16

Worksheet_Change(ByVal Target As Range) works when cell updated manually but not with vba

  1. #1
    Forum Contributor
    Join Date
    07-11-2010
    Location
    Minneapolis, USA
    MS-Off Ver
    Excel 2016
    Posts
    308

    Worksheet_Change(ByVal Target As Range) works when cell updated manually but not with vba

    I am using the Worksheet change event to color cells who's content changes this work fine when cells are updated manually but if I update with vba the no coloring update happens even if cell content changes

    The code is in the worksheet module before the MatchReplace is run

    Thank you

    Worksheet Change Code
    Please Login or Register  to view this content.
    Code to update cells
    Please Login or Register  to view this content.

  2. #2
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Worksheet_Change(ByVal Target As Range) works when cell updated manually but not with

    This line is a bit of a giveaway as to why nothing happens

    Please Login or Register  to view this content.
    When you run your other code the count is greater than 1, this should fix it.

    Please Login or Register  to view this content.

  3. #3
    Forum Contributor
    Join Date
    07-11-2010
    Location
    Minneapolis, USA
    MS-Off Ver
    Excel 2016
    Posts
    308

    Re: Worksheet_Change(ByVal Target As Range) works when cell updated manually but not with

    Hello jason.b75

    I get the error
    Compile error:
    Argument not optional
    and the "Target" in
    Please Login or Register  to view this content.
    is highlighted

    I tried changing "Target" to "Range" but did not help

  4. #4
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Worksheet_Change(ByVal Target As Range) works when cell updated manually but not with

    OK, that's new!

    What did you do in order to trigger the code?

  5. #5
    Forum Contributor
    Join Date
    07-11-2010
    Location
    Minneapolis, USA
    MS-Off Ver
    Excel 2016
    Posts
    308

    Re: Worksheet_Change(ByVal Target As Range) works when cell updated manually but not with

    I call it like so
    Please Login or Register  to view this content.
    did this answer you question?

  6. #6
    Forum Contributor
    Join Date
    07-11-2010
    Location
    Minneapolis, USA
    MS-Off Ver
    Excel 2016
    Posts
    308

    Re: Worksheet_Change(ByVal Target As Range) works when cell updated manually but not with

    Does the sheet "OriginalData" need to be selected for the Worksheet_Change to work?

  7. #7
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Worksheet_Change(ByVal Target As Range) works when cell updated manually but not with

    It answers it, but unfortunately it doesn't explain why you encountered the error.

    Do you have any other code running that uses the variable "Target"?

    Any merged cells?

    I can't get the code to produce an error so I can't find the problem.

    edit:-

    In answer to your last question, no, it should work with any sheet selected.
    Last edited by jason.b75; 08-21-2015 at 01:33 PM.

  8. #8
    Forum Contributor
    Join Date
    07-11-2010
    Location
    Minneapolis, USA
    MS-Off Ver
    Excel 2016
    Posts
    308

    Re: Worksheet_Change(ByVal Target As Range) works when cell updated manually but not with

    Do you have any other code running that uses the variable "Target"?
    Yes in a different worksheet module I have
    Please Login or Register  to view this content.

  9. #9
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Worksheet_Change(ByVal Target As Range) works when cell updated manually but not with

    That shouldn't affect it (I hope).

    The line that produced the error, shouldn't produce that error, even if there was something wrong with the code. I'm just trying to see if there is something else that might be conflicting with it.

    Where does this line reside.

    Please Login or Register  to view this content.
    Is that in another module that you run?

  10. #10
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Worksheet_Change(ByVal Target As Range) works when cell updated manually but not with

    Actually, I just looked at your MatchReplace code in more detail, there appears to be a hole in there that need plugging.

    The variable lrow has no value assigned so the 2 ranges that it converts to arrays will be causing problems that could be part of the problem.

  11. #11
    Forum Expert skywriter's Avatar
    Join Date
    06-09-2014
    Location
    USA
    MS-Off Ver
    2016
    Posts
    2,760

    Re: Worksheet_Change(ByVal Target As Range) works when cell updated manually but not with

    The variable lrow has no value assigned so the 2 ranges that it converts to arrays will be causing problems that could be part of the problem.
    I'm not sure about that, if I re-create this code and remove option explicit to allow me to put lrow in the code it fails on the first line with lrow. I don't see how any of that would cause a problem with the worksheet change event.
    The worksheet change event is triggered by something on the worksheet changing (as far as I know), so either something changed or it didn't. I also don't see how target could be changed by having another variable somewhere else named target, since my understanding is target only can be the target cell(s) that changed as Excel passes that information in, maybe I'm wrong, but still it would be a range and for each c should not error. One thing for sure is I'm convinced your code should run no matter what.
    If not, then for my own knowledge I really want to know what's going on here.

    You declared it as a range and since something changed there should be a target(range).

    My take on this is #1 I would want to be sure the code that is having an error is actually in the worksheet module and not in a regular module and getting called from somewhere that we aren't aware of and target is not a range variable.

    The main problem with all of this is we don't have a sheet to see for ourselves. If the OP isn't capable of stepping through the code line by line and following the path of the code and assessing what the values, addresses, status of ranges, variables etc., then they need to post the sheet with the issues.
    Last edited by skywriter; 08-21-2015 at 02:51 PM.
    Click the * Add Reputation button in the lower left hand corner of this post to say thanks.

    Don't forget to mark this thread SOLVED by going to the "Thread Tools" drop down list above your first post and choosing solved.

  12. #12
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Worksheet_Change(ByVal Target As Range) works when cell updated manually but not with

    Quote Originally Posted by skywriter View Post
    I'm not sure about that, if I re-create this code and remove option explicit to allow me to put lrow in the code it fails on the first line with lrow. I don't see how any of that would cause a problem with the worksheet change event.
    That was my thought but I didn't actually test it, that said something must be happening for the event to trigger.

    Quote Originally Posted by skywriter View Post
    I also don't see how target could be changed by having another variable somewhere else named target, since my understanding is target only can be the target cell(s) that changed as Excel passes that information in, maybe I'm wrong, but still it would be a range and for each c should not error. One thing for sure is I'm convinced your code should run no matter what.
    I was wondering if there were any Target variables declared as Global / Public, something that would be available to all modules and retain a value when the procedure ends. I recall reading that there is a hierarchy for ambiguous variable names in this situation, but can't remember which gets priority.

    The only thing I can get to trip the code is by entering a formula that evaluates to an error in the sheet holding the code, which returns runtime error 13.

    I'm sure we can get to the bottom of this somehow.

  13. #13
    Forum Expert skywriter's Avatar
    Join Date
    06-09-2014
    Location
    USA
    MS-Off Ver
    2016
    Posts
    2,760

    Re: Worksheet_Change(ByVal Target As Range) works when cell updated manually but not with

    I recall reading that there is a hierarchy for ambiguous variable names in this situation, but can't remember which gets priority.
    Now I'm going to have to try some experiments with variables named Target.

    That was my thought but I didn't actually test it, that said something must be happening for the event to trigger.
    That's why I thought maybe the code was in a regular module and was being called and target was being passed in.
    I did a little experiment.
    All of the code below is in a regular module.

    Please Login or Register  to view this content.
    When I use the y as a worksheet, I get error 13 type mismatch right at the call line. When I use the range part it runs your code no problem.
    Last edited by skywriter; 08-21-2015 at 03:17 PM.

  14. #14
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Worksheet_Change(ByVal Target As Range) works when cell updated manually but not with

    I've just been looking into the possibility of conflicting variables, a variable declared within a procedure should be prioritised over one declared elsewhere unless explicitly referred to, so that would eliminate that as a possible cause.

    For information http://www.cpearson.com/excel/scope.aspx there is a section on resolving conflicts about halfway down the page.

  15. #15
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Worksheet_Change(ByVal Target As Range) works when cell updated manually but not with

    Going back to your earlier observations, the code must be in the worksheet module and running as an event rather than being called manually. See thread title and post #1.
    Last edited by jason.b75; 08-21-2015 at 03:34 PM.

  16. #16
    Forum Expert skywriter's Avatar
    Join Date
    06-09-2014
    Location
    USA
    MS-Off Ver
    2016
    Posts
    2,760

    Re: Worksheet_Change(ByVal Target As Range) works when cell updated manually but not with

    Going back to your earlier observations, the code must be in the worksheet module and running as an event rather than being called manually. See thread title and post #1.
    I'm not trying to offend anyone, but we haven't seen any of this for ourselves. I've gone back and forth with people that have no trouble shooting skills, trying to help them solve their problem. When I finally convince them to make the effort to post their sheet I find the problem in no time.

    If I could follow that code up to the error I think I could figure out the issue quickly.

+ 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. Replies: 5
    Last Post: 12-16-2014, 05:47 PM
  2. [SOLVED] multiple Sub Worksheet_Change(ByVal Target As Range)
    By hoss88 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-14-2014, 04:39 AM
  3. [SOLVED] Private Sub Worksheet_Change(ByVal Target As Range) End Sub
    By kanonathena in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-10-2013, 12:25 AM
  4. [SOLVED] Worksheet_Change(ByVal Target As Range) - set to only update when 3 target cells changed?
    By trillium in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-07-2012, 06:40 PM
  5. Date format in cell Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    By Hilton1982 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-15-2012, 03:22 AM
  6. Worksheet_CHange (ByVal Target as Range) when target is formula cell
    By coasterman in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-09-2012, 07:00 PM
  7. use of Worksheet_Change(ByVal Target As Range)
    By Isaac in forum Excel General
    Replies: 2
    Last Post: 07-07-2006, 02:10 PM

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