What method required?

[julhs]

I have a label/shape that I want to ultimately cover over cells, D16:E19

What I want to happen is;
Click on the label/shape and for it to Close
To then be able to input data into cells D16:E19, and as long as any cell in D16:E19 is greater than zero, the label will remain Closed
But then when/if ALL cells in D16:E19 are zero the label will Open back up

Attached is code from a Recorded Macro that I added to the labels ‘Click ()’ event sub.
Clearly this is not going to allow me to do what I want, but it may show what I am trying to do

Or would it be better to have a permanent Named Button/Toggle switch that will open or close the Label as and when I choose?

Private Sub Enter_Values_Label_1_Click()
' Code used is from the Recorded Macro
' Used Alt>F10 to open "Selection and Visibility" > "Shapes on Sheet",
' Then clicked option button for ("Enter_Values_Label_1"), to CLOSE it (msoFalse).
' Then made some random entries and then deleted them.
' Then went back and returned ("Enter_Values_Label_1"), to Open it (msoTrue)
ActiveSheet.Shapes.Range(Array("Enter_Values_Label_1")).Visible = msoFalse
Range("D16").Select
ActiveCell.FormulaR1C1 = "1"
Range("D18").Select
ActiveCell.FormulaR1C1 = "1"
Range("D17").Select
ActiveCell.FormulaR1C1 = "2"
Range("D16:D18").Select
Selection.ClearContents
ActiveSheet.Shapes.Range(Array("Enter_Values_Label_1")).Visible = msoTrue
End Sub

 

[Osvaldo Palmeiro]

Hi.
Please, see if the two codes below could help.

Private Sub Enter_Values_Label_1_Click()
 Me.Shapes("Enter_Values_Label_1").Visible = False
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
 If Intersect([D16:E19], Target) Is Nothing Then Exit Sub
 If Application.CountIf([D16:E19], 0) = 8 Then Me.Shapes("Enter_Values_Label_1").Visible = True
End Sub

 

[julhs]

Give me a while longer to test further.
But yes,

'Private Sub Enter_Values_Label_1_Click()
 'Me.Shapes("Enter_Values_Label_1").Visible = False
'End Sub

Works to hide shape " Enter_Values_Label_1".
BUT it remains permanently CLOSED
At moment can't get

'Private Sub Enter_Values_Label_1_Click()
'If Intersect([D16:E19], Target) Is Nothing Then Exit Sub
 'If Application.CountIf([D16:E19], 0) = 8 Then Me.Shapes("Enter_Values_Label_1").Visible = True
'End Sub

to function.
Keep getting a new click event sub when I click on "Enter_Values_Label_1". label"

What I have come up with SO far is;
Your solution,

'Private Sub Enter_Values_Label_1_Click()
 'Me.Shapes("Enter_Values_Label_1").Visible = False
'End Sub

to close it.
Plus a Command Button that can "Open or Close" it, that I can use to actually CLOSE it

Private Sub CommandButton1_Click()

If ActiveSheet.Shapes("Enter_Values_Label_1").Visible Then
        ActiveSheet.Shapes("Enter_Values_Label_1").Visible = False
    Else
        ActiveSheet.Shapes("Enter_Values_Label_1").Visible = True
    End If

 

[Osvaldo Palmeiro]

The second code I posted will unhide the shape as you manually fill the range D16:E19 with zeros, as you described below.

But then when/if ALL cells in D16:E19 are zero the label will Open back up

 

[julhs]

Hi Osvaldo

With both installed,

Private Sub Enter_Values_Label_1_Click()
Me.Shapes("Enter_Values_Label_1").Visible = False
End Sub

works to CLOSE the shape. But;

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect([D16:E19], Target) Is Nothing Then Exit Sub
If Application.CountIf([D16:E19], 0) = 8 Then Me.Shapes("Enter_Values_Label_1").Visible = True
End Sub

will not re-open the Label after; data is entered in D16:E19 and then subsequently cleared

 

[Osvaldo Palmeiro]

Hi.
Ok, if this time I followed what you meant then this will work.

Private Sub Worksheet_Change(ByVal Target As Range)
 Me.Shapes("Enter_Values_Label_1").Visible = Application.CountIf([D16:E19], 0) = 8
End Sub

 

[julhs]

Haven't yet tried what you just posted.

But have been playing with what you gave me previously.
Made a very subtle change to the “Change event” of CountIf([D16:e19], "") = 8 ;
to this,

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect([D16:d19], Target) Is Nothing Then Exit Sub
If Application.CountIf([D16:d19], "") = 4 Then Me.Shapes("Enter_Values_Label_1").Visible = True
End Sub

And now when I clear cell content, label DOES reappear

 

[Osvaldo Palmeiro]

Edit: correct from "= 8" to "<> 8" sign

Private Sub Worksheet_Change(ByVal Target As Range)
Me.Shapes("Enter_Values_Label_1").Visible = Application.CountIf([D16:E19], 0) <> 8
End Sub

 

[julhs]

Just tried your post #8. Label is opening back up after just one entry being made in D16:E19.

But having made that subtle change from

CountIf([D16:d19], 0) = 8
too
CountIf([D16:d19], "") = 8

everything works as intended

Thanks Osvaldo, you have got me to my goal.
Many thanks for your help