Finding Button Name

[Jimmy P]

I have part of a sub that captures a Button click, and then runs a processes. I want to disable the Button if the count in a cell = 0 (see red line in code) and enable it again when the sub is evoked again.

Here's what I have:

b???Enabled = True 'This is here to enable the Button when the sub is entered again after Cnt1 <> 0 and a new run starts.
Dim b1 As Button
Set b1 = ActiveSheet.Buttons("???")
If Cnt1 = 0 Then
MsgBox "There are no more Samples to Process" _
& vbCr & vbCr & "Click OK to make another selection or Quit the program", _
vbOKOnly + vbInformation, "Sample Run Over"
b???.Enabled = False
End If

The ??? is there because I can't find the Button properties name to use it (The Button text is "Load Sample & Start Processing)". I've tried turning on Design Mode through the Developer tab and then right clicked on the Button and the worksheet properties window opens in the VBA code window with no Buton properties shown. And there is no Properties selection for the Button showing when I right click on the Button.

Where are the Button properties located and will the code work if I do find the Button name and use it in place of ???.

 

[ranman256]

If you used form buttons, then you must cycle the shapes to find them.

Dim shp As Shape
For Each shp In ActiveSheet.Shapes
  MsgBox shp.Name
Next

but if you used activeX buttons, then yes, it is in the controls.

For Each ctl In me.controls  
    MsgBox ctl.Name
Next

 

[BiocideJ]

instructions should be valid for Excel 2007 and later.

On the Developer tab make sure the Design Mode button is selected.
Click on the button in question and see what the text is in the "Name Box". This is the text area to the left of the formula bar and immediately above the column A header.
You can also change the buttons name by editting the name in this box as well.

Lastly, I don't believe you can actually 'disable' a non-ActiveX button so I recommend you change the button to an ActiveX button (the second set of controls on the INSERT section of the Developer tab.

You can then name the control whatever you want (e.g. "myButton") and call it as

ActiveSheet.myButton.Enabled = TRUE/FALSE