Command Button Count reset

  • Thread starter Thread starter Ed Davis
  • Start date Start date
E

Ed Davis

Is there a way to reset the command button counter.
I just added 1 command button and the number is 6120.
I do not have any command buttons in this workbook at all.
 
I'm betting that you do have lots of invisible Commandbuttons on that sheet--but
they're hidden.

If you show the control toolbox toolbar and click the design mode icon, do any
appear on that worksheet?

You could delete them manually if you could see them. But if they're really
small or scattered, it could take a longggggg time.

Instead, how about a macro?

You could delete the hidden ones or even show them:

Option Explicit
Sub testme()

Dim OLEObj As OLEObject
Dim wks As Worksheet
Dim HowMany As Long

Set wks = ActiveSheet

HowMany = 0
For Each OLEObj In wks.OLEObjects
If TypeOf OLEObj.Object Is MSForms.CommandButton Then
If OLEObj.Visible = False Then
HowMany = HowMany + 1
OLEObj.Delete 'oleobj.visible = true 'if you want to see them
End If
End If
Next OLEObj

MsgBox HowMany
End Sub
 
I went to the selection Pane did Ctrl-G and there are about 15 command
buttons in the whole workbook. I checked every sheet (about 35) I do have
text boxes on every sheet about 15 per sheet. But I have been deleting the
command buttons and replacing them with the text boxes.
I just added another command button and the number is 6152.

However I will try the macro you gave me just in case they are nor showing
using Ctrl-G special Objects.
 
I just ran this macro and get an error "User defined type not defined on the
line that starts with

If TypeOf OLEObj.Object Is MSForms.CommandButton Then
 
You could add a userform to the workbook's project and remove it later--or you
can add a reference.

Inside the VBE
Tools|References|search for "microsoft forms 2.0 object library"
and check it.

If you comment that .delete or .visible=true line, the code will just count the
number of commandbuttons (visible and hidden) and show you the number.



Ed said:
I just ran this macro and get an error "User defined type not defined on the
line that starts with

If TypeOf OLEObj.Object Is MSForms.CommandButton Then
Click to expand...
 
The how many shows 0.
but when I add a command button now it shows 6185 the last one said 6156
 
I don't think that this will work, so don't get your hopes up.

Close excel
Windows start button|Run
Type
%temp%
and hit enter

Delete all the temporary files that you see there. You may have to view the
details and stay away from the ones with today's date (they could be in use by
other programs).

Then back to excel to test it.

Please post back your results. I'm curious.
 
Just did that and the next button it produced was 6260.
I tried putting one in an empty workbook and the number was 1 (what it
should be).

Thanks for your help Dave.
 
Sorry it didn't work.

Ed said:
Just did that and the next button it produced was 6260.
I tried putting one in an empty workbook and the number was 1 (what it
should be).

Thanks for your help Dave.
Click to expand...
 
Back
Top