CODEWITHSUNDEEP
vbaexcel
mithun nair
mithun nair

Reputation: 136

VBA: Disable button in Excel after first click

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.

Upvotes: 2

Views: 2730

Answers (3)

OGP99
OGP99

Reputation: 169

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.

Upvotes: 0

S Meaden
S Meaden

Reputation: 8260

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

Upvotes: 4

MarcinSzaleniec
MarcinSzaleniec

Reputation: 2256

after Dim line just write:

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

that should be enough

Upvotes: 0

Related Questions