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

0
3/2/2006 6:04:11 PM
excel 39879 articles. 2 followers. Follow

11 Replies
554 Views

Similar Articles

[PageSpeed] 31

I put a button from the Forms toolbar on that worksheet and assigned it this
macro:

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

You know that if you hide/unhide those columns manually, then the caption will
be out of sync.

dreamkeeper wrote:
> 
> 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

-- 

Dave Peterson
0
petersod (12004)
3/2/2006 6:25:59 PM
Wow, Dave this is so great!  Thank yo so much.  Now if I can figure out
how to do this with the grouped outline  show and hide, I wll have a
world class spreadsheet!

thank you so much!
Tina

0
3/2/2006 8:32:11 PM
HI Dave,
is there something I can put at the end of that code to autofit only
the visible cells?

thank you!
Tina

0
3/2/2006 10:00:09 PM
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
    
    On Error Resume Next
    ActiveSheet.UsedRange.Cells _
        .SpecialCells(xlCellTypeVisible).EntireColumn.AutoFit
    On Error GoTo 0
    
End Sub


dreamkeeper wrote:
> 
> HI Dave,
> is there something I can put at the end of that code to autofit only
> the visible cells?
> 
> thank you!
> Tina

-- 

Dave Peterson
0
petersod (12004)
3/2/2006 10:46:14 PM
thank you Dave!

0
3/7/2006 5:36:21 PM
thank you Dave!

0
3/7/2006 6:14:03 PM
Hi Dave,
The macro you gave me works great!  Is there a way to use the same
macro to change the range to sheets of the workbook.  I would like to
use the same idea of using one control button to hide and show sheets
ina workbook.

Is there some wayt o change this code to do that?  I want to hide and
show groups of sheets.
Thanks for your help.  
Tina

0
3/28/2006 7:42:38 PM
Change the sheet names to what you want:

Option Explicit
Sub HideUnhideSheets()

    Dim myBTN As Button
    Dim mySheets As Variant
    Dim myVisible As Long
    Dim iCtr As Long
    
    mySheets = Array("sheet2", "sheet9", "sheet99")
    
    Dim RngToHide As Range
    
    With ActiveSheet
        Set myBTN = .Buttons(Application.Caller)
    End With
        
    myVisible = Sheets(mySheets(LBound(mySheets))).Visible
    
    If myVisible = xlSheetVisible Then
        myVisible = xlSheetHidden
        myBTN.Caption = "Show the Sheets"
    Else
        myVisible = xlSheetVisible
        myBTN.Caption = "Hide the sheets"
    End If
    
    For iCtr = LBound(mySheets) To UBound(mySheets)
        Sheets(mySheets(iCtr)).Visible = myVisible
    Next iCtr
    
End Sub







dreamkeeper wrote:
> 
> Hi Dave,
> The macro you gave me works great!  Is there a way to use the same
> macro to change the range to sheets of the workbook.  I would like to
> use the same idea of using one control button to hide and show sheets
> ina workbook.
> 
> Is there some wayt o change this code to do that?  I want to hide and
> show groups of sheets.
> Thanks for your help.
> Tina

-- 

Dave Peterson
0
petersod (12004)
3/28/2006 8:56:11 PM
Hopefully this will be easy to fix but I got a run time
error...subscription out of range and the debugger takes me to :
myVisible = Sheets(mySheets(LBound(mySheets))).Visible

0
3/28/2006 10:45:34 PM
Dave,
I am using excel 2003 and I am still getting the subscription out of
range error...not sure what to fix.  can you help?

Thanks,
Tina

0
3/30/2006 5:52:59 PM
I chose these names pretty much at random.

mySheets = Array("sheet2", "sheet9", "sheet99")

You'll have to change them to match what you need.  If you did this already,
then I'd bet there was a typo.

dreamkeeper wrote:
> 
> Dave,
> I am using excel 2003 and I am still getting the subscription out of
> range error...not sure what to fix.  can you help?
> 
> Thanks,
> Tina

-- 

Dave Peterson
0
petersod (12004)
3/30/2006 6:04:09 PM
Reply:

