Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

Hide/Unhide Tabs VBA

--------------------------------------------------------------------------------

I have Excel report that have 20+ tabs (too many for a single report) this report is intended for several people and these people may just want to see only one tab on the wookbook or all of them, so I think the best solution is to hide the tabs and use several macro/buttons in the first tab (each named for each tab) to see the tabs. When the work book opens all the tabs will be hidden except the Main tab where all buttons are.

The first tab has two buttons to “Show All” and “Hide All” plus several buttons named for each tabs, each tab have a "Hide Tab"-"Main Page" buttons.

The macros (showall/hideall) works fine if all the tabs are visible or hidden, or when I click in a individual name button to unhide a tab. . If I unhide individual tabs with individual buttons and the use the “Show All” button, everything works fine, the rest of the tabs will appear on the work book, but if I use the “Hide All” button when some tabs are visible it gives me a error Run time error; ‘1004 Select Method of Sheets class failed. What I thing is happening is that the macro is looking for all the tabs recorded on the macro to be visible and stops and the macro giving me the error.

What is the best VBA approach to overcome this problem? Can I modify the macro or sheet properties to make it work? Or my only solution is VBA?


Post your answer or comment

comments powered by Disqus
Good morning.

I'm new in this forum and I know that probably there is something related to this subject here but I don't have enough time to do a good research and need some help

I am creating this Excel book that contains everything related to a project but I have too many tabs and I dont need to see them all at the same time.
So I am trying to create 4 different buttons (Toggle Buttons I think are the best fit) to hide/unhide tabs related to specifics of the project.
I think that sort them by color is the easiest way than by name since there are too many tabs.

So I did a trial hiding/unhiding tabs by the tab name, but I still cannot figure how to do it by tab color, so if any one know how to do it I'll very thankful!!

Ill attach my two files so you can see what I have so far.

Thank you very much in advance.

Hi there,
I have found and adapted a piece of code which works extremely well however there are a couple of bits I need to change and wondered if anyone could help.

I have a 'Department Codes' sheet which the user can select departments the want by putting a 'Y' in column E.

In the main sheet (Reviews) where the VBA code is, I use the following formula
=INDEX('Department Codes'!E:E,MATCH(Reviews!B86,'Department Codes'!B:B,1))
This brings in all the 'Y' that match into column A.
The VBA code I use keeps all the Y rows visible and hides all other rows.

rivate Sub Worksheet_Calculate()
Dim r As Range, cell As Range
On Error GoTo ErrHandler
Set r = Me.Range("a6:a454")
Application.ScreenUpdating = False
Application.EnableEvents = False
For Each cell In r
 If cell.Value = Y Then
   cell.EntireRow.Hidden = True
 Else
   cell.EntireRow.Hidden = False
 End If
Next
ErrHandler:
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub
I need to speed the process up, so is it possible to make the Code only update when I make a change to the 'Department Codes' sheet rather than updating after every change in the whole workbook?
Also the VBA code now doesn’t allow me to Undo/Redo any changes I make, is there an additional piece of code to allow me to use the function again?
And lastly, I want to protect the 'Review' sheet from changes being made however when it is protected the VBA code doesn’t hide/unhide the rows, is there code I can use to override this?

Any help would be greatly appreciated.

Thanks
Justin.

Hi, Please seen to example file (include comment), how to make hide/unhide rows? Thank

Hello, i am preparing a sales pipeline report. I have set it up with a basic summary tab. On the summary tab i have set up a simple hyperlink that jumps to another tab that shows a sales pipeline summary for individual sales reps.

Now, with many sales reps it means there will be many tabs and it will get cluttered.

Can anyone suggest a way that i may be able to hide all sales rep tabs until a user clicks the hyperlink to that particular rep, thereby un-hiding the tab and enabling it to be seen?

I have tried the conventional Hide/ Unhide tab but this ignores the hyperlink.

Thanks in advance

Chris

Hello Everyone,

This is my first time to the forum and newbie to Excel VBA.
I have a userform labels role_selection with 6 checkboxes. Each checkbox with the caption labels as follow: Revenues, Erevenues, Occupancy, ADR, EADR and RevPAR.
These checkboxes refers to a tab where I have all the datas to hide / unhide

My question is how can i write a program to determine which checkbox is selected (true or false) and hide boxes which are unchecked (and unhide the selected boxes).
I basically need a vba code that I can assign to a command button as follows:

Hide entire columns: AI:AX

If cell BC3 = TRUE, then unhide AJ and AR columns
If cell BC4 = TRUE, then unhide AK and AS columns
If cell BC5 = TRUE, then unhide AL and AT columns
If cell BC6 = TRUE, then unhide AM and AU columns
If cell BC7 = TRUE, then unhide AN and AV columns
If cell BC8 = TRUE, then unhide AO and AW columns

Thanks you very much for your help

I was looking for some advice on VBA code to hide/unhide sheets in a dynamic named range. I have a monthly workbook (start a new workbook the first day of each month) and among the many sheets there exists one sheet for each potential day of the month, 1,2,3, . . . ,30,31. I already have dynamic named ranges with the names of the sheets that are weekdays and weekends respectively. For example, for June 2012, the weekdays dynamic range is a 22 X 1 array {1,4,5,6,7,8,11,12,13,14,15,18,19,20,21,22,25,26,27,28,29} and the weekends named range is a 8 X 1 array {2,3,9,10,16,17,23,24}. I would like to have code that hides/unhides both/either/or of these dynamic named ranges. I looked through previous postings and couldn't find something applicable. Any suggestions would be appreciated. Thanks.

I have a multi tab (50+) worksheet. At times I would hide certain tab based on the color depending on task at hand. I like a quick way to perform this task of hiding and unhiding by way of a macro with selection of buttons or some clever means representing the color of the tabs.

I have a workbook which has roughly 50 sheets. What I'm trying to do is automatically hide/unhide sheets based on the cell values in the first sheet. So in sheet1 cell A1 i would a value of FALSE which would trigger sheets1, 2, & 3 to hide, when that value changes to TRUE then those same sheets would unhide. I need to replicate that for the 10 corresponding sets of sheets, but for each grouping of sheets a different cell in sheet1 would be the trigger, cell A2 = sheets 4 - 10, cell A3 = sheets 11 - 20, etc. Any help on the VBA code would be greatly appreciated. Thanks everyone!

Hello All,

I have a number of tables (not in a list format) that each are a 10 Rows high, each one separated by 3 empty Rows.

In the third Row down of each table, the Cell in Column A indicates whether the data held in that table is active or not ("Yes" or "No").

I would like to have two Command Buttons at the top of the Worksheet to hide/unhide the the tables where the Cell (in Column A) indicates that the table is not active.

Can anyone suggest some VBA code that would do this?

Since I am trying to increase my knowledge of VBA, it would be good if any code posted also indicated what each line of the code does.

Many thanks in advance to anyone who can help!

Regards,

Matty

I'm hoping that someone can help me write a VBA for hiding/unhiding rows.

For data validation lists B27 to B30, if any of them is equal to "EBS via ULL" then unhide rows 31 to 42.
If any of the data validation list not equal to "EBS via ULL" then unhide row 44 and hide rows 31 to 42.
If "EBS via ULL" and any other product in the lists is selected, unhide rows 31 to 44.
If validation lists B27 to B30 are all equal to "Select Product..", then hide rows 31 to 44.
I would like to use the worksheets_change event.

I would appreciate any help given.

I have used the code below, which is tied to a multi-drop-down-list
in
order to unhide/hide rows (unhiding rows = yes = 1, hiding rows = no =
2.

The macro works fine, unless the worksheet is being protected. I then
get a runtime-error 1004, saying the hidden property of the range
object cannot be determined (Code-Line 8).
S.o. (Luke M) suggested that I need to have the option of "Format
rows" enabled, or I can't hide/unhide rows. The respective VBA-Code
suggestion was:
ActiveSheet.Protect DrawingObjects:=True, Contents:=True,
Scenarios:=True _
, AllowFormattingRows:=True

Can s.o. help me integrating the above-mentioned code into below-
mentioned code?
Unfortunately I have only little knowledge in VBA.... Many many
thanks!
Sub HideRows()
Dim Rng As Range
Set Rng = Sheets("Ziel1").Range("K6")
If Rng.Value = 1 Then
Rows("7:19").EntireRow.Hidden = False
Range("K6").Select
ElseIf Rng.Value = 2 Then
Rows("7:19").EntireRow.Hidden = True
End If

Help again,

I have found on the web a fantasitc piece of code (below) that prints from a form. This works like a charm!

I would like to use this same type of form to select both hide, unhide and very hidden sheet options, the code would be executed by a control button on my cover page of the workbook the same way I use this code for 'selecting sheets to print'.

I have tried playing around with this code to modify it to what I need but have crashed and burned...Any help would be appreciated.
Thanks
Tim

Sub SelectSheets()
Dim i As Integer
Dim TopPos As Integer
Dim SheetCount As Integer
Dim PrintDlg As DialogSheet
Dim CurrentSheet As Worksheet
Dim cb As CheckBox
Application.ScreenUpdating = False

' Check for protected workbook
If ActiveWorkbook.ProtectStructure Then
MsgBox "Workbook is protected.", vbCritical
Exit Sub
End If

' Add a temporary dialog sheet
Set CurrentSheet = ActiveSheet
Set PrintDlg = ActiveWorkbook.DialogSheets.Add

SheetCount = 0

' Add the checkboxes
TopPos = 40
For i = 1 To ActiveWorkbook.Worksheets.Count
Set CurrentSheet = ActiveWorkbook.Worksheets(i)
' Skip empty sheets and hidden sheets
If Application.CountA(CurrentSheet.Cells) 0 And _
CurrentSheet.Visible Then
SheetCount = SheetCount + 1
PrintDlg.CheckBoxes.Add 78, TopPos, 150, 16.5
PrintDlg.CheckBoxes(SheetCount).Text = _
CurrentSheet.Name
TopPos = TopPos + 13
End If
Next i

' Move the OK and Cancel buttons
PrintDlg.Buttons.Left = 240

' Set dialog height, width, and caption
With PrintDlg.DialogFrame
.Height = Application.Max _
(68, PrintDlg.DialogFrame.Top + TopPos - 34)
.Width = 230
.Caption = "Select sheets to print"
End With

' Change tab order of OK and Cancel buttons
' so the 1st option button will have the focus
PrintDlg.Buttons("Button 2").BringToFront
PrintDlg.Buttons("Button 3").BringToFront

' Display the dialog box
CurrentSheet.Activate
Application.ScreenUpdating = True
If SheetCount 0 Then
If PrintDlg.Show Then
For Each cb In PrintDlg.CheckBoxes
If cb.Value = xlOn Then
Worksheets(cb.Caption).Activate
ActiveSheet.PrintOut
' ActiveSheet.PrintPreview 'for debugging
End If
Next cb
End If
Else
MsgBox "All worksheets are empty."
End If

' Delete temporary dialog sheet (without a warning)
Application.DisplayAlerts = False
PrintDlg.Delete

' Reactivate original sheet
CurrentSheet.Activate
End Sub:biggrin:

Hello,

I am working on a workbook which contains multiple sheets.

Now what I am looking for is a code to hide / unhide some sheets not all of them on start up.

1- On start up all sheets are hiden, only the dashboard that control all is available.

2- I have one button on the dashboard that helps to unhide one of the sheet I work the most on. Now I need to hide the 6 other sheets at a time (not seen on start up). These 6 sheets can only be viewed by a mean of a button that unhide them using VBA and a password.

I tried to use Array but it doesn't work.

Is someone, want to help me with that?

Thank you.

Hello,

I would like to know the VBA code that should written in the commnad button
(Hide/Unhide) placed in the Excel worksheet to hide/unhide the cells that are
"empty". i.e, if the cell is empty and hide button is clicked, then the empty
cells have to be hidden. and if the cell is with by any alphanumerical
character, then that cell should not be hidden when hide button is clicked.

Hello,

I have a range F5:J20 (16 rows, 5 columns). Column F has names of 16 sheet names. Columns G, H, I, J represent 4 scenarios (called P1, P2, P3, P4) and the user has the choice to enter an "x" in any of the 4 cells (columns G to J) against a sheet name in column F. He can also choose not to enter an "x" against a sheet name at all.

Desired result: If any 1 of the 4 cells in a row against a sheet name have an "x", then that sheet should be visible, else invisible. I have attached an example file for this.

Please help me do this using VBA. I looked at the previously asked hide/unhide sheet type questions, but could not find a similar situation. Many thanks for your time and help.

Sincerely,
JC

I am new to VBA. My thanks to the community for the education. I have built a spreadsheet for work and am nearly done, but have hit a snag. Using information found on the site, I have written code to hide/unhide rows and make other entries based on cell values. There are 3 main parts to my code, all in the same Worksheet Change macro. Two parts work fine.

The remaining part works great, hiding and unhiding rows when I change the value in the target cell. Unfortunately, when I select another cell everything unhides - including the parts that were hidden based on the target cell entry

Because it is a bit long (the case goes to 16), I have only posted partial syntax (repeat case 1 & 2 thru 16). After the problem sample, I have posted a complete macro sample. I do not well understand the rules for indenting code lines and apologize for any confusion:

Problem Section:

	VB:
	
 
    Select Case (Target.Value) 
    Case "0" 
        Range("97:112").EntireRow.Hidden = True 
    Case "1" 
        Range("97:97").EntireRow.Hidden = False 
        Range("98:112").EntireRow.Hidden = True 
    Case "2" 
        Range("97:98").EntireRow.Hidden = False 
        Range("99:112").EntireRow.Hidden = True 
    Case "16" 
        Range("97:112").EntireRow.Hidden = False 
    End Select 
End If 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Sample of complete code:

	VB:
	
 Range) 
     
    ActiveSheet.Unprotect Password:="ABC123" 
    Application.ScreenUpdating = False 
    Rows("88:93").EntireRow.Hidden = Range("C18").Value = "No" 
    Rows("95:117").EntireRow.Hidden = Range("C18").Value = "No" 
    Rows("31:36").EntireRow.Hidden = Range("J30").Value = "No" 
    Rows("70").EntireRow.Hidden = Range("J30").Value = "No" 
    Rows("118").EntireRow.Hidden = Range("J30").Value = "No" 
    Rows("140").EntireRow.Hidden = Range("J30").Value = "No" 
    Rows("157").EntireRow.Hidden = Range("J30").Value = "No" 
    If Target.Address = Range("J89").Address Then 
        Select Case (Target.Value) 
        Case "0" 
            Range("97:112").EntireRow.Hidden = True 
        Case "1" 
            Range("97:97").EntireRow.Hidden = False 
            Range("98:112").EntireRow.Hidden = True 
        Case "2" 
            Range("97:98").EntireRow.Hidden = False 
            Range("99:112").EntireRow.Hidden = True 
        Case "16" 
            Range("97:112").EntireRow.Hidden = False 
        End Select 
    End If 
    If Target.Address = Range("J30").Address Then 
        Select Case (Target.Value) 
        Case "No" 
            Range("H32:H34").ClearContents 
        End Select 
    End If 
    If Target.Address = Range("J38").Address Then 
        Select Case (Target.Value) 
        Case "No" 
            Range("J44").Value = 0 
        End Select 
    End If 
    ActiveSheet.Protect Password:="ABC123", UserInterfaceOnly:=True 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


Hello,

Ive created a simple textbox which I would like the ability to hide/unhide by means of a button assignment.
I'm aware of the visible category within it's properties, and have tried changing this to both true and false, while recording a macro.. but it yields no information.

Is there a VBA code function which can hide, unhide a textbox?

Thanks

Jeff

Hi,

Before I start, I know this is 2 (very) common questions and before starting a new thread I just wanted to mention that I did researched this forum and google but because of my lack of skills with VBA it's very hard for me to adapt existing code I found online, hopefully someone can have a look and help me.

So here's the deal,

first part, hiding/unhidding the rows where value is 0, in my collumn E i have range of rows where I have a vlookup, if the result of this vlookup is zero, then hide the row completly, if not unhide it or leave it be if it wasn't hidden, so for example my first range is E13:E52, so look in that range and for each "E__" cell with a value of zero hide the corresponding cell, i.e if E16 is zero, hide complete row 16, VBA should also unhide is previous result was zero but formula update switch to a value different from zero.

Then the auto-fit of wrapped text, next to my collumn E, I have collumn F (doh!) which is also a vlookup but this time return a text value, depending on the result it can go from short to very long, I would like that row size auto-fit based on the content of cell in collumn F

and that's it I hope someone will have the patience to go over this one more time, thanks a lot.

