How to determine button reference

[Lon Sarnoff]

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

 

[Dave Peterson]

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