hide and show columns using one control button
 

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

hide and show columns using one control button
 

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

--
Regards,
Tom Ogilvy


"dreamkeeper" wrote in message
oups.com...
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

hide and show columns using one control button
 

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

hide and show columns using one control button
 

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

--
Regards,
Tom Ogilvy


"dreamkeeper" wrote in message
oups.com...
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

hide and show columns using one control button
 

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

hide and show columns using one control button
 

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?

--
Regards,
Tom Ogilvy

"dreamkeeper" wrote in message
oups.com...
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

hide and show columns using one control button
 

Hey!

Those blank lines came from the paste--not the original post.



Tom Ogilvy wrote:

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?

--
Regards,
Tom Ogilvy

"dreamkeeper" wrote in message
oups.com...
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


--

Dave Peterson

hide and show columns using one control button
 

See my answer to your other post in Misc

<just joking

--
Regards,
Tom Ogilvy

"Dave Peterson" wrote in message
...
Hey!

Those blank lines came from the paste--not the original post.



Tom Ogilvy wrote:

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?

--
Regards,
Tom Ogilvy

"dreamkeeper" wrote in message
oups.com...
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


--

Dave Peterson

hide and show columns using one control button
 

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.



Tom Ogilvy wrote:

See my answer to your other post in Misc

<just joking

--
Regards,
Tom Ogilvy

"Dave Peterson" wrote in message
...
Hey!

Those blank lines came from the paste--not the original post.



Tom Ogilvy wrote:

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?

--
Regards,
Tom Ogilvy

"dreamkeeper" wrote in message
oups.com...
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


--

Dave Peterson

--

Dave Peterson