hide and show columns using one control button

  • Thread starter Thread starter dreamkeeper
  • Start date Start date
D

dreamkeeper

Hi.
I am great in excel but not so great in macros

I am creating a rperot that has "this year", "plan", and "last year"
columns. I want to be able to hide and show specific columns like
"this year" by pressing a "hide ty" button and once it is hidden, have
that same button now say "show this year" and then show this year
columns.

I have created two button controled macros to do the above but I want
to only have one button that toggles and the text changes from hide to
show.

I have an example of this that I can send to someone.

thank you for your help...I am a rookie!
Sub Hide_TY()
Range("F:F,I:I").Select
Range("I1").Activate
Selection.EntireColumn.Hidden = True
End Sub

Sub unhide_TY()
Range("E1:G1,H1:J1").Select
Range("H1").Activate
Selection.EntireColumn.Hidden = False
Range("F10").Select
End Sub
 
Sub Hide_TY()
Dim btn as Button
set btn = Activesheet.Buttons(application.Caller)
If Range("I1").Entirecolumn.Hidden = False then
Range("F1,I1").EntireColumn.Hidden = True
Range("E1:G1,H1:J1").Entirecolumn.Hidden = False
btn.Caption = "Show F and I"
else
Range("E1:G1,H1:J1").Entirecolumn.Hidden = True
Range("F1,I1").EntireColumns.Hidden = False
btn.Caption = "Show E:G and H:J"
End if
End Sub
 
Thank you Tom but I amhaving trouble interpretting the code to the real
spreadsheet.
I want to be able to hide and show and hide again etc.

Would you mind if I sent you the spreadsheet to look? I

Actually it is more complicated since the columns I want to hide/unhide
are also involved in an outline of grouped columns that I can
collapse.

any input would be greatly appreciated
thanks!
Tina
 
Actually, I misunderstood what you were trying to do. I now see you are
picking the rows on each side of F and I to do the unhide.

Try this instead:

Sub Hide_TY()
Dim btn As Button
Set btn = ActiveSheet.Buttons(Application.Caller)
If Range("I1").EntireColumn.Hidden = False Then
Range("F1,I1").EntireColumn.Hidden = True
btn.Caption = "Show F and I"
Else
Range("F1,I1").EntireColumn.Hidden = False
btn.Caption = "Hide F and I"
End If
End Sub
 
someone from another group gave me this and it works great and very
easy to interpret.

thanks again!
Tina

Option Explicit
Sub HideUnhide()


Dim myBTN As Button
Dim RngToHide As Range


With ActiveSheet
Set myBTN = .Buttons(Application.Caller)
Set RngToHide = .Range("F:I")
End With


RngToHide.EntireColumn.Hidden = Not (RngToHide.Columns(1).Hidden)


If RngToHide.Columns(1).Hidden Then
myBTN.Caption = "Show This Year"
Else
myBTN.Caption = "Hide this Year"
End If
End Sub
 
Not quite as simple as mine although it is essentially identical. I guess I
need to put a lot of blank lines in my code. It doesn't make any
difference it hides more columns than F and I?
 
I just spent 35 minutes looking for that other post <vbg>.

I was going to add a "see your thread in...", but you had already posted, so I
didn't.
 
Back
Top