VBA code to automatically clear a cells contents when another cell is empty

**masso1980** · 07-25-2012, 10:02 AM

hi guys,

i am currently trying to protect our racing teams spreadsheet. It contains a drop down list of 18 race tracks on one sheet, which has the 18 tracks listed on another worksheet.
So far what ive been able to do is once that race has completed it gets removed from the list using the IF function. Next to each track name it has the date of the race so if todays date is greater than this the cell will show "" therefore nothing will show up.
However the only trouble with that is if i were to change my system date back the track reappears in this list.
I would like the following, but please note that the track list i want to clear has to be protected, that way users of the team cant just enter the tracks back in.

here is the code i have in the sheet for now.

Please Login or Register  to view this content.

this code actually works to some extent but only when the sheet is unprotected and i click any of the other cells on that same sheet. This sheet will be protected and hidden. I have tried tried changing my system date forward, then back again but it didnt clear the contents of the track list until i went to the sheet and it was unlocked.

cells A8:A25 is where the IF function is located. B8:B25 is the date those races scheduled. C8:C25 is the list of tracks i want to clear once the race is over.

Please note i dont want to clear the range of cells C8:C25 all at the same time, only clear cell C8 when A8 is blank etc

Any help on this is appreciated.

Thanks,

Andrew

**GaidenFocus** · 07-25-2012, 10:08 AM

Not sure what you are trying to accomplish but instead of 100 if statements use this

Please Login or Register  to view this content.

**masso1980** · 07-25-2012, 10:42 AM

hi GaidenFocus

thank you very much for your reply,

that actually cleared what i needed once i changed it to the following

Please Login or Register  to view this content.

however it only cleared cells C8:C25 if i already had the sheet unlocked. but when its locked it wont seem to unlock the sheet for me. Also it still only deletes contents of c8:C25 once i become active on the same sheet like select another cell or something with my mouse. Is there a way to code it so it does cell clearing of c8 to c25 with the sheet locked and hidden.

thanks, and sorry my first post wasnt very clear

Andrew

**GaidenFocus** · 07-25-2012, 10:48 AM

Try this:

Please Login or Register  to view this content.

Obviously change "TargetSheet" to the name of your sheet. let me know if this works!

**masso1980** · 07-25-2012, 11:54 AM

no im afraid it still does the same thing, it clears contents in column C when column A is blank in the same row (which is what I want).
But it only does this once I click unprotect sheet (now clicking unprotect sheet i dont require a password), I then click any cell on the same sheet this then clears it, but it does nothing unless I do this.
What i would really like to do is clear this without clicking unprotect sheet and selecting any cell in the sheet. Also would prefer it to password protect the sheet when it locks the sheet again so only i can unlock the sheet.

regards,

Andrew

**GaidenFocus** · 07-25-2012, 12:16 PM

Sounds like you are putting the code in the wrong sheet. Right click the sheet tab you want this to work on, and view code. Put it in that one. It will target the hidden sheeet and modify the data even though you are on a different sheet.

Please Login or Register  to view this content.

---------- Post added at 11:16 AM ---------- Previous post was at 11:05 AM ----------

If the above does not solve it try

Please Login or Register  to view this content.

**masso1980** · 07-25-2012, 01:48 PM

hi again,

the above did not solve this although it did give the sheet a password afterwards. i then tried the second code you gave me and now it comes up with compile error: sub or function not defined then highlights worksheet in blue after set tmpbox = worksheet on line 4 of your code.
I did have your codes always posted on the correct sheet and i did change "targetsheet" to the correct sheet name also.

**GaidenFocus** · 07-25-2012, 02:20 PM

sorry worksheets not worksheet. i tested it and it is working. let me know!

Please Login or Register  to view this content.

**masso1980** · 07-25-2012, 02:45 PM

it doesnt work for me, its not working until I unprotect the sheet by mouse enter the password then click on any other cell. then it works for me fine, it also locks the sheet again with the password.

**GaidenFocus** · 07-25-2012, 03:40 PM

Ok yes this macro will only activate on cell selection change. how did you want it to activate?

**masso1980** · 07-25-2012, 06:17 PM

ok thank you for the hint there, thought i was doing something wrong to be honest. So what ive done is copied and pasted that same code to the workbook open and workbook before close, so now it clears those cells when i open and close excel. This will be good enough for me now i reckon, so thank you so much for your time on this.

Regards,

Andrew

**Cutter** · 07-25-2012, 10:52 PM

@ masso1980

Welcome to the forum.

Please notice that code tags have been added to your post(s). The forum rules require them so please keep that in mind and add them yourself whenever showing code in any of your future posts. To see instructions for applying them, click on the Forum Rules button at top of the page and read Rule #3.
Thanks.

Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as SOLVED. I'll do that for you now but please keep in mind for your future threads that Rule #9 requires you to do that yourself. If your problem has not been solved you can use Thread Tools (located above your first post) and choose "Mark this thread as unsolved".
Thanks.

Also, as a new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

VBA code to automatically clear a cells contents when another cell is empty

LinkBack

Thread Tools

Rate This Thread

Display

VBA code to automatically clear a cells contents when another cell is empty

Re: VBA code to automatically clear a cells contents when another cell is empty

Re: VBA code to automatically clear a cells contents when another cell is empty

Re: VBA code to automatically clear a cells contents when another cell is empty

Re: VBA code to automatically clear a cells contents when another cell is empty

Re: VBA code to automatically clear a cells contents when another cell is empty

Re: VBA code to automatically clear a cells contents when another cell is empty

Re: VBA code to automatically clear a cells contents when another cell is empty

Re: VBA code to automatically clear a cells contents when another cell is empty

Re: VBA code to automatically clear a cells contents when another cell is empty

Re: VBA code to automatically clear a cells contents when another cell is empty

Re: VBA code to automatically clear a cells contents when another cell is empty

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions