Close a command button after it has been clicked

[pcarmour]

Hi,
I have a program that copies a command button labeled 'Next' and its code to another worksheet called Summary . The user is instructed to follow some instructions on the Summary worksheet and then click the 'Next' button. After the user has clicked the button and the code runs I want to delete the button from the Summary worksheet. The problem is that there are other buttons on the Summary worksheet that I do NOT want to delete, and the copied button's name/number always changes. I hope that explains the situation clearly.

I am working with Windows Home Premium version 6.1.7601 SP 1 Build 7601and Excel version 14.0.6123.5001 (32 bit)

Thank you for any help.
Regards,
Peter.

[SamT]

Objects.Count is also the last Object Index number. So If you save the Objects.Count value before you add a new object, the Index of the new Object is SavedCount + 1.

HTH

[pcarmour]

Hi HTH,
Thank you for your prompt reply.
That looks exactly the way forward as I do want to delete the last Object. I have Googled save the Objects.Count value but no clear help. Can you please give some code?
Regards,
Peter.

[SamT]

Any code must depend on the Object Type of the Command button. Can you post the code that does the copying?

Hope This Helps.

SamT

[pcarmour]

Hi Sam T,

Thanks again and for explaining HTH.

Here is the code used to copy the next Button. I need the code attached to the button to delete it once clicked.



Sub New_Broker_2()

'Next button on Manager Sheet
'Delete hidden column in Summary and instruct to insert new Broker Column.

Sheets("Summary").Select

Columns("AO:BF").Select
Selection.EntireColumn.Hidden = False
Columns("BE:BD").Select
Selection.Delete Shift:=xlToLeft
Columns("AP:BE").Select
Selection.EntireColumn.Hidden = True

'Instruct to add Broker Details
Columns("I:BF").Select

'Get Next Button
Sheets("Manager").Select
ActiveSheet.Shapes.Range(Array("Button 4")).Select
Selection.Copy
Sheets("Summary").Select
Range("S1").Select
ActiveSheet.Paste




MsgBox "Insert a New Column for the Broker" & Chr(10) & _
"In the Same Alphabetical Position" & Chr(10) & _
"As Inserted in the Manager List" & Chr(10) & _
"Then Click Next", vbInformation, "Peter Says"


End Sub

[SamT]

Peter, This code is the same programatically as yours. I merely cleaned it up and moved the Shape copy line above the Summary sheet section.


Sub New_Broker_2()

'Next button on Manager Sheet
'Delete hidden column in Summary and instruct to insert new Broker Column.

'Get Next Button
Sheets("Manager").Shapes.Range(Array("Button 4")).Copy

With Sheets("Summary")
.Columns("AO:BF").Hidden = False
.Columns("BE:BD").Delete
.Columns("AP:BE").Hidden = True
.Range("S1").Select
.Paste
End With

'Instruct to add Broker Details

MsgBox "Insert a New Column for the Broker" & Chr(10) & _
"In the Same Alphabetical Position" & Chr(10) & _
"As Inserted in the Manager List" & Chr(10) & _
"Then Click Next", vbInformation, "Peter Says"

End Sub

I think these two subs might be what you are looking for

Sub New_Broker_2()

'Of all the shapes on the Worksheet, there's only one named "Button 4"
Sheets("Manager").Shapes("Button 4").Copy

With Sheets("Summary")
'By Definition, a Column is an Entire Column
.Columns("AO:BF").Hidden = False
'There is only one direction to shift when deleting a Column or Row
.Columns("BE:BD").Delete
.Columns("AP:BE").Hidden = True
.Range("S1").Select
.Paste
'The newly pasted Shape is the last one in the Shapes Collection
.Shapes(.Shapes.Count).Name = "DeleteThisButton"
'And we named it so the deleting Macro can know wich one to delete
.Shapes("DeleteThisButton").OnAction = "MyMacroName"
End With
'
'
'
End Sub

Sub MyMacroName()
'Do Stuff
'
'
'
Sheets("Summary").Shapes("DeleteThisButton").Delete
End Sub

I think that if you permanently put a button on the sheet and use its Visible property, it might be better for you.

Sub New_Broker_2()

With Sheets("Summary")
.Columns("AO:BF").Hidden = False
.Columns("BE:BD").Delete
.Columns("AP:BE").Hidden = True
.Shapes("ButtonNext").Visible = True
End With
'
'
'
End Sub

Sub ButtonNextClick()
'Do Stuff
'
'
'
Sheets("Summary").Shapes("ButtonNext").Visible = False
End Sub

[pcarmour]

Hi Sam T,

Brilliant - does exactly what I require, Thank you.
Thanks foe suggesting the permanent option which I may use elsewhere.

Thanks again for your help.:beerchug: