How to determine button reference
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 |
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 |
All times are GMT +1. The time now is 04:58 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com