Button to Unhide Columns on two worksheets

[Analyst232]

Hi Everyone,

I am brand new to VBA/Macro. I am trying to develop a DIY forecasting template and need some help creating four buttons.

Request
I would like to create buttons to consecutively unhide a column/row (preferably within a defined range) and have this button (macro) linked to another sheet within this workbook.

Progress?
I developed Macros for each of the buttons, but it is pretty much the same Macro that works okay, but references the next active cell, does not have a defined range, and is not linked to the other worksheet.
Please see my descriptions/screenshot below and I would really appreciate any help . Thank you!

1. Red Button: Unhides the next single column from: Column H - AH
2. Green Button: Unhide the next single row from: Row 8 - 16
3. Blue Button: Unhide the next single row from: Row 18 - 32
4. Gray Button: Unhide the single row from: Row 34 - 37

Red Button:
Sub UnhideColumn()

Sheet1.Protect Password:="pass123", UserInterfaceOnly:=True
ActiveCell.Offset(0, 1).Range("A1:A1").Columns("A:A").EntireColumn.Select
Selection.EntireColumn.Hidden = False

End Sub


Green/Blue/Gray Button:
Sub UnhideRow()
Sheet1.Protect Password:="pass123", UserInterfaceOnly:=True
ActiveCell.Offset(1, 0).Rows("1:1").EntireRow.Select

Selection.EntireRow.Hidden = False

End Sub

 

[Fluff]

Hi & welcome to MrExcel.
Which sheet do you want to unhide the rows/columns on?

 

[Analyst232]

Hi Fluff,

I would like the person to click and unhide rows and columns in sheet 1 (forecasts), but it would simultaneously unhide the same rows and columns in sheet 2 (revenues).

Thanks!

 

[Fluff]

Ok, try this for the columns

   With Sheet1.Range("G1:AH1").SpecialCells(xlVisible).Offset(, 1)
      .EntireColumn.Hidden = False
      Sheet2.Range(.Address).EntireColumn.Hidden = False
   End With

 

[Analyst232]

Fluff,

This is VBA code is amazing and works Flawlessly!

I have a quick question.. How difficult would it be for us to unhide one column in Sheet1 and two columns in Sheet2 only? Totally okay if we cannot add this in.

THANKS!

 

[Fluff]

How about

   Sheet1.Range("G1:AH1").SpecialCells(xlVisible).Offset(, 1).EntireColumn.Hidden = False
   With Sheet2.Range("G1:AH1").SpecialCells(xlVisible)
      .Offset(, .Columns.Count).Resize(, 2).EntireColumn.Hidden = False
   End With

 

[Analyst232]

Worked like a charm!!!

Any idea on the rows?

 

[Fluff]

How about

   Sheet1.Range("A7:A16").SpecialCells(xlVisible).Offset(1).EntireRow.Hidden = False
   Sheet2.Range("A7:A16").SpecialCells(xlVisible).Offset(1).EntireRow.Hidden = False

Just change the ranges for the other two buttons

 

[Analyst232]

Perfect!! It works exactly the way I needed it to and I can't thank you enough for the quick replies and experienced knowledge.

Say if I wanted to add few more buttons in the future that hide the columns (act as an undo button), would I just need to swap "False" with "True"? or is it more complicated with that?

Thanks again!

 

[Fluff]

It's more complicated than that.