Similar Artilces:

Hiding formuals in cells question
Hi. I was wondering if there was a way to hide the formulas in cells without protecting the sheet. I am making something for other teachers at my school and I want to hide the formulas so they won't mess anything up on the spreadsheet. Any help would be greatly appreciated. Thanks! Hi not without protecting the sheet but why is this a problem for you? -- Regards Frank Kabel Frankfurt, Germany "JP" <JP@discussions.microsoft.com> schrieb im Newsbeitrag news:ABFE07BF-6087-4E24-814A-03AF8863A633@microsoft.com... > Hi. I was wondering if there was a way to hide the fo...

Not allowing Alpha Characters in a Numeric Formatted Column
I have a spreadsheet that we provide to vendors where they have to fill in their product data and send it back to us where we import this data into the products database. I have the spreadsheet set up the way I want it and have drop down boxes where applicable for data validation. I have several fields that i only want #'s. Sometimes integers, sometimes there may be a decimal. I set the formats accordingly. HOwever, you can still type the letter A or B or C etc... in there. I only want them to be able to type in #'s. In access I can use a mask. Is there anything similar in Exce...

Permanently keep holidays from showing up in Calendar2007
I had holidays automatically set to appear in my Outlook 2003 Calendar. I'm now in Outlook 2007, and would like to turn off Holidays. When I go to Tools, Options, Calendar Options, Add Holidays..., as soon as I uncheck the United States box, my OK button becomes grayed out so I'm unable to permanently keep the checkmark removed from in front of United States. I'd prefer not to individually & manually delete all the holidays. Anybody know what a fix might be? Thanks! When viewing the calendar, switch to the by category view and delete the holiday category. ...

Using Access with other db programs
I work for a health organization and we have upgraded to ACCESS after years of using various other db programs. Is it possible for me to import a DBIII file into ACCESS and then match it to another db file? If so, how? Also, is there a good book on ACCESS which discusses this type of question and good overall reference once you're past the beginner stage? Thanks. Hi. > Is it possible for me to import a DBIII file into ACCESS > and then match it to another db file? Yes. See "Supported data sources for importing, for exporting, and for linking in a Microsoft Office A...

