Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Maintaining cell reference after sorting | Excel Discussion (Misc queries) | |||
Command Button anomalies | Excel Discussion (Misc queries) | |||
RELATIVE REFERENCE BUTTON | Excel Discussion (Misc queries) | |||
Command Button VBA code | Excel Discussion (Misc queries) | |||
Macro to simply bring up the Find dialogue box?? | Excel Discussion (Misc queries) |