How do I hide 5 command buttons

[Karoo News]

Is there an easy way to hide / unhide 5 command buttons I have on one
worksheet named 'New Style 2006'

Rgds
Neil

 

[Leith Ross]

Hello Neil,

Since you didn't say which type of Command Buttons you have Forms or
Control Toolbox type I am including macro examples for both.
________________________________

Example 1 - Hide/Unhide Forms Type:
Sub HideCommandButtons()
With Worksheets("New Style 2006")
..Shapes("Button 1").Visible = False
..Shapes("Button 2").Visible = False
..Shapes("Button 3").Visible = False
..Shapes("Button 4").Visible = False
..Shapes("Button 5").Visible = False
End With
End Sub

Sub UnHideCommandButtons()
With Worksheets("New Style 2006")
..Shapes("Button 1").Visible = True
..Shapes("Button 2").Visible = True
..Shapes("Button 3").Visible = True
..Shapes("Button 4").Visible = True
..Shapes("Button 5").Visible = True
End With
End Sub


Example 2 - Hide/Unhide Controls Toolbox Type:
Sub HideCommandButtons()
Dim Button As Object
With Worksheets("New Style 2006")
Set Button = .OLEObjects("CommandButton1")
Button.Visible = False
Set Button = .OLEObjects("CommandButton2")
Button.Visible = False
Set Button = .OLEObjects("CommandButton3")
Button.Visible = False
Set Button = .OLEObjects("CommandButton4")
Button.Visible = False
Set Button = .OLEObjects("CommandButton5")
Button.Visible = False
End With
End Sub

Sub UnHideCommandButtons()
Dim Button As Object
With Worksheets("New Style 2006")
Set Button = .OLEObjects("CommandButton1")
Button.Visible = True
Set Button = .OLEObjects("CommandButton2")
Button.Visible = True
Set Button = .OLEObjects("CommandButton3")
Button.Visible = True
Set Button = .OLEObjects("CommandButton4")
Button.Visible = True
Set Button = .OLEObjects("CommandButton5")
Button.Visible = True
End With
End Sub
________________________________

Remember to change the command button names to match the names on your
worksheet.

Sincerely,
Leith Ross

 

[Gary Keramidas]

you could use something like this

Option Explicit
Dim i As Integer
Sub HideButtons()
Dim shp As String
With Worksheets("New Style 2006")
For i = 1 To 5
shp = "button " & i
Shapes(shp).Visible = False
Next
End With
End Sub

 

[Tom Ogilvy]

Sub ABC()
Dim Obj As OLEObject
For Each Obj In ActiveSheet.OLEObjects
If TypeOf Obj.Object Is MSForms.CommandButton Then
Obj.Visible = Not Obj.Visible
End If
Next
End Sub

 

[Tom Ogilvy]

Here is the code with the New Style 2006 sheet specified.

Sub ABC()
Dim Obj As OLEObject
For Each Obj In Worksheets( _
"New Style 2006").OLEObjects
If TypeOf Obj.Object Is MSForms.CommandButton Then
Obj.Visible = Not Obj.Visible
End If
Next
End Sub

 

[Karoo News]

Thanks Tom that works perfect

PS thanks to Leith & Gary too