Show / hide object in Excel

All objects located on the sheet are stored in the Shapes collection, whose elements have the Visible property, which controls the visibility of these objects. On handler [Перекл. 4348] [Перекл. 4348] hang the code

 ThisWorkBook.Worksheets("МойЛист").Shapes("Кнопка 4354").Visible = False ThisWorkBook.Worksheets("МойЛист").Shapes("Кнопка 4355").Visible = False ThisWorkBook.Worksheets("МойЛист").Shapes("Кнопка 4356").Visible = False 

and to the handler [Перекл. 4350] [Перекл. 4350] respectively

 ThisWorkBook.Worksheets("МойЛист").Shapes("Кнопка 4354").Visible = True ThisWorkBook.Worksheets("МойЛист").Shapes("Кнопка 4355").Visible = True ThisWorkBook.Worksheets("МойЛист").Shapes("Кнопка 4356").Visible = True 

Just keep in mind that in the macro you need to specify the names of the buttons, and not the text on them. The names can be found as follows:

 Sub one() Dim j As Shape With ThisWorkBook.Worksheets("МойЛист") For Each j In .Shapes MsgBox "Текст на элементе: " & j.AlternativeText & ", название элемента: " & j.Name Next j End With End Sub 

If there are no other objects on the sheet (and buttons, shapes, diagrams are all objects), you can without using switches. Pressing a pair of keys Ctrl + 6 hides / shows sheet objects.

'---------------

Option if there are other objects.

Place all the invisible objects on one common (let's call it BigObject ). Beautiful, covered in a nice color :)

You can show / hide objects using the ZOrder parameter, i.e. placing objects under / above BigObject :

to the background

 Sub ObjHidden() With ActiveSheet .Shapes("Кнопка 4354").ZOrder msoSendToBack .....' другие объекты End With End Sub 

bring to Front

 Sub ObjVisible() ' то же самое, но ZOrder msoBringToFront End Sub 

It is more correct to control not the visibility of objects on the BigObject , but the position of the BigObject itself relative to the parasites attached to it)

 Sub BigHidden() ActiveSheet.Shapes("BigObject").ZOrder msoSendToBack End Sub Sub BigVisible() ActiveSheet.Shapes("BigObject").ZOrder msoBringToFront End Sub 

And if you manage, then let him manage himself, there is nothing for him to push the buttons. Let's give him one cell to help and let visibility be controlled by a click on BigObject .

 Sub HiddenVisibleObj() With ActiveSheet.Shapes("BigObject") If Range("A1").Value = 1 Then .ZOrder msoBringToFront Range("A1").Value = 0 Else .ZOrder msoSendToBack Range("A1").Value = 1 End If End With End Sub 

If you think that a cell is being allocated, it will be bold, let it be by its own means, not small:

 Sub HiddenVisibleObj() With ActiveSheet.Shapes("BigObject") If .TextFrame2.TextRange.Characters.Text = "" Then .ZOrder msoBringToFront .TextFrame2.TextRange.Characters.Text = "Все на дно!" Else .ZOrder msoSendToBack .TextFrame2.TextRange.Characters.Text = "" End If End With End Sub