LinkBack URL
About LinkBacks
Dear all
Is it possible to have a piece of code that check that no cells are filled yellow before saving a workbook?
If any of the the cells are yellow then the code will not allow the workbook to be saved?
Thanks in advance
Jim
Dear all
Is it possible to have a piece of code that check that no cells are filled yellow before saving a workbook?
If any of the the cells are yellow then the code will not allow the workbook to be saved?
Thanks in advance
Jim
Last edited by Jim28; 06-21-2011 at 05:11 PM.
Hi,
You can try this, you will need to change the range to your range
Please Login or Register to view this content.
Thank You, Mike
Some Helpful Hints:
1. New members please read & follow the Forum Rules
2. Use Code Tags...Place[code]Before the first line and[/code] After the last line.
3. If you are pleased with a solution mark your post SOLVED.
4. Thank those who have help you by clicking the scales at the top right of the post.
Here...
Hi Jim,
Yes, it is certainly possible using the Workbook_BeforeSave or BeforeClose event, however, it may be simpler to check for a logical reason why those cells are yellow, rather than the color itself as the color may be due to a manual fill color or one of multiple conditional formatting rules.
If you know that 10 specific cells are supposed to contain data prior to saving, and you have formatting applied so that they are yellow if left blank, just check if they're blank rather than checking if they're yellow.
Hopefully that makes sense!
You read my mind Paul - yes I have set up some conditional formatting so that cells that should contain data that are empty turn yellow. I see you point about the danger of users simply manually filling the cells yellow - it it possible to avoid this?Originally Posted by Paul
Re the code provided kindly by real nice guy does this go in the view code section of one of the particular sheets?
Thanks again.
With acknowledgements to rng5000 and Paul
another idea?
Please Login or Register to view this content.
Hi that code goes in Thisworkbook under the view code of the workbook.
.
Real Nice Guy - code does not work. I have placed it where you suggested - any ideas? I have put a cell in the A column in yellow.
Barry your code does not work either. I put a MSgbox in to test. What am I doing wrong?
Last edited by Jim28; 06-21-2011 at 05:01 PM.
Jim, this is the issue you run into when trying to check for a specific color. The colorindex value of 6 is a specific shade of yellow from the standard colors toolbar. In Excel 2007 and newer, when you shade a cell "yellow" it may not be that specific colorindex value. Typically it's a shade of yellow and has different paramenters. This is why it's probably going to be easier to just check for blanks in those cells rather than mess around with colors.
Real Nice Guy got your code working thanks I had it in the wrong place - Barry still no luck with yours...
Yes so did I Jim
For what its worth I've attached my test sheet for you to test
1. I'm using 2007 like you
2. I'm using the Home menu tab; Font group; Fill color picker; Standard Colors and select Yellow swatch
3. Its just a bog standard procedure that can go in a standard code module and run it 'manually'.
It does bear out the advice given by Paul though. If you need to make your project portable - its good advice?
Barry does not work for me your test workbook. I am in fact running Excel 2010 (I know I will update my profile) shoudl this make a difference?
Hi Jim
Don't know about the nuances of different versions of Excel myself.
But its likely it will make a difference.
The Fill color picker represents the colors of a Theme.
I had the Theme called "Office" selected at the time.
That said I did select from Standard Colors which you would think are ..er..standard!
I have attached a nice little routine provided by Marcol in an earlier post which you may like to play around with.
Good luck mate - it can be very frustrating at times!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks