VBA: Disable button in Excel after first click

Question

I am trying to disable a button placed on my sheet1 once the user click on it. I went through few old Stackoverflow answers but dosent work as my expectation.

The code:

Sub Button2_Click()

    Call FindADate

    Dim myshape As Shape: Set myshape = ThisWorkbook.Worksheets("Sheet1").Shapes("Button 2")
    With myshape
        .ControlFormat.Enabled = False           '---> Disable the button
        .TextFrame.Characters.Font.ColorIndex = 15 '---> Grey out button label
    End With
End Sub

It actually grayed out the button which give a feel that the button is disabled but User can click again and again and it run my code.

Kindly let me know a solution to get the button disabled after 1 click, the button will be active again only once I close and re open the excel. (I am using MS Office professional Plus 2013)

Thanks in advance.

why not keep a variable to remember that it has been clicked? — S Meaden
– S Meaden, Commented Dec 5, 2017 at 23:26

S Meaden · Accepted Answer · 2021-04-16 12:25:26Z

4

You need to record somehow not to process the click. You could examine the button's text colour and if grey then ignore the click. Alternatively, you could set a variable either a global, module or static local variable. I chose the static local variable in the code below, called vDisable.

Option Explicit

Sub Button2_Click()
    Static vDisable As Variant
    If IsEmpty(vDisable) Then
        
        Call FindADate
    
        Dim myshape As Shape: Set myshape = ThisWorkbook.Worksheets("Sheet1").Shapes("Button 2")
        With myshape
            .ControlFormat.Enabled = False           '---> Disable the button
            .TextFrame.Characters.Font.ColorIndex = 15 '---> Grey out button label
        End With
        vDisable = True
    End If
End Sub

edited Apr 16, 2021 at 12:25

answered Dec 5, 2017 at 23:29

S Meaden

8,3203 gold badges39 silver badges74 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

MarcinSzaleniec · Accepted Answer · 2017-12-05 23:31:01Z

0

after Dim line just write:

if myshape.TextFrame.Characters.Font.ColorIndex = 15 then exit sub

that should be enough

answered Dec 5, 2017 at 23:31

MarcinSzaleniec

2,2661 gold badge12 silver badges23 bronze badges

Comments

OGP99 · Accepted Answer · 2022-06-16 14:43:20Z

0

I know this is an old question, but I had the same situation and solved it by the following:

With myshape
     .OnAction = ""           '---> Removes any macro from the button
     .TextFrame.Characters.Font.ColorIndex = 15 '---> Grey out button label
End With

This removes the need for a global/static variable.

answered Jun 16, 2022 at 14:43

OGP99

1699 bronze badges

Collectives™ on Stack Overflow

VBA: Disable button in Excel after first click

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related