summing a column according to criteria of two other columns
I am creating a cash flow forecast sheet and trying to add up the project values of a particular service provided in june this year. i am attempting to do this by referring to a sheet called data which has a column of project values (N), a column with the month and year of the project (V) written as 62004 and a column of services (E) of which i want to select those projects where the service was Translation - Standard. By looking at the help for countif by multiple criteria i altered the formula to a sumif. I am using the formula... =SUM(IF((Data!$E$3:$E$2000='Translation - Standard'...

Checkbox control and forms
I have a database with a few different tables but each table is pretty much the same. The tables have a set of tasks in the cells in Column A (a daily routine to be completed). All other columns in cell 1 have a number 1-31 for each day of the month. I'm trying to create a windows application (I'm using Visual Studio) that as soon as it comes up asks for the users initials, then goes to the main form with a list of tasks that comes from one of the tables (I guess I'll connect using datagrid view for the rows?), beside each task there will be a check box and when the user ...

Using custom fields for better email list view.
Hello. I get lots of letters with the same title. I want to group them by topic and sort on the number of letters in the group. I know that I can do this using custom fields but I do not know what query to use to get the number of letters the group. ...

Hiding "0" values in pivot table
In a worksheet I have a listing of stock records with ItemNumber, Date, StockLocation and NumberOnStock. When I make simple Pivot Table with ItemNumber and StockLocation as rows and select a Sum of "NumberOnStock" I would like to hide rows where the sum is "0". But this does not seem to be possible. Tried to follow the guidelines in the Microsoft article: http://office.microsoft.com/en-us/assistance/HP051998791033.aspx#Hide%20zero%20values%20in%20a%20PivotTable%20report But no luck :-( Mogens You could use conditional formatting on these cells - highlight the cells,...

hide my name
When replying to an email or creating one from scratch how do i hide my name in the "from" field...thx fred <fdbjrgetridofthis@wowway.com> wrote: > When replying to an email or creating one from scratch how do i hide > my name in the "from" field...thx Are you saying you want the reply to be anonymous? -- Brian Tillman [MVP-Outlook] no, I don't mind if my email is revealed.. just my name. I have tried by removing it from the "User Name:" field in the email account but it continues to show up. I have also removed from the header informati...

how do i use an if is null, and if is not null statement together
I have to compare two columns of data, and show the status in column three. Column1 = enrolleddate, Column 2= DisenrolledDate, Column 3 = Status If column1 and column2 are null, "Active", if column2 is not null, then disenrolleddate Thanks for helping me. If you were using a spreadsheet, you might need that third column. In Access you can simply use a query to do the comparison and generate the "calculated" value. Regards Jeff Boyce Microsoft Office/Access MVP "latha" <latha@discussions.microsoft.com> wrote in message news:A4366DA3-9E0E-48C8-BB30-452...

Hiding and Un-Hiding Data fields
I am using a form for people to select data via dropdown menus and then having it run through a query to filter results. Is there a way that I can have certain feilds hidden until a toggle button or something of the like is clicked? Basically I want have three choices of buttons: Year, Quarter, and Month. What I ideally want to happen is when someone selects the button for year, a hidden drop down menu appears where they can then choose from the available years to filter through the query. I would say I am above average with using access, but I have no idea how to hide fields and then...

Using Exchange 2003 features in a mixed mode w/ Exchange 2000 with
Hi Group, I'm researching to find out is it possible to keep my user's email accounts using IM on Exchange 2000 and add Exchange 2003 to use the new features? Since IM was removed from Exchange 2003, when I create a new user I must choose between E2K or E2K3 servers, so whatever server I choose i will use those features. Any help would be appreciated! Thanks Dave You can have the user's mailbox on a 2003 server and have htier IM virtual server be on a 2k server... -- --Brian Desmond Windows Server MVP desmondb@payton.cps.k12.il.us www.briandesmond.com "dpcrensh...

How do I hide gaps in an Excel 2007 chart?
I have a bar chart that is showing gaps and i don't want them. The select data > hidden and empty cell button does not give an option to hide gaps. I think by default they are hidden but if you even click on this to see what it does the gaps are shown and no way to turn off. Hi, If you hide the actual rows then with the Plot visible cells only setting on the gaps will be removed. In xl2007 the setting is on the Select Data dialog, Hidden/empty cells button. Cheers Andy Waynesch wrote: > I have a bar chart that is showing gaps and i don't want them. The s...

Accesiing the IO space of SMbus host controller
Hi, I need to develop a driver to read and write to a SMbus host controller. I have VIA VX855 chipset.My device is in the IO space of PCI bus 0 device 17 function 0. My device is in the motherboard so it is always connected.so it is not shown in the device manager. I looked into portIO driver but I am not sure how to get the base address of my device in the driver since it is not plug and play? Can anyone suggest me what should be my approach towards this? Thanks, vbkr Do I need to write a PCI function driver or portIO driver vbkr <vbkr@discussions.microsoft.c...

Hiding a worksheet #3
I want to hide a worksheet. So, I went to the format menu and the "worksheet" option is grayed out. Why? And, how do I "ungray" it. Hi is your worksheet protected ? -- Regards Frank Kabel Frankfurt, Germany llong wrote: > I want to hide a worksheet. So, I went to the format menu and the > "worksheet" option is grayed out. Why? And, how do I "ungray" it. I think Frank meant to ask about the Workbook--not the worksheet. (Check under Tools|protection|Protect workbook or Unprotect workbook) My question is: Is there another sheet in the w...

Cannot add columns to custom views
I have a problem that is causing a major handicap. I cannot add any columns to any custom leads view that I create. No matter what, the only column that is displayed is the default key column (name) that is there when the view is created. When I click on add columns, the dialog box that is supposed to list columns that I can add to the view has no columns listed at all. I am logged in as the System Administrator when trying to accomplish this. Also, when I create a filter criteria it is never saved. I save it, but when I go back into it, it is gone. Any help would be GREATLY apprec...

Hide images when Hiding Rows
I have inserted images into cells, but when I hide the column the images are not hidden with that column, they just get squished between the two cells next to the hidden cell. Is there a setting or way to tie the image to that cell so that it hides/unhides appropriately with the row? -- kurt ------------------------------------------------------------------------ kurt's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=35686 View this thread: http://www.excelforum.com/showthread.php?threadid=554712 Kurt You cannot insert an image into a cell. You can overlay ...

Rejected Hours not showing in PWA
We are using MS office Project 2003/SP3 and SQL2005 database. This is been happning to multiple users Resource Enters 40 hours(8 hours a day) for a Task and submits it. Project Managers Rejects the 40 hours for the resource. When the Resource opens the PWA (Project Web Access), Resource doesn't see any hours in PWA (Resource should have seen 40 hours that were rejected) In PWA Actuals Hours shows 40 hours and Remaining Hours as 40 hours, After PM rejected the hours, it should have shown Actual Hours as 80 and Remaining hours as 0 Any help on this would be greatly appreciated Thanks VJ...

control box list
Hi I have a long list of hospitals, and would like to put a control in that will fill in as you type to search. I have tried list box, but can not see how to enter the list into the find data. thanks tim ...

how to hide an ActiveX control?
hi there i have an ActiveX control, written in C++, which has the OLEMISC_INVISIBLEATRUNTIME flag set, yet when i display the web page it is attached to, i get the familiar "red cross" graphic in the middle of the screen - how can i hide the control so nothing is displayed at all? tia bhu can u set the size of ur control as 1x1 pixels "bhu Boue vidya" <bhuvidya@yahoo.com.au> wrote in message news:1175703214.292931.252210@e65g2000hsc.googlegroups.com... > > hi there > > i have an ActiveX control, written in C++, which has the > OLEMISC_INVISIBLEATR...

Repeating columns cuts off merged cells
Hi all, I hope you can help because I can't find a solution to this one. I've got a two page spreadsheet (side by side) and I'm trying to repeat columns A & B because their row headings on the left and should only appear when printed (split onto two pages) (as in print setup > columns to repeat at left > $A:$B), ...... but B57:I57 are merged and wrapped (another grief that merged wrapped cells don't auto height, but that's for another day) and so are B58:I58 and B59:I59 (footer things). ...... and when printed, B57:I57, etc. cuts off on the second page and...

Hiding plot Area
I have a formatted output sheet that has a Pie Chart embedded. The data for the pie chart comes from a second sheet using 2 ranges B2:B6 & D2:D6 B2:B6 is always populated and contains labels. What I want to do is, when D2:D6 contains 0 in each cell (or Null), suppress the Pie chart but always show the Legend (which is driven by B2:B6) Many Thanks Adam Adam - All you see of the pie when all values are zero is a single line at the first slice. The legend still appears. To hide the line, double click on one of the wedges and on the Patterns tab, change the Border setting to None. ...

Upload Button
I'm using WSS. In a document libaray, is there a way to hide or disable the Upload button on the tool bar? I can hide the options under the button but that does not disable the button. Thanks, Joel ...

Retrieve activity attachment using the CRM SDK
Hi Can I extract the content of an attachment file in an activity to an external file. Is that possible using the sdk? Anyone has a sample? Thanks Rakefet Shohat RepliWeb, Inc. http://www.repliweb.com ...

Hiding rows with VBA
Hi all, In my sheet called "insertsheet I have this VBA: (thanks to some users of Google groups) Private Sub Worksheet_Change(ByVal Target As Range) Const nMAX As Long = 10 Dim nRows As Long Application.ScreenUpdating = False With Me With .Range("B8") If Intersect(Target(1), .Cells) Is Nothing Then Exit Sub nRows = .Value End With .Range(.Cells(1, 5), .Cells(1, _ .Columns.Count)).EntireColumn.Hidden = True .Range(Cells(1, 5), .Cells(1, _ ...