Worksheet Button

[HSG]

In Excel 2007 I have created a button at design time on a worksheet that appears when the worksheet is clicked and when in turn, the button is clicked it runs a macro. Once the macro starts I no longer need the button. I would like to eliminate it so that when the user clicks the worksheet once the macro has begun (it is a VBA application) the button won't appear again when the worksheet is clicked subsequently. I have not been able to find the button's properties in the Property Window. All I know is that application.caller returns Button 1. I don't know how to refer to the button in VBA. The worksheet also contains a userform which is a combobox that works properly.

[Joseph]

Can this macro run without a button? Meaning, does the macro need to run each time the workbook is opened? Or more so as needed? You may not need the button is why I ask.

[HSG]

Yes, it can and I know how to do that. However, I would also like to know how to control a button on a worksheet as I have proposed.

[Joseph]

This is one way you can control a button on a worksheet.

Code: Select all

ActiveSheet.Shapes("Button 1").Select
Selection.Delete

However I would wait for a more direct response.

[HansV]

Welcome to Eileen's Lounge!

To delete the button, use the line

ActiveSheet.Shapes(Application.Caller).Delete

Let's say the macro called by the command button is named MyMacro. It could look like this:

Code: Select all

Sub MyMacro()
  ' Delete the command button
  ActiveSheet.Shapes(Application.Caller).Delete
  ' Your code goes here
  ...
  ...
End Sub

[HSG]

Unfortunately, although it gets rid of the button, it also eliminates it completely so that the next time you open the worksheet - no button.

[HSG]

I'm sorry. I need to clarify. The next time you open the worksheet the button is gone. That's fine. The problem is that if you save the workbook the next time you run the application, the button is gone, too.

[HansV]

Well, you did ask for the button to be "eliminated" because you no longer needed it.

Change the macro as follows:

Code: Select all

Sub MyMacro()
  ' Hide the command button
  ActiveSheet.Shapes(Application.Caller).Visible = False
  ' Your code goes here
  ...
  ...
End Sub

Double-click ThisWorkbook in the project explorer in the Visual Basic Editor and create the following event procedure:

Code: Select all

Private Sub Workbook_Open()
  ' Unhide the button
  Worksheets("Sheet1").Shapes("Button 1").Visible = True
End Sub

Modify the sheet name Sheet1 and the button name Button 1 as needed.

[Joseph]

Hans beat me to it. With a better, answer of course.

[HSG]

Thank you both. One more question: Is there any way to modify the appearance of the Excel (not ActiveX) button other than the text? It doesn't seem to appear in the properties window.

[HansV]

No, you can only format the caption (text); the button itself is always a plain button using the "buttonface" color set in Windows.

Instead of a button from the Form Controls, you can use any shape. Excel 2007 has a whole array of special effects for shapes, and you can assign a macro to a shape just like you can for command buttons.

[HSG]

Again, thanks. I learned a lot from this exchange. I had tried reading the Excel Help file regarding "Application.Caller" and found it to be most unhelpful.

Regards
Hans G.

[HansV]

You're welcome!

[HSG]

I thought I had it all worked out. But....
Suddenly the button has disappeared.
Activesheet.shapes.count returns 1 and Activesheet.shapes(1).name returns "Button 6".
Activesheet.shapes(1).visible.=true either when running the Sub or in the Immediate window doesn't make the button appear.
In my Button_Click subroutine the following statement appears:
Application.OnKey "{F12}", "show_button"
And
Sub show_button()
activesheet.Shapes(1).Visible = True
End Sub

If the button is clicked it calls Sub Button1_Click which is in a separate module and which in turn, makes the button disappear with activesheet.shapes(1).visible = False and then calls the first Subroutine in my code

Even after running sub show_button() or running the "Show" statement in the Immediate window, Pressing F12 does nothing.

It all used to work but in the course of debugging my VBA code, the button has disappeared from Sheet 1 which is always the activesheet.

I also don't understand why the Button numbers in its name keep incrementing when there is never any more than one button. I would like to retain the "Button 1" name

Activesheet.names.count returns 0. So does application.names.count

Again, any help will be greatly appreciated.

[HansV]

If the name varies, I suspect that you still have code that deletes the command button, then creates a new one later on.

If you have a shape that you can't find, it might be in an unexpected position.

[HSG]

Hans:

Again thanks for your interest. I have to confess that your idea occured to me and so I conducted an exhaustive search of the Worksheet. Furthermore, if I hade deleted it, why would activesheet.Shapes.Count return 1? There are no other shapes to be seen anywhere. It's very mysterious. Especially since it has been working fine and I've not added any code referring to Shapes. Using the CTL + F method and searching for Shapes in the whole project the only Property I've found is Visible which is either True or False and there are no Methods. The button was created manually from the sheet itself. Can it be deactivated but not deleted somehow by some manual interaction with the Sheet?

[HansV]

Try this:
Activate the Developer tab of the ribbon.
Turn on Design Mode.
Press Ctrl+G or F5.
Click Special...
Select Objects, then click OK.
Even if you don't see any objects, if any were selected you should be able to delete them now (press the Delete key).