Making cells and buttons hide based on another cell's contents

[andrewb90]

hello all,

I am using this code currently to hide a button as well as a few lines, but I am finding a new issue with it, and I could use some help.

1: Neither of my rows seems to be hiding
2: since cell M3 is filled in automatically, the button and rows I want to hide don't hide automatically until I click on the formula in the cell. This makes having M3 hidden and protected very difficult because they user wouldn't be touching that cell.

Is there a way to make all of this happen automatically perhaps as soon as the excel document is open?
Also the cell the M3 fills in from is only used once at the very beginning of a new month and then new used on the worksheet again, so I can't expect the user to click on that cell every time either.

Thanks,

Andrew

 

[andrewb90]

Here's the code that I am using:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Cell As Range
If Not Intersect(Target, Range("M3")) Is Nothing Then
Range("809:809,816:816").EntireRow.Hidden = Range("M3") <> 5
End If
If Not Intersect(Target, Range("M3")) Is Nothing Then
Me.Shapes("Shape51").Visible = Range("M3") >= 5
End If
End Sub

 

[tweedle]

So you're basically saying in the code:
If the cell being changed is M3 then
Hide rows 809 and 813 if M3 <> 5
So for the Rows to hide; something has to touch M3 and M3 has to be something other than 5.
Is that the intent?

If M3 is a formula, it would not be the Target passed to Worksheet_Change.
One of the M3.Precidents being changed would be passed as the Target.

 

[andrewb90]

basically, if M3 does not equal 5 then rows 809:813 need to be hidden.
It seems that something has to touch M3 but I need M3 to be protected and hidden so, therefore the user will not be able to touch that cell.
Rows 80:813 need to be automatically hidden when either the source to M3 is changed or when the worksheet is opened.
Does this make sense?

 

[tweedle]

Try moving it to Calculate...
[I'm not sure I have your Shape options sorted out ]

Private Sub Worksheet_Calculate()
    
    If Me.Range("M3") <> 5 Then
        Me.Range("809:809,816:816").EntireRow.Hidden = True
        'Me.Shapes("Shape51").Visible = False
    Else
        Me.Range("809:809,816:816").EntireRow.Hidden = False
        'Me.Shapes("Shape51").Visible = True
    End If
End Sub