Hi,

I have a worksheet with approx 50 worksheets in. I have 5 spin buttons that relate to the hidden sheets. I would like to use a spin button that would hide/unhide the sheets relevent to spin button.
Can anyone provide me with the vba code to make this work.

Thank you

Hi --
I want to be able to toggle the hidden/unhidden properties of several rows with the click of a button; i.e., when you click the button, rows 4 to 10 are hidden, and then when you click the button again, it unhides those rows.

For some reason, the following doesn't work; clicking the button hides the rows, but clicking it again doesn't unhide it:

	VB:
	
 
    Rows("4:10").EntireRow.Hidden = True 
Else 
    Rows("4:10").EntireRow.Hidden = True 
End If 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
What code in vba should I use so that the button will properly toggle the hide/unhide properties of the rows?

Thanks so much!

I am making a bidding program and I have a VBA code written to hide/unhide cells based on a certain cells value. So if we have 10 things to bid we'd type "10" into A5 and it would unhide 10 rows. The problem is that when we do bids often times we do multiple bids, add/change orders, and rarely end up with the same products that we initially bid. I have it so that we can hide/unhide rows based on how many products we bid.

What I'm trying to accomplish is to hide any empty rows, which would be caused by us initially bidding something out and then taking it off the bid, as well as unhiding any rows that have a bid on it.

For example, lets say we bid 10 things, they end up not wanting 3 of them, which in the bid program is in rows 3, 5, and 7. We want to be able to automatically hide those rows but keep rows 1, 2, 4, 6, 8, 9, 10.

Here is a portion of the code I have so far, should be plenty to get the idea of where I'm at. The example above would be as if I initally put "10" into A5, deleted rows 3, 5 and 7 with data still in all the other rows (so even though A5 has value 10 in there there would only be 7 rows in that range showing, and if I put "5" into A5 there'd still be showing 7 rows). I'm pretty new to VBA, infact this is my first project, I only have about 5 hours into it and I'm basically learning as I go (this is like 1/10th of the whole code that I am posting). I am pretty sure I could make a button in the spreadsheet that would manually run a macro to hide/unhide the rows that I want, which is fine and I will do it if this isn't possible and/or I have to completely start the code over, but I am trying to make using this sheet as easy and "idiot proof" as possible as there will be multiple people using it.

Also, if im doing this completely assbackwards to begin with (like I said this is basically my first project in VBA), please let me know what would've been the "correct" way to do it.

Thanks in advance!

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$5" Then
If Target.Value = NoValue Then
Range("A6:L26").EntireRow.Hidden = True
End If

If Target.Value = 1 Then
Range("A6:L26").EntireRow.Hidden = True
Range("A6:L7").EntireRow.Hidden = False
End If

If Target.Value = 2 Then
Range("A6:L26").EntireRow.Hidden = True
Range("A6:L8").EntireRow.Hidden = False
End If

If Target.Value = 3 Then
Range("A6:L26").EntireRow.Hidden = True
Range("A6:L9").EntireRow.Hidden = False
End If

If Target.Value = 4 Then
Range("A6:L26").EntireRow.Hidden = True
Range("A6:L10").EntireRow.Hidden = False
End If

If Target.Value = 5 Then
Range("A6:L26").EntireRow.Hidden = True
Range("A6:L11").EntireRow.Hidden = False
End If

