Disable shape as button

[noelmus]

Hi,
I would like to disable a shape as button to run macro after first click.
Is this possible?

Thanks

 

[Yongle]

Try this - the shape is disabled after first click

Public DisableShape As Boolean   'at top of module

Sub MyShapeName_Click()
    If DisableShape Then
        MsgBox "shape is disabled"
        Exit Sub
    Else
        MsgBox "Macro goes here"
        DisableShape = True
    End If
End Sub

and if reset required

Sub ReEnableShape()
    DisableShape = False
End Sub

 

[noelmus]

Thanks for your reply.
1. My button is on another sheet and the macro runs on another.
2. MsgBox "Macro goes here". Do I need to put the code here?

Thanks

 

[Yongle]

Place this at the top of any standard module (NOT a sheet module) - must be placed above any procedures

Public DisableShape As Boolean   'at top of module

Insert this line in your macro

DisableShape = True

Make this the FIRST line In the macro that is assigned to your shape

If DisableShape Then Exit Sub

 

[noelmus]

Hi,
I can't manage, If you can will you please help me. Code Below in Regular module.

Sub Copy()

Workbooks("2").Sheets("Chit1&2").Range("E20:H21").Value = "=" & "'C:\Users\littl\Desktop\[1.xlsm]Chit1&2'!E20:H21"
Workbooks("2").Sheets("Chit3&4").Range("E20:H21").Value = "=" & "'C:\Users\littl\Desktop\[1.xlsm]Chit3&4'!E20:H21"
Workbooks("2").Sheets("Chit5&6").Range("E20:H21").Value = "=" & "'C:\Users\littl\Desktop\[1.xlsm]Chit5&6'!E20:H21"
Workbooks("2").Sheets("Chit1&2").Cells(8, "J").Value = "=" & "'C:\Users\littl\Desktop\[1.xlsm]Chit1&2'!J8"
Workbooks("2").Sheets("Chit3&4").Cells(8, "J").Value = "=" & "'C:\Users\littl\Desktop\[1.xlsm]Chit3&4'!J8"
Workbooks("2").Sheets("Chit5&6").Cells(8, "J").Value = "=" & "'C:\Users\littl\Desktop\[1.xlsm]Chit5&6'!J8"


End Sub

Thanks

 

[mikerickson]

If a Public variable is used, it will loose its value when Excel closes, even if the file is saved before closing.

One way to preserve the Enabled status of a shape would be to use a hidden property of the shape as an indicator.
For example, if one has a shape where the Reflection is set to msoReflectionTypeNone, one could use the .Reflection.Size to indicate if its macro is enabled (0-disabled, otherwise-enabled)
Since a newly created shape has a .Reflection.Size of 100, they are created enabled.

Routines (or code within another routine) like this could control the Enabled status of a shape

Function IsEnabled(aShape As Object) As Boolean
    IsEnabled = CBool(aShape.Reflection.Size)
End Function

Sub SetShapeEnable(aShape As Object, EnableVal As Boolean)
    aShape.Reflection.Size = Abs(CLng(EnableVal))
End Sub

And (if the shape is assigned to Macro1) used like

Sub Macro1()
    If Not IsEnabled(ActiveSheet.Shapes(Application.Caller)) Then Exit Sub
    
    MsgBox "your code here"

    SetShapeEnable ActiveSheet.Shapes(Application.Caller), False
End Sub

 

[Yongle]

Nice catch @mikerickson

Public variables are set to default when a workbook is reopened

1. What is it that you want to happen ? Do you want the shape disabled ...
- until the workbook is re-opened
OR
- until something happens (what should the trigger be?)

2. For clarification - I am not sure if you are dealing with 2 macros or one macro ...
- is the macro that disables the shape the same macro as is attributed to the shape?

3. What is the name of the sheet containing your shape?