Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Show or Hide control characters in a cell (using Excel 2007)... | Excel Discussion (Misc queries) | |||
Command Button to show/hide a different worksheet | Excel Discussion (Misc queries) | |||
hide or show a series with a button | Charts and Charting in Excel | |||
Excel button :: Filter columns by value - possible? Additionally, hide certain columns | Excel Programming | |||
Macro to Hide/Show Columns based on control cell value | Excel Programming |