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

Try this:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim tmpbox As Range
application.screenupdating = false
Set tmpbox = Sheets("TargetSheet").Range("A8")
sheets("TargetSheet").Unprotect

Do Until tmpbox.Row = 26
If tmpbox.Value = "" Then
tmpbox.Offset(0, 2).Value = ""
End If
Set tmpbox = tmpbox.Offset(1, 0)
Loop
Sheets("TargetSheet").Protect

End Sub

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

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

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.

Sheets("TargetSheet").protect Password:="password"

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

If the above does not solve it try

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim tmpbox As Range
application.screenupdating = false
Set tmpbox = worksheet("TargetSheet").Range("A8")
worksheet("TargetSheet").Unprotect

Do Until tmpbox.Row = 26
If tmpbox.Value = "" Then
tmpbox.Offset(0, 2).Value = ""
End If
Set tmpbox = tmpbox.Offset(1, 0)
Loop
worksheet("TargetSheet").Protect password:="password"

End Sub

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.

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

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim tmpbox As Range
application.screenupdating = false
Set tmpbox = worksheets("TargetSheet").Range("A8")
worksheets("TargetSheet").Unprotect

Do Until tmpbox.Row = 26
If tmpbox.Value = "" Then
tmpbox.Offset(0, 2).Value = ""
End If
Set tmpbox = tmpbox.Offset(1, 0)
Loop
worksheets("TargetSheet").Protect password:="password"

End Sub

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.

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

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

@ 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.