macro that hides a row referenced in a cell, instead of just the row indicated by the cell

[susanbarbour]

Hello and I am new to VBA, so thorough answers are appreciated. I have tried searching for help, but am not sure what to search for. A spreadsheet with full code is attached.

I have 6 buttons attached to 6 macros that each hide a row or rows when clicked. However, the row numbers I need to hide keep changing, so hard coding the row number into my macro is not working. I want to make my spreadsheet a little more useful and have the range in the macro reference the value of the cell in the formula For example, cell "A4" in the example below has the value of 14 on the attached file. I want row #14 hidden, not row #4 which will be hidden according to the code as written, but I can't find a way to make that work. The next time I update the worksheet, cell A4 might have the value of 22, so I would want row #22 hiden in that instance, but whatever the row, the macro needs to point to cell A4 in order to figure out the row assignment.

Sub HideChartRow4Toggle()

Set myRng = Range("A4") '***I want the code to hide the row referenced in cell A4, not row 4, but do not know how to name the range correctly***
myRng.EntireRow.Hidden = Not (myRng(1).EntireRow.Hidden)

End Sub

Also, is there a way to make the button display a name (hide, unhide) to indicate what will happen if you click it?
Thank you greatly,
Susan

[watersev]

hi susanbarbour, welcome to Excelforum,

Please Login or Register  to view this content.

PS. You need to use code tags when posting VB code

[watersev]

codes for buttons (top to bottom) basing on the sample sheet provided:

Please Login or Register  to view this content.

[susanbarbour]

Watersev,
Thank you! I spent the better part of this week working on my little piece of code & searching forums (again, new at this). How do you unhide using your code?

[watersev]

for button_one:

Please Login or Register  to view this content.

[susanbarbour]

Ah - okay, thanks!

[Useful]

Please Login or Register  to view this content.