CODEWITHSUNDEEP
excelvba
obaram
obaram

Reputation: 461

Disable all buttons in an Excel sheet

I want to disable buttons after they are clicked in Excel 2013.

My code works fine but for just one specific button.

How can apply the same logic to all buttons in a Sheet?

ActiveSheet.Shapes("Button 1").OnAction = Empty
ActiveSheet.Shapes("Button 1").DrawingObject.Font.ColorIndex = 16

Upvotes: 0

Views: 2545

Answers (3)

RobH
RobH

Reputation: 1

This should hide all form control (including buttons) in the worksheet.

Dim ws_export As Worksheet 
Dim shp_hide As Shape

 For Each shp_hide In ws_export.Shapes
    If shp_hide.Type = msoFormControl Then shp_hide.Visible = FALSE

 Next shp_hide

Upvotes: 0

ThunderFrame
ThunderFrame

Reputation: 9461

It's not clear from your question whether you want pressing Button1 to "disable" all controls on the sheet, or whether you want each button to disable itself.

Button1 disables all controls

Sub Button1_Click
  Dim shp As Shape
  For Each shp In Sheet1.Shapes
    With shp
      If .Type = msoFormControl Then
        .OnAction = ""
        .DrawingObject.Font.ColorIndex = 16
      End If
    End With
  Next shp
End Sub

Each button disables itself Use a common button disabler helper procedure...

Sub Button1_Click()
  DisableButton Sheet1, "Button 1"
End Sub

Sub Button2_Click()
  DisableButton Sheet1, "Button 2"
End Sub

Sub DisableButton(hostSheet As Worksheet, shapeName As String)
  Dim shp As Shape
  On Error Resume Next
  Set shp = hostSheet.Shapes(shapeName)
  On Error GoTo 0
  If Not shp Is Nothing Then
    With shp
      If .Type = msoFormControl Then
        .OnAction = ""
        .DrawingObject.Font.ColorIndex = 16
      End If
    End With
  End If
End Sub

Upvotes: 2

Preston
Preston

Reputation: 8177

I would guess this is what you're looking for:

Sub Answer()
dim sh as shape
For Each Sh In ActiveSheet.Shapes
    Sh.OnAction = Empty
    Sh.DrawingObject.Font.ColorIndex = 16
Next
End Sub

Upvotes: 0

Related Questions