View Full Version : [SOLVED:] Close a command button after it has been clicked
pcarmour
09-23-2013, 12:35 PM
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.
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
09-23-2013, 01:55 PM
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.
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
09-23-2013, 11:26 PM
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
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
09-24-2013, 09:49 AM
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:
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.