VBA doesn't work

Question

Hi,

I have a sheet partially locked so only some cells can be changed by users.

I want set amount of rows to be hidden when Cell M10 has a value of 0, and be shown when when it has value of more than 0..

Can you see anything wrong with this VBA code? I can't make it work (however, I can't make a simpler version work anymore, so the issue might be simpler, or something else entirely). I used AI to write this code, and it worked. Then i swapped it out and it didn't. Going back to version 1 didn't work anymore.. i'm frustrated.

Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$M$10" Then
If Target.Value > 0 Then
If ActiveSheet.ProtectContents Then
ActiveSheet.Unprotect Password:="TL1234"
Rows("18:20").EntireRow.Hidden = False
ActiveSheet.Protect Password:="TL1234"
End If
Else
If ActiveSheet.ProtectContents Then
ActiveSheet.Unprotect Password:="TL1234"
Rows("18:20").EntireRow.Hidden = True
ActiveSheet.Protect Password:="TL1234"
End If
End If
End If
End Sub

Answer 1

Duplicate thread, follow up here:

https://answers.microsoft.com/en-us/msoffice/forum/all/help-to-hide-rows/b5d55e9c-e3f9-45fa-8f88-1508278a28ae

Please do not ask the same question twice, it is not fair if 2 people are working on the same solution at the same time, it is a waste of time for everyone. This behavior violates the forum rules:
Microsoft Community - Code of Conduct

I close your thread now.

Thank you for your understanding.

Andreas.

Answer 2

Does M10 contain a formula?

Answer 3

Truly appreciate you taking your time to help.

I've done what you've suggested, but when i change between 0 and anything above 0, nothing happens... Any ideas?

Answer 4

1. Activate the Visual Basic Editor.

Press Ctrl+G to activate the Immediate window.

Type (or copy/paste) the following line, then press Enter:

Application.EnableEvents = True

2. Change the code to Sub Worksheet_Change(ByVal Target As Range) Dim rng As Range On Error GoTo ExitHere: Set rng = Range("M10") If Not Intersect(rng, Target) Is Nothing Then Application.ScreenUpdating = False Application.EnableEvents = False Me.Unprotect Password:="TL1234" Range("A18:A20").EntireRow.Hidden = (rng.Value <= 0) ExitHere: Me.Protect Password:="TL1234" Application.EnableEvents = True Application.ScreenUpdating = True End If End Sub