VBA Toggle Button on "Fluid" Rows using a Defined Name

[shivelresk]

Hello all! Trying to add some dynamics into a chart that our office uses in some reports. We have a few areas of the chart that will be used in some reports and not others, and I would like to have the option to hide and show those sections (rows) at will. I would prefer not to manually show and hide the rows, as I would prefer a visible indicator that something is hidden. I've tried using the Grouping option, but that leaves unwanted gaps, needing to have a row dedicated to the +/- toggle.

I found the following Code to use VBA to hide rows/columns, which I used to create a toggle on Rows 6 and 7:

[COLOR=#222222][FONT=Verdana]Private Sub ToggleButton1_Click()[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]Dim xAddress As String[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]xAddress = "6:7"[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]If ToggleButton1.Value Then[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]    Application.ActiveSheet.Rows(xAddress).Hidden = True[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]Else[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]    Application.ActiveSheet.Rows(xAddress).Hidden = False[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]End If[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]End Sub[/FONT][/COLOR]

The above works as that section is set at only those two rows, but my other sections of rows are "fluid" in that they may contain more or less rows as may be desired; the start/stop of the sections can change too as a result. I have defined these two sections of rows with Defined Names. For instance Name1 ='Sheet1!$8:$17. The Defined Name value will update itself to reflect the addition or deletion of rows. I would like to use the Defined Names in a Toggle code like above. I've tried with the following, but all I try only returns errors.

[COLOR=#222222][FONT=Verdana]Private Sub ToggleButton2_Click()[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]Dim xAddress As String[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]xAddress = "LotPE"[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]If ToggleButton2.Value Then[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]    Application.ThisWorkbook.Names(xAddress).RefersToRange.Hidden = True[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]Else[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]    Application.ThisWorkbook.Names(xAddress).RefersToRange.Hidden = False[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]End If[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]End Sub[/FONT][/COLOR]

I am totally new to anything VBA, so can only go by what I can find online. I've used what I can find, but have found nothing directly covering this specific usage. Any help in getting this working would be greatly appreciated.​

 

[Fluff]

Hi & welcome to MrExcel.
How about

Private Sub ToggleButton2_Click()
   Range("LotPE").EntireRow.Hidden = Me.ToggleButton2.Value
End Sub

 

[shivelresk]

Wow. Excellent. That has worked like a charm. And so simple and compact that I opted to change the first code into the same format as well. Thanks for the help and quick response. Frustration gone. Hope you have a great day!

 

[Fluff]

Glad to help & thanks for the feedback