VBA code works on all sheets except for one, why?

[DigitalDingers]

Hello all -

I am using the below code (not as a module) but at the sheet level. I have it running just fine on 4 of the 5 sheets I need it on. However, for some reason it does not want to run on one particular sheet. Do I have a setting off somewhere? To make things more odd, when I click on the A3 cell and place my cursor next to the formula in the formula space at top and press ENTER, the code runs, but not before then. Like my other sheets (which run this code perfectly), I want it to run automatically once cell A3 = 2.

Can you help?

thx



Private Sub Worksheet_Change(ByVal Target As Range)


If [A3] = 2 Then
Sheets("debt").Visible = True
Sheets("debt").OptionButtons = False
Sheets("debt").Range("B10") = ""
Sheets("debt").Range("E10") = ""
Sheets("debt").Range("H10") = ""
Sheets("debt").Range("K10") = ""
Sheets("debt").Range("N10") = ""
Sheets("debt").Range("B20") = ""
Sheets("debt").Range("I20") = ""
Else
Sheets("debt").Visible = False
End If
End Sub

 

[Michael M]

a couple of questions....
1. Does A3 only contain a single digt? To make sure you don't have any other characters in A3 type =LEN(A3) in another cell and make sure it returns 1
2. Is the sheet that won't work proyeced / locked in any way ??
3. Is the sheet that doesn't work VeryHidden and not just Hidden ?

 

[footoo]

Presumably A3 on the sheet in question contains a formula, in which case need Worksheet_Calculate procedure.

 

[DigitalDingers]

Michael -

1) LEN did return 1
2) How do I know if it is locked or not?
3) No, the sheet is NOT veryhidden

Any other thoughts?

Footoo -

How would you rewrite the code using Worksheet_Calculate to make this work? And, yes - A3 does have a formula in it.

Thx all!

 

[Michael M]

you could change it to a Worksheet_change event

https://docs.microsoft.com/en-us/office/vba/api/excel.worksheet.change

 

[footoo]

How would you rewrite the code using Worksheet_Calculate

Private Sub Worksheet_Calculate()
With Sheets("debt")
    If [A3] = 2 Then
        .Visible = True
        .OptionButtons = False
        .Range("B10,E10,H10,K10,N10,B20,I20") = ""
    Else
        .Visible = False
    End If
End With
End Sub

 

[DigitalDingers]

footoo -

Your code worked perfectly, THANK YOU!!!!!!!

Thanks to both of you for your help.

Have a wonderful day