Good day all, once again,
I have a code attached to many "CheckBoxes" that when checked finds the relevant "NamedRange" & copies the data to "Page4+" however when the box is unchecked it copies the "NamedRange" a second time & I can't get the code to remove the data instead when unchecked? Any advice would be truly appreciated, thank you - Marco
Please Login or Register to view this content.
First you'll need to test the status of the CheckBox1. If it's TRUE then commit your current code actions.
But if it's FALSE, you'll need a set of logic to find that data and remove it. Nothing in your code nor question indicates how that data can be found and removed yet.
Please Login or Register to view this content.
Last edited by JBeaucaire; 10-28-2012 at 04:01 AM.
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use theicon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
?None of us is as good as all of us? - Ray Kroc
?Actually, I *am* a rocket scientist.? - JB (little ones count!)
Hi JBeaucaire,
Sorry but your Excel prowess far exceeds mine, I have attached my workbook. All my "NamedRanges" are stored on sheet "Test" & the sheet being populated & hopefully un-populated by the checkbox will be "Page4+". I tried your code above with no success sadly due to my ignorance really, i'am currently trying to work out a find & remove code again with little success so far - As always JBeaucaire any assistance would be greatly appreciated, thank you once again & have a great day - Marco
Hello matrixpom,
Maybe you just want something like this?
Please Login or Register to view this content.
Please consider:
Be polite. Thank those who have helped you. Then Click on the star icon in the lower left part of the contributor's post and add Reputation. Cleaning up when you're done. If you are satisfied with the help you have received, then Please do Mark your thread [SOLVED] .
Hi again Winon,
Tried the code & it a appears to colour column "G" but nothing more, then my page freezes, does your code work on 2007? I'm using Excel 2010, maybe this is causing the problem? Thanks again for your time Winon - Marco
Hi matrixpom,
It works fine with my Excel 2007. I have checked it several times without a problem. Do you have Conditional Formats applied to some Sheets?
Maybe it would be a good idea if you could upload a sample of your problem WorkBook, for us to have a look at.
Hi Winon,
Already attached above
Here is the CheckBox1 example filled out completely. I found that in the rows copied when you "check" the box is the unique cell with "DRY LAID BLOCKS". That section is 5 rows deep. With that information, we can set the macro to search for that cell on Page4+ (the FIND method) and delete those 5 rows when the box is unchecked. The final complete macro for Dry Laid Blocks would be:
Please Login or Register to view this content.
Hi again JBeaucaire,
Tried to simply copy & paste your code to the relevant checkbox before trying to understand how it works & sadly I can't get it to work on my worksheet, it does however stop copying the code when unchecked but does not remove it? I have attached my Workbook with your code updated to "Checkbox1" Hopefully you can locate where I am getting it wrong...once again thank you for your time JBeaucaire I can't tell you how much i appreciate this - Marco
Sorry JBeaucaire, I made some updates to the original worksheet I attached, disregard my last comments JBeaucaire, it works perfectly on the original!!!? Currently working out what changes I have made that caused your code to fail...
Hi again JBeaucaire,
Is there a way to have the "Named Range" removed rather than 5 rows, this because some of my procedures are only three or four rows & others could be 15? Thank you again for your time JBeaucaire, this is really amazing stuff for me & the results so far are fantastic!
No, the names range isn't getting copied, just the data IN the named range. Since each section is randomly sized, you will have to create a "version" for each unique section. If the section being searched for is 12 rows, you'll change the .Resize(5) to .Resize(12) for that one as well as edit the search string from "DRY LAID BLOCKS" to "WHATEVER". You will have to create a unique version for each one.
I understand,
Once all my procedures are complete there should only be a few rows difference from one to the other, possibly best if i minimise the extra rows to as small as possible, either way JBeaucaire the result of your code is fantastic, your a legend in my book! Can't thank you enough - Marco
Sorry about this Jerry - Just hit another issue, when I run the code from the start page it freezes excel if I check & uncheck a box...any ideas as to why? - Thanks
Last edited by matrixpom; 10-28-2012 at 10:53 PM.
Hello matrixpom,
Some of the Sheets you are referencing with your Code are Hidden and Protected. To get the Code to execute, add UnProtect the specific file at the beginning of each relevant Code, and the Protect the sheet again at the end op that specific Code. You don't need to make the Hidden Sheet visible.
That should prevent Excel from "Freezing"
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks