Command Button Count reset

[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.

 

[Ed Davis]

Sorry I did not realize I sent this twice.

 

[Dave Peterson]

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

 

[Dave Peterson]

ps.

If you're new to macros:

Debra Dalgleish has some notes how to implement macros here:
http://www.contextures.com/xlvba01.html

David McRitchie has an intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ron de Bruin's intro to macros:
http://www.rondebruin.nl/code.htm

(General, Regular and Standard modules all describe the same thing.)

 

[Ed Davis]

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.

 

[Ed Davis]

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

 

[Dave Peterson]

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.

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

 

[Ed Davis]

The how many shows 0.
but when I add a command button now it shows 6185 the last one said 6156

 

[Dave Peterson]

Sorry, I don't have any other guesses.

The how many shows 0.
but when I add a command button now it shows 6185 the last one said 6156

 

[Dave Peterson]

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.

 

[Ed Davis]

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.

 

[Dave Peterson]

Sorry it didn't work.

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.