Cannot debug worksheet change even below. If invalid entry entered change event is inoperable unless workbook closed and reopened. Goal is to
1. Limit entries to VBA Array PTest (trying to avoid Data Validation, if possible?) 2. want to change column F to reflect date that corresponds to value inserted in PTest.
Is this doable? I cannot even debugg to test, any suggestions?
Please Login or Register to view this content.
x needs to be a Variant rather than Long, then
I can't begin to emphasize enough that you really oughta stop using so many public variables. For instance why is x public?Please Login or Register to view this content.
also in this code
your with statement doesn't do anything. the code oughta bePlease Login or Register to view this content.
note the periods that qualify the Cells property callsPlease Login or Register to view this content.
Josie
if at first you don't succeed try doing it the way your wife told you to
Thank You and will update asap. I really appreciate it. Will edit the Public variables also. I'm rebuilding this workbook, and was trying to create efficiencies while referring to the old model, as I was continually using items like TestsTaken and MyTests, etc.
Last edited by cmore; 10-22-2013 at 03:35 PM.
Tested; couple problems:
1. Index of array not working
2. Error Handling: Works for Enable events, but If Then statement is apparently useless and not resulting in desired fix?
Please Login or Register to view this content.
EURIKA, I've got it!!! But now how do I get change event to occur ever time value is entered into the cell, even if there's already a value there?
P.S. someone will have to tell me why Application.Match was required in one instance and Worksheet.Match accepted in another smh
Please Login or Register to view this content.
the change event occurs whenever you edit a cell (even if you don't actually change the value)
application.match and worksheetfunction.match do the same thing-the difference is in what happens when a match is not found. application.match returns an error value that you can test with IsError; worksheetfunction.match raises a run-time error that has to be handled with an On Error statement
Thank You! Question, what's your reference for finding out the answer for the match difference? Help? And can you clarify what you mean about the change event? Because right now if I put 3 in the workbook things will populate, but if I change to 5 nothing happens. I would think this is a change event and not a calculate event, because of the manual entry, etc. but based on your statement I'm dumbfounded.
I'm asking because the next challenge for me is 1. updating the cell and having items populate always (i.e. 3 to 5, etc.) but also when I clear contents I'd like to clear the entire row?
Thanks for your insight sir
sir?? ;-)
experience. I think it's mentioned on Chip Pearson's site and a few otherswhat's your reference for finding out the answer for the match difference?
exactly what I said. a manual entry will trigger the change event unless you have events disabledcan you clarify what you mean about the change event?
Aha! Thanks. Experience, hate when that's the answer. Ok, so I tried Disabling events, still same behavior, if initially null input triggers change event, if not empty, event does not fire.
first remove theline from your Build sub or you may end up stuck in a loop you can't terminatePlease Login or Register to view this content.
second your code only monitors one cell-column C and the row (teststaken + 7) which you update as soon as an entry is made in a row. so once you've made an entry you are only monitoring the cell below it. I don't really know why you set it up that way so can't advise what your best solution is-it may be just to monitor the whole column C
Quick question, why take off app.enabevent = true? Don't I have to cut it back on after I cut it off?
So I did that to ignore changes above the header. changed code to IfWorked perfectly for updating.Please Login or Register to view this content.
How would you do for delete on update
Last edited by cmore; 10-24-2013 at 05:25 PM.
So I got the lovely infinite loop........searching for solution
Possible Solution?
Please Login or Register to view this content.
Last edited by cmore; 10-24-2013 at 07:46 PM.
Updated, is there a better way?
Please Login or Register to view this content.
that only re-enables events in the Else clause-you should always re-enable events!
the only reason I got an infinite loop in your last sample workbook was because you re-enabled events at the start of the Build routine but I assume you removed that like I said?
Yes sir, and moved Build. Please see updated attached. WOndering if you see any problems or extraneous code
1 it's ma'am, not sir
2 you still haven't removed the
line from the start of the Build routinePlease Login or Register to view this content.
3 are you actually experiencing problems? I'm not gonna just peer review your code for you ;-) (especially not with all those public variables-they give me a headache)
My apologies, ma'am.
So I did remove from top Application.EnableEvents = False, so change doesn't trigger anything else. I then turn it on at different points to ensure code will fire on next change. No peer review, definitely experiencing issues as I'm trying to learn and build. but all help is muuuuch appreciated and needed. I wish I could be anyone's peer on this forum. I'm building this as a study tool and to learn and then will try to build some things at work.
and I promise I'm working towards removing as many public variables and named ranges as possible, I'm just moving from one sheet to the next
latest file is too big for some reason, and found a random bug that deletes A and B of summary on sheet change, will update and attach. THANKS for all help so far
*Finally realized you meant, Application.EnableEvents = True at the top of the Build sub.........deleted and face palm
Last edited by cmore; 10-29-2013 at 11:26 AM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks