Hide/Unhide macro

[leojez]

Hello,

I have the following macro that hides selected rows in a table when clicking a shape assigned to the macro:

Sub Trade_History_Collapse_Table()
'

Range("TableWatchList5911925[#All]").Select
Selection.EntireRow.Hidden = True

End Sub

This works fine. However, I want to add the following so when the same shape is clicked it unhides the selected rows:

Selection.EntireRow.Hidden = False

I can't seem to figure it out, Any help much appreciated!

Thanks.

 

[Fluff]

If you want it to act as a toggle button, try

Sub Trade_History_Collapse_Table()
'

With Range("TableWatchList5911925[#All]")
   .EntireRow.Hidden = Not .EntireRow.Hidden
End With

End Sub

 

[leojez]

If you want it to act as a toggle button, try

Sub Trade_History_Collapse_Table()
'

With Range("TableWatchList5911925[#All]")
   .EntireRow.Hidden = Not .EntireRow.Hidden
End With

End Sub

That worked at treat, thanks!

There's an additional part ... What I'd like to do is refer to the table without using the table name. The workbook has 12 tables (one for each month of the year) for which I have a shape just above each table acting as a toggle button to hide/unhide the rows inside the table. Is there a way to have the macro select the table just below the shape, select the rows, and then hide/unhide them? Then I can use the same macro for all 12 buttons.

Either that or I would need 12 different macros for each table, each with the table name.

Thank you!

 

[Fluff]

If you make the caption exactly the same as the name of the table, you can use

Sub Trade_History_Collapse_Table()

   With ActiveSheet.Shapes(Application.Caller).TextFrame.Characters
      With Range(.Caption & "[#All]")
         .EntireRow.Hidden = Not .EntireRow.Hidden
      End With
   End With
End Sub

 

[leojez]

If you make the caption exactly the same as the name of the table, you can use

Sub Trade_History_Collapse_Table()

   With ActiveSheet.Shapes(Application.Caller).TextFrame.Characters
      With Range(.Caption & "[#All]")
         .EntireRow.Hidden = Not .EntireRow.Hidden
      End With
   End With
End Sub

Thanks ... sorry I don't know how to add a table caption - I've tried googling it but it only brings up chart captions.

 

[Fluff]

You need to change the caption in the shape to match the sheet name.

 

[leojez]

You need to change the caption in the shape to match the sheet name.

Do you mean text inside the shape? If so, no the shape is a small up/down arrow with no space for text. Any other possibilities?

 

[Fluff]

In that case are you happy to change the name of the shapes to match the table names but omitting the word table, so they would be named like "WatchList5911925"

 

[leojez]

In that case are you happy to change the name of the shapes to match the table names but omitting the word table, so they would be named like "WatchList5911925"

Yes, sure. How would I do that?

 

[Fluff]

Select the shape & in the name box (to the left of the formula bar enter the name of the table (without the word table) & then you can use

Sub Trade_History_Collapse_Table()
   With ActiveSheet.Shapes(Application.Caller)
      With Range("Table" & .Name & "[#All]")
         .EntireRow.Hidden = Not .EntireRow.Hidden
      End With
   End With
End Sub