Are you using xl97?
IIRC, this "feature" was fixed in xl2k--but I could be remembering incorrectly.
Could you use the location of the button?
Option Explicit
Sub testme()
Dim fWks As Worksheet
Dim tWks As Worksheet
Dim AddrToClean As Variant
Dim iCtr As Long
Dim myRng As Range
Dim myShp As Shape
Dim HiddenCtr As Long
AddrToClean = Array("c6", "c10", "g13")
Set fWks = Worksheets("sheet1")
fWks.Copy after:=fWks
Set tWks = ActiveSheet
With tWks
HiddenCtr = 0
For Each myShp In .Shapes
For iCtr = LBound(AddrToClean) To UBound(AddrToClean)
If Intersect(.Range(myShp.TopLeftCell, _
myShp.BottomRightCell), _
.Range(AddrToClean(iCtr))) Is Nothing Then
'do nothing
Else
myShp.Visible = False
HiddenCtr = HiddenCtr + 1
Exit For
End If
Next iCtr
Next myShp
End With
'just a warning while testing.
If (UBound(AddrToClean) - LBound(AddrToClean) + 1) = HiddenCtr Then
'everything worked ok
'do nothing
Else
MsgBox "Only: " & HiddenCtr & " shapes were hidden!"
End If
End Sub
Lon Sarnoff wrote:
My application copies a spreadsheet to create a second spreadsheet in the
same workbook, and massages the data in the second sheet. There are four
buttons in the first sheet, and I wish to hide three of them in the second
sheet. The button numbers of the first sheet change when they appear in the
second sheet. How can I identify the buttons in the second sheet so as to
hide them using the following statement: ActiveSheet.Shapes("Button
X").visible = False
--
Lon Sarnoff
--
Dave Peterson
|