Good afternoon.

Using Excel 97 SR2 with Win98 SE.

I have a workbook containing one worksheet where the shapes are
disappearing. There are no shapes from the Control Box Toolbar. There
is a combination of 6 TextBoxes, 6 Spinners, 13 Checkboxes, 18
Buttons, 1 Drop Down, and 2 Comments that are a constant (always on
the worksheet).

On a daily basis, I programmatically delete and add comments (this
varies day to day, but on average about 15 comments). I also use the
AutoFilter facility frequently.

Using a routine that enumerates the shapes in a given worksheet, I
have ascertained that the shapes are not becoming "not visible"; that
they are being deleted.

I have rebuilt this file once already (about a year ago) from scratch
(created new data and objects; absolutely no transfers from the old
file to the new file).

Using the routine that enumerates the shapes in a given worksheet, I
find that the AutoFilter Drop Downs numbering increases with each
AutoFilter usage (whether done programmatically or manually) and that
the numbering is maxing out at the 65545 level (or thereabouts). E.g.
"Drop Down 65545" (maxed out unsigned integer?).

Is there a method to deal with this is so that I don't have to create
another file? Is there a way to prevent this from reoccurring?

Many thanks in advance for any suggestions

Paul Sardella

I'm gonna guess that the shapes are actually becoming not-visible--not like
..visible = false, but more like too skinny to be seen.

Maybe the next time this happens, you can try running this code:

Option Explicit
Sub testme()

'6 TextBoxes, 6 Spinners, 13 Checkboxes, 18
'Buttons, 1 Drop Down

Dim Shp As Shape
Dim testStr As String

For Each Shp In ActiveSheet.Shapes
If Shp.Type = msoFormControl Then
Select Case Shp.FormControlType
Case Is = xlButtonControl, xlCheckBox, xlSpinner
Call CheckThisShape(Shp)
Case Is = xlDropDown
testStr = ""
On Error Resume Next
testStr = Shp.TopLeftCell.Address
On Error GoTo 0
If testStr = "" Then
'do nothing, it's an autofilter dropdown.
Else
Call CheckThisShape(Shp)
End If
End Select
ElseIf Shp.Type = msoTextBox Then
Call CheckThisShape(Shp)
ElseIf Shp.Type = msoOLEControlObject Then
If Shp.OLEFormat.progID = "Forms.TextBox.1" Then
Call CheckThisShape(Shp)
End If
End If

Next Shp

End Sub
Sub CheckThisShape(Shp As Shape)

If Shp.Height < 2 _
Or Shp.Width < 2 Then
MsgBox Shp.Name & " at: " & Shp.TopLeftCell.Address
Shp.Height = 17
Shp.Width = 50
End If

End Sub

You may want to play around with those limits (I used < 2, but that was pretty
arbitrary).




am wrote:

Good afternoon.

Using Excel 97 SR2 with Win98 SE.

I have a workbook containing one worksheet where the shapes are
disappearing. There are no shapes from the Control Box Toolbar. There
is a combination of 6 TextBoxes, 6 Spinners, 13 Checkboxes, 18
Buttons, 1 Drop Down, and 2 Comments that are a constant (always on
the worksheet).

On a daily basis, I programmatically delete and add comments (this
varies day to day, but on average about 15 comments). I also use the
AutoFilter facility frequently.

Using a routine that enumerates the shapes in a given worksheet, I
have ascertained that the shapes are not becoming "not visible"; that
they are being deleted.

I have rebuilt this file once already (about a year ago) from scratch
(created new data and objects; absolutely no transfers from the old
file to the new file).

Using the routine that enumerates the shapes in a given worksheet, I
find that the AutoFilter Drop Downs numbering increases with each
AutoFilter usage (whether done programmatically or manually) and that
the numbering is maxing out at the 65545 level (or thereabouts). E.g.
"Drop Down 65545" (maxed out unsigned integer?).

Is there a method to deal with this is so that I don't have to create
another file? Is there a way to prevent this from reoccurring?

Many thanks in advance for any suggestions

Paul Sardella

--

Dave Peterson

Dave,

Thanks for the response. I probably wasn't clear. The code that I ran
to determine that the shapes were no longer there loops through the
Shapes collection, listing each Shape and its TopLeftAddress and Name.
Doing this on a backup copy of the file lists all the shapes. On the
corrupted file, not all the shapes are listed. If they were just too
skinny to be seen, all the shapes would be listed.

Anyway. I created a new file, saved the file (along with a backup for
use a year from now), and then started playing with it again. With
each AutoFiltering, the 'Drop Downs' for the AutoFilter' arrows were
increasing in the numbering.

Many thanks for your valuable insights.

Paul

-------------------------------

I'm gonna guess that the shapes are actually becoming not-visible--not like
.visible = false, but more like too skinny to be seen.

Maybe the next time this happens, you can try running this code: