Message box macro not working
I have the following code that I want to have a message box pop up if the value of cell H104 is more or less than the value of cell F104 by more than 0.02. I have been playing with this code for about 3 days now but can't seem to get it to work. Any Ideas?
Please Login or Register to view this content.
Last edited by scottwhittaker2333; 06-12-2010 at 07:08 PM.
I'm not sure why you have a for loop running on one cell?
replace you code with this..
Please Login or Register to view this content.
Please Login or Register to view this content.
Or possibly.......?
Please Login or Register to view this content.
[EDIT]
I think it was how you defined your tolerances that was causing the problem.
This code should be okay now.
Hope this helps.
If you need any further information, please feel free to ask further
However
If this takes care of your needs, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED. It helps everybody!
Also
If you are satisfied by any members response to your problem please consider using the scales icon top right of thier post to show your appreciation.
Last edited by Marcol; 06-11-2010 at 08:09 PM. Reason: Tolerances corrected
Or ...
Please Login or Register to view this content.
Entia non sunt multiplicanda sine necessitate
Ihave tested all of the above posts and none of them did anything. The las one returned some error so I changed it to this, however that did not work either.
I really am at a loss for why this isn't working. I have several other codes that are almost Identical to the code in my first post and they work fine.Please Login or Register to view this content.
Where are you putting the code?
Demo workbook attached
[EDIT]
Code amended to allow for Delete
Please Login or Register to view this content.
Attachment updated
[EDIT]
See shgs' post #8
If the code is in the module ThisWorkbook
Then it will work as it stands in any sheet in the workbook.
If you only want it to apply to a specific sheet then the code must go in the module for that sheet.
Thanks shg
Last edited by Marcol; 06-11-2010 at 09:49 PM. Reason: Added demo workbook, trouble attaching file
The Workbook_SheetChange event has to go in ThisWorkbook.
If you change the signature to
... then it goes in the Sheet module.Please Login or Register to view this content.
My oversight shg
Apologies
Post amended to suit
Thanks for the assistance and I realize that the code is for the workbook and thats were it is. There must be something else that is causing the macro to not work. It is in the workbook_sheetchange and in the correct location but it is there with many other macros I wonder if that has something to do with it. I know that if there is to much it will say pocerdure to large and I have not gotten that worning so I do not think thats it. I will attach a copy of what I am using and you can take a look at that. Maybe you will see what I am missing. As I said there is alot of code in the workbook so scroll all the way to the bottom and you will see your code not working.
hmmm ......
At first glance your code is somewhat over complicated, the continual use of
means in all probability the sub has done just that before it gets to the code you are trying to add.Please Login or Register to view this content.
How much off your code actually comes into use? I suspect there will be other chunks that are not triggered.
Try putting the new code at the beginning of your code to see what I mean, then only it will work!!!!
You need at the very the least to use If > ElseIf > Else statements, or better still Select Case.
Are there other sheets in the workbook, if so the code you are using is triggered on every one!
I again am only guessing at this stage, but I think the Sheet_Change is a better place for your code.
Why do you have
in a stndard module, Module3, and not in the module ThisWorkbook ?Please Login or Register to view this content.
I'll have a further look later, but this will take some time to sort out.
[EDIT]
Why so many merged cells?
They are a major source of problems in my opinion
Last edited by Marcol; 06-12-2010 at 06:20 AM.
I just placed SHG's code in the worksheet module, and it worked fine
Please Login or Register to view this content.
Last edited by davesexcel; 06-12-2010 at 09:52 AM.
They all do!
The problem seems to be the amount of unneeded code, esp Exit Sub
The code has been inserted at the end of the procedure and is is being exited before it is triggered, due to other events being triggered with the change event.
Scott
Do you really need so many cells checked and changed if only one cell is changing?
This will slow excel down considerably.
Put the chosen code at the beginning and modify the conditions to trigger it and all is okay
Please Login or Register to view this content.
What are the tolerances required in L104,P104,T104 & X104?
I suspect the will use the same or similar code to H104
Amended workbook attached.
I'm trimming some of the code in another copy of the file and will post it later.
I hope this helps in the meantime.
Last edited by Marcol; 06-12-2010 at 10:32 AM. Reason: Question on tolerances added
I am confused. I opened the workbook you attached and the scale calibration macro still didn't do anything even though it was at the top of the code. Did it work for you?
As for the other codes in the workbook they all seem to work just fine for me. I have noticed that it runs quite a bit slower when I do it at home thought. Excel 2002 at work and 2007 at home. May be a compatibility mode issue. Weight checks are done in sets of 5 every hour and each set lives and dies together. Unfortunatly the cells do have to be merged. One of the conditions for the project was that I had to keep the format of our paper version.
It was actually what I used for the starting point.
I have to say that when I started the project I didn't know anything about macros and have been studying lots of forums and using a visual basic ref guide to learn about them. I've been working on this for about a month now and have learned alot but still have a ways to go. I have to admit that I actually enjoy learning about excel macros, however sometimes it can be a little frustrating. No one at work seems to have a clue about macros. Believe me I asked. What other code can be removed? I already did quite a bit of trimming as alot of the code was recorded to start. Oh and the before close thing in the model is actually not needed. It was origenally part of the workbook code but I didn't want to delete it so I put it in a model for later use.
Also I wasn't aware that the exit subs could be removed. All of the 40 weight failure macros have it and they function fine so I do not understand why another one at the end would not work. If the Exit sub was causing the problem wouldn't most of the other codes also not work?
It seems that there really is to much code in the workbook.'I put all of the codes in the worksheet_Change in code for worksheet and they all seemed to work. I ended up going with this code for now.
I'm still not really sure why its not working in the workbook as I removed all the exit subs and did not get the procedure to large worning but it still would not work when I copied and pasted it there. One thing i've learned there are alot of different ways to write code to get the same end result. And alot of it seems to be personal prefrence. Thanks to everyone for there assistance. You have all been very helpful.Please Login or Register to view this content.
Last edited by scottwhittaker2333; 06-12-2010 at 06:29 PM.
Another way:
Please Login or Register to view this content.
Thanks shg but when I ran the code I got a type mismatch error on this part
Please Login or Register to view this content.
Only if one of the values is not numeric ...
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
,
Bookmarks