If Target.Value = 6 Then
Range("A6:L26").EntireRow.Hidden = True
Range("A6:L12").EntireRow.Hidden = False
End If

If Target.Value = 7 Then
Range("A6:L26").EntireRow.Hidden = True
Range("A6:L13").EntireRow.Hidden = False
End If

If Target.Value = 8 Then
Range("A6:L26").EntireRow.Hidden = True
Range("A6:L14").EntireRow.Hidden = False
End If

If Target.Value = 9 Then
Range("A6:L26").EntireRow.Hidden = True
Range("A6:L15").EntireRow.Hidden = False
End If

If Target.Value = 10 Then
Range("A6:L26").EntireRow.Hidden = True
Range("A6:L16").EntireRow.Hidden = False
End If

End If

End Sub

I'm using a toggle button to "hide/unhide" the content of a range of cells (save as Excel xls on Excel '07). As you may notice in the formula, I'm simply changing to a white font to achieve this (depress=true). The problem is that the toggle button is not working as it should. I've copied the VBA into a blank workbook and assigned it to a togglebutton and it does work! The intended Worksheet has two other togglebuttons and buttons. I've also tried removing the password protection from the code to no avail. What's your take?

Code:
Private Sub ToggleButton2_Click()
    If ToggleButton1.Value = True Then
         'This area contains the things you want to happen
         'when the toggle button is not depressed
            ActiveSheet.Unprotect Password:="****"
            Range("G6:Q258").Select
            Selection.Font.ColorIndex = 2
            Range("C6").Select
            ActiveSheet.Protect Password:="****", DrawingObjects:=True, Contents:=True, Scenarios:=True _
                , AllowSorting:=True, AllowFiltering:=True
         
    Else
         'This area contains the things you want to happen
         'when the toggle button is depressed
            ActiveSheet.Unprotect Password:="****"
            Range("G6:Q258").Select
            Selection.Font.ColorIndex = 0
            Range("C6").Select
            ActiveSheet.Protect Password:="****", DrawingObjects:=True, Contents:=True, Scenarios:=True _
                , AllowSorting:=True, AllowFiltering:=True
    End If
End Sub


Hi,
I'm new to VBA and macros. I've tried following instructions in various posts but nothing seems to work the way I need to.

I'm working on a vocabulary practice workbook in Excel 2007 (XP platform). The first sheet contains my first round of practice. Sheet2 is using the formula =IF(Sheet1!C13=1,"",Sheet1!A13) to copy the word in Sheet1 that I didn't translate properly. The formula is repeated down each row to call all words found in Sheet1. But as you see, if the value of the formula = 1, than the word does not appear. This allows me to concentrate on the words that I'm having a hard time to learn.

My posting is in regard the possibility to hide/unhide the rows in which the word is not "called". If the value changes to 0 and the word is copied from Sheet1, than the row would need to "unhide" automaticaly.

I have tried using some of the macros similar to what I'm trying to do but without success. I have inserted a secondary formula (lets say column D of each row of Sheet2) that will switch between 1/0 to see if it would cause the macros to work but without success.

For any possible macros describe what to do step by step please.

Your help is much appreciated

Hi,

I'm using VBA to create a large number of pdf files from a workbook and am having problems with a chart moving about the page.

Basically I have a worksheet with a number of summary tables that use formula to summarise data from a number of other sheets. These tables can have between 4 and 12 rows of summary info depending on the area selected and to keep the pdf a standard look I hide/unhide a number of rows automatically beneath to keep each tables relative position on the page the same.

I also have a number of charts on the page, one of which is surrounded by these tables so has rows hidden above below and behind it. I have set the Object Positioning property to "Don't move or size with cells" but even so as my macro runs for each area that I want to produce a pdf for and the rows are hidden/unhidden the chart creaps around the page.

Can anyone suggest a solution to this? Basically I want to anchor the chart so that the botton right hand corner of it is always aligned to the bottom right hand corner of cell H79.

Cheers in advance,

Dom


No luck finding an answer? You could always try Google.