Return to Button

[Gregg Wolin]

I created a button ("Reports") that toggles the visibility of a series of sheets (listed within the named range "L_ReportsNames") and assigned the macro below.

Public Sub ShowHideReportSheets()
Application.ScreenUpdating = False
For Each Cell In Range("L_ReportNames")
    ActiveWorkbook.Worksheets(Cell.Value).Visible = Not ActiveWorkbook.Worksheets(Cell.Value).Visible
Next Cell
Application.Goto (ActiveWorkbook.Sheets("i_Project").Range("a21"))
Application.ScreenUpdating = True
End Sub

My intent is to place a copy of the "Reports" button on other worksheets in the workbook. The Application.Goto line above works fine in the "i_Project" sheet , but ideally, I'd like to get the macro to "go to" the button that triggered the macro regardless of the worksheet on which a copy of the button is placed.

 

[Chihiro]

Not 100% sure on use, but assuming the code is always run from button, which resides on given sheet...

Why not just use ActiveSheet.Range("a21") instead of ActiveWorkbook.Sheets("Sheetname").Range("a21")?

 

[Gregg Wolin]

Not 100% sure on use, but assuming the code is always run from button, which resides on given sheet...

Why not just use ActiveSheet.Range("a21") instead of ActiveWorkbook.Sheets("Sheetname").Range("a21")?

I tried this and it hides the sheets on the first click, however on the next click, it unhides the sheets and navigates to cell [a21] of one of the report sheets that isn't referenced anywhere.

Public Sub ShowHideReportSheets()
 Application.ScreenUpdating = False
   For Each Cell In Range("L_ReportNames")
    ActiveWorkbook.Worksheets(Cell.Value).Visible = Not ActiveWorkbook.Worksheets(Cell.Value).Visible
        Next Cell
    Application.Goto (ActiveSheet.Range("a21"))
 Application.ScreenUpdating = True
End Sub

 

[Fluff13]

Assuming they're Forms Control Buttons, try

Public Sub ShowHideReportSheets()
 Dim Btn As Shape
 Set Btn = ActiveSheet.Shapes(Application.Caller)
 Application.ScreenUpdating = False
   For Each cell In Range("L_ReportNames")
    ActiveWorkbook.Worksheets(cell.Value).Visible = Not ActiveWorkbook.Worksheets(cell.Value).Visible
        Next cell
    Application.Goto Sheets(Btn.Parent.Name).Range("A21")
 Application.ScreenUpdating = True
End Sub

 

[Gregg Wolin]

Assuming they're Forms Control Buttons, try

Public Sub ShowHideReportSheets()
Dim Btn As Shape
Set Btn = ActiveSheet.Shapes(Application.Caller)
Application.ScreenUpdating = False
   For Each cell In Range("L_ReportNames")
    ActiveWorkbook.Worksheets(cell.Value).Visible = Not ActiveWorkbook.Worksheets(cell.Value).Visible
        Next cell
    Application.Goto Sheets(Btn.Parent.Name).Range("A21")
Application.ScreenUpdating = True
End Sub

Thanks! Works perfectly!

 

[Fluff13]

You're welcome & thanks for the feedback

 

[p45cal]

Surely, if the button you click on is in a sheet (the active sheet) then early in the macro:
Set mySht=ActiveSheet
then later:
Application.Goto mySht.range("A21")

ps. could we shorten:
Application.Goto Sheets(Btn.Parent.Name).Range("A21")
to:
Application.Goto Btn.Parent.Range("A21")
?

 

[Fluff13]

You're quite right on both counts.
Why make life easy, when you can make it more difficult