Hide/ Unhide Sheets With Macro

Nov 6, 2008

I want to run 2 different macros:

Macro 1- hides Sheet1 and unhides Sheet2
Macro 2- Hides Sheet2 and unhides Sheet1

I used the macro recorder to attempt to make this work but am running into a problem if Macro1 is run two times consecutively. In this situation the macro displays a debugging error b/c Sheet1 is hidden. Is there a way to get around this...possibly using an if then statement?

ADVERTISEMENT

Macro To Unhide Then Hide Sheets

Sep 29, 2008

I have 16 sheets and 4 additional sheets that will kind of 'Group' these 16sheets. For example: I have 'Sheet1', 'Sheet2', 'Sheet3'......, 'Sheet16'.
4 additional Sheets are: 'Group1', 'Group2', 'Group3', 'Group4'.

I need a help with macro so that when this workbook is open all 20 sheets ('Sheet1', 'Sheet2', 'Sheet3'......, 'Sheet16') will go into hiding and only 4 additional Sheets ('Group1', 'Group2', 'Group3', 'Group4') will be visible. Now, these 4 sheets will have the command button links to the following sheets:

Sheet 'Group1':'Sheet1', 'Sheet2', 'Sheet3', 'Sheet4', 'Hide All'
Sheet 'Group2':'Sheet5', 'Sheet6', 'Sheet7', 'Sheet8', 'Hide All'
Sheet 'Group3':'Sheet9', 'Sheet10', 'Sheet11', 'Sheet12', 'Hide All'
Sheet 'Group4':'Sheet13', 'Sheet14', 'Sheet15', 'Sheet16', 'Hide All'

This being said, when you click on each command buttons, the respective sheets will open up and when click on 'Hide All', all of the open sheets for that *additional sheet' for example sheet 'Group1' will go into hiding again.

View 9 Replies View Related

Macro To Unhide / Hide Sheets With Combobox Selection

Jun 18, 2007

I have a workbook that contains approx 50 sheets and will grow to somewhere in the region of 200.

The majority of sheets, which contain the raw data referenced by the renaining sheets, are hidden. I will occasionally need to update the data in some of those hidden sheets and would like an easy / quick way of unhiding them.

The front page has several comboboxes which select the page needed for the calculation being performed, eg I select Chapter2 in the first combobox, section 4 in the second and page 12 in the 3rd. The output is combined / abbreviated into into a cell eg Ch2-Sec4-P12. That being the name of a sheet I then use INDIRECTs to retrieve the data I want and place it in a spare sheet, that works well.

I'd like to do the same to select the sheet to unhide. I can setup the comboboxes to give the name of the sheet I want to unhide / hide in a cell but then I'm stuck;

How can I use the contents of a cell in place of the sheet name in a macro command such as Sheets("data").Visible = Not (Sheets("data").Visible) ?

View 9 Replies View Related

VBA To UnHide / Hide Sheets

Feb 20, 2014

I have a workbook with about 20 worksheets in it.

6 are visible
3 are hidden

the remaining are very hidden

I'm creating a "welcome" page to the workbook with instructions on how to update data. The data between the 20 sheets consists of pivot tables, charts and summary data. The Visible sheets are data for management's review (all protected). The 3 hidden sheets are pivot tables that the user needs to pull data from and the very hidden sheets are not to be seen by anyone.

In my "welcome" page, I added the instructions of how to update data, but wanted an area where I could assign a checkbox or button to click on for the user to unhide my 3 hidden sheets (say Sheet1, Sheet2 and Sheet3). I do not want it to unhide my very hidden sheets. Then, when the information needed is retrieved from those sheets, I would like the user to use a checkbox or button to hide the 3 hidden sheets again. Is there a way to do this?

I tried creating custom view but couldn't do that because of the pivot tables (the option was disabled). I'm a beginner in VBA so don't even know how to begin.

View 6 Replies View Related

Hide Sheets, Then Need Password To Unhide

Oct 6, 2008

See I have hidden my sheets and I need them to stay hidden.

But when I need to unhide them, a password should be needed to unhide them.

Maybe add a button on one of the open sheets that when pressed will unhide the sheets but will promt for password.

View 9 Replies View Related

VBA To Hide/Unhide Sheets Based On Cell Value

Jun 2, 2008

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.

View 9 Replies View Related

Hide/unhide Selected Range In All Sheets

Aug 10, 2007

I have several workbooks, and the workbooks can include several sheets. Is there's an easy way to create a macro so the user selection to be hidden or unhidden is hidden or unhidden in all sheets in the workbook. E.g. if the user selection is to hide rows 54-189, the macro hides rows 54-189 in all sheets in the workbook.

View 2 Replies View Related

Excel 2007 :: Hide And Unhide Sheets Using Checkboxes?

May 13, 2014

Using Excel 2007, I have a workbook with 7 sheets. The first one is a Navigation Page where I have checkboxes (form controls, not active X) with the names of the other 6 sheets. When the box(es) are checked, the sheet(s) become visible. I have accomplished this by assigning macros I recorded.

I need to now add the opposite: When the box(es) are unchecked, the sheets become hidden. From googling and looking for other threads/forums here, I gather that I need to add code/ VBA, but I know nothing about these at all.

View 2 Replies View Related

Excel 2013 :: How To Hide And Unhide Headings From All Sheets At Once

May 14, 2014

i want to hide and unhide headings from all sheets at once (Excel 2013)

View 8 Replies View Related

Hide Selected Sheets And Not Unhide Without Entering Password

Mar 18, 2014

I want to hide selected sheets and when i want to unhide them, ask for password to unhide the sheets.

View 10 Replies View Related

Capture Cells From One Sheet Into Another. Hide/Unhide Sheets

Oct 9, 2009

There is 2 parts to my question and I will explain them as best as i can.

1. I have a front sheet and 12 other sheets ( 1 for each month of the year) they all have the same layout. On my front sheet I want to create a button which will capture the sheet onto my front sheet for the current month, whether this means to make 12 buttons to choose which month is displayed on the front sheet or one that detects the date by system time i dont mind.

2. These 12 sheets will ideally be hidden and what I am wanting is again, on the front sheet 12 buttons for each sheet to bring up the corresponding hidden sheet so they can be viewed, and then on each of the 12 sheets another button which will hide them and return the user to the front sheet.

View 10 Replies View Related

Macro To Hide/unhide A Row

Sep 23, 2007

I made this simple little macro to hide/unhide a row on a different sheet based on a check box. The row hides fine. The problem I have is unhide. I uncheck the box, but the row stays hidden. What am I missing to make this little gem come to life? This is for tracking popcorn sales for scouting and I'm trying to make it as easy to use as possible.

Sub Patrol1_Scout1()
If True Then
Sheets("Show_n_Deliver_Sold").Rows("7:7").EntireRow.Hidden = True
End If

If False Then
Sheets("Show_n_Deliver_Sold").Rows("7:7").EntireRow.Hidden = False
End If
End Sub

View 9 Replies View Related

Macro To Hide/Unhide Worksheets

May 3, 2007

Develop a macro that will enable me to hide or unhide worksheets.

Basically, I have a few worksheets in my workbook and I want to have a page at the front with two buttons. One for Unhide sheet and the other for Hide sheet. Once activated, I want it to give me a list of worksheets that I can click to hide or unhide (depending on the button i click).

View 13 Replies View Related

Column Hide/unhide Macro..

Nov 9, 2008

I am trying to hide/unhide several columns by hitting a button. It's a pretty simple macro to do it once, but how do I get a button to hide on the first click, then unhide on the second?

View 3 Replies View Related

Hide Or Unhide Columns With One Macro

Nov 21, 2008

I have a single button I want to use to call a macro to:
1.Hide columns C:AZ if they arent already hidden
2.Unhide columns C:AZ if they are already hidden.

View 2 Replies View Related

Macro To Hide / Unhide On Cell

Oct 20, 2012

need to create a macro that will hide/unhide rows. I have a list of items that need to be completed and under each item is another list that explains how to complete the items on the first list.

Let’s say for example, How to bake a cake

I want rows 2 – 6 to be hidden when the file is opened and then unhide when A1 is clicked. Then hide again when A1 is clicked again.

I have a huge list of items that need to work in this format.

The other thing I need is when the document is printed all of the hidden items are printed.

View 7 Replies View Related

Set Up Macro That Will Hide And Unhide Columns?

Nov 27, 2012

I want to set up a macro that will hide and unhide columns.

View 9 Replies View Related

Hide Unhide Rows Via Macro

Feb 26, 2010

I have a workbook that has 500 rows. In order to be able to print the spreadsheet, I have added a Macro in that hides any rows that have a "0" in the A column. I then put an if/then formula (ex. if(isblank(A5),0) so that if the cell was blank and 0 would be there and therefore the row would be hidden.

The problem now is that I want a new row to become unhidden everytime the row above has data in it. So, if row 5 gets data put into it, row 6 would become unhidden. The problem I'm running into is that the data in column A is peoples names, and therefore each row will have a different name (i.e. different data).

1. Is there a macro to do this?

2. (This may be a really stupid question, but...) Can you run 2 macros in the same sheet?

View 9 Replies View Related

Macro To Hide & Unhide Certain Columns

Sep 19, 2008

I'd like a macro that can hide/unhide certain columns. At the moment, the columns I want to hide/unhide are F, I, M, P, U and Y.

View 4 Replies View Related

Hide/Unhide Columns With Macro

Sep 3, 2007

I need hide/show some column by using Macro Button. I have attached the excel sheet( name VBA testing.xls). I need to hide column K,L,N,O & visible column G,H by clicking button "Plan A".Similarly i need to hide the column G,H,N,O & unhide the column K,L by clicking the button "Plant 2. Similarly by clicking the Button "Plant 3", hiding the column G,H,K,L are needed whereas column N,O will be unhide.

View 7 Replies View Related

Macro For Hide / Unhide Columns With Same Button

Nov 28, 2012

Need a macro for each button.

The sheet will be protected with a password (in the future, users will have varied access privileges).

Column A is designated as the "Button" Column.

There are 5 buttons here. Each representing the area on the sheet that needs to be viewed. Once the button is pressed, it takes you to that section of the sheet. At this time, I have designated each column area as:

a-z
aa-az
ba-bz
ca-cz
da-dz

I have tried this formula with opening tabs, but this won't work.

View 5 Replies View Related

Hide/unhide Col Macro Based On Cell Value

Aug 27, 2009

I have a worksheet that contains 10 columns of data. In row 2 I have a formula that will display the result "x" if the data in that column should NOT be hidden, and for all columns of data without an "x" in row 2 I would like to hide the entire column.

Therefore, I am looking for a macro that looks across the range of cells E2:N2 and if there is not an "x" in the cell, then hide that column...

To make things a little more complicated, the value in row 2 will change when other values are amended on other worksheets that feed into this one and I will need the macro to 'unhide' the column as soon an "x" appears in row 2

View 9 Replies View Related

Listbox Macro To Hide / Unhide Rows In Different Sheet

Jun 27, 2014

In the attached abbreviated example, what should the second line in each of the three macros read to direct the hiding/unhiding of rows in the adjacent sheet? Right now I'm trying a If/Then configuration but it's not working. I was able to figure this out using grouped check boxes but a list box should be graphically "cleaner" and hopefully simpler in coding. The original table list is hidden in column "A", if that is important. I could use a combo box but the menu list is short and I think the list box approach will be simpler.... If there is a way to combine the three macros into one.

View 2 Replies View Related

Macro To Hide / Unhide Worksheet Based On Cell Value

Jul 18, 2010

I would like to create a macro/vb code that will unhide a worksheet if cell value is greater than zero, or hide the worksheet if the cell value is zero.

I'm not too familiar with VB code and was wondering if this is possible? I have my doubts, since it means that the code must constantly be running in an endless loop, checking to see if the value has changed?

View 9 Replies View Related

Hide / Unhide Columns Macro Based On Cell Value?

Dec 7, 2011

I want to hide and unhide columns based on a cell's value.

If D6 of the ‘Summary’ Worksheet Is <> to ‘Test1’ and <> ‘Test2’
THEN Hide columns D:K of the ‘Charts – Source Data’ Worksheet
OTHERWISE Unhide columns D:K

View 4 Replies View Related

Record Macro To Hide And Unhide Columns (one Button)

Jan 10, 2012

I'm trying to record a macro which will hide and unhide columns K:P of data, but I only want one button. I know how to do this to produce one button for hiding and another for unhiding...but I want one combined button.

How to use vba, how I do this via the macro recorder?

View 9 Replies View Related

Macro To Hide And Unhide Rows Based On Cell Value

Jan 2, 2014

I am trying to create a macro that will work on my master summary sheet. The sheet pulls information from other worksheets. I would like to have a macro that will look at range E7:E356. If the value is 0 then hide, but if the value changes then unhide the row. I have tried autofilter but it will not update automatically.

View 1 Replies View Related

Macro To Hide/unhide Rows Based On A Returned Value

Feb 4, 2008

I need to do: Based on whether a cell returns "Export" from a vlookup function, I need a macro to run, hiding some cells and unhiding others at the same time. Also, if possible, if the cell's value changes from "Export" to something else, I need it to revert back to the original hidden/visible rows.

View 9 Replies View Related

Macro To Hide/unhide Rows Based On Cell Value

Jun 17, 2003

Like to create a macro (button) to hide / unhide rows based on a cell value

Example

A1=No then hide roa A
C1=No then hide row c

View 9 Replies View Related

Conflict Between Auto Save&close Macro And Show/hide Sheets Macro

Oct 16, 2009

I am trying to make a save&close workbook macro.

I found several examples on google, but unfortunatly it conflicts with another macro I use for forceing users to enable macros (hide all sheets except one if macros are disabled).

The attached file is an example contaning the save&close code and the show/hide sheets depending on macros enabled.

If the file is opened with macros disabled then only one sheet will be visible.
If the file is opened with macros enabled other sheets are visible.

The problem if that this code uses a custom save, witch makes the save&close not save... (in module1 and in ThisWorkbook)

The pourpose of the save&close is to make sure some users don't forget the excel open and thus block access to it. So if a certain idele time passes excel has to save and close without any confirmation messages.

View 10 Replies View Related

Hide All Sheets When Macro Is Disabled?

Aug 2, 2014

continued from: [URL]

Option Explicit forces explicit declaration of all variables in the code. That is why i had to declare ws as worksheet using the Dim statement before using it in the code. It is recommended to have it - however for this code it wouldn't have mattered.

I'm not sure I exactly follow what you imply here. But yes, you can hide all the sheets at first and then "unhide" at the start of the macro. Let's say you have a "Start" sheet.. then:

[Code] .....

The workbook is used by other people too. I cannot just tell them to hide the sheets, it defeats the purpose. What I want is, when macro is disabled, they will only see the START sheet, otherwise, all sheets will be visible. To add to this, a new sheet is added everyday. Sheets name are calendar dates in mmdd format

View 4 Replies View Related

Macro - Hide Multiple Sheets

Mar 10, 2014

What would be the macro if i need to hide multiple select sheets? I'm working on a test with 12 sheets. Even number sheets contains the fields that they need to answer and the Odd number sheets contains the formula for score computing including the answers so it needs to be like this:

Sheet 2 - The test
Sheet 3 - Must be hidden
Sheet 4 - The test
Sheet 5- Must be hidden
Sheet 6- The test
Sheet 7 - Must be hidden

I already have the code to unhide all sheets, just need the macro to hide specific sheets like the ones above.

View 2 Replies View Related

Macro To Hide Sheets In Workbook

Jun 3, 2014

I have a workbook that I edit daily then pass along to another team. All of my modules are passwork protected and there are also several sheets in the workbook that I hide before email it to the other team. I hide the sheets by changing the sheet properties in the VBA editor to "2 - xlSheetveryhidden".

This isn't a very time consuming process but I am wondering if there is a way to automate it through VBA?

I tried the the fail safe of "see what happens when I record it" but all I got was an empty Module.

View 2 Replies View Related

Hide/show Multiple Sheets By Macro?

Oct 29, 2008

I got a quite huge excel file with multiple sheets. For convenience sake I want to group and hide all the sheets not necessary for the viewer.

View 14 Replies View Related

Excel 2003 :: Macro To Hide Multiple Sheets?

May 10, 2012

I wrote a macro to select multiple sheets by name and hide them, but I keep getting the following error message:

"Object variable or with block variable not set".

Below is the macro:

Sub HideSheets1A()
Dim ws As Worksheet
Application.DisplayAlerts = False
If ws.Name = "Variance Evaluation" Or "Investment" Or "Costs & Incentives" Or "Revenues Total" Then ws.Visible = False
End Sub

I use Excel 2003

View 5 Replies View Related

Macro To Hide Column Based On Row Value (multiple Sheets)

Jan 29, 2014

I would like to have a macro that will hide a column based on the value in the row. I have multiple sheets and if row 4 (or more specific, B4:AL4) contains a zero, then I'd like the whole column to be hidden on that sheet. I'd like to be able to run the macro and it evaluate every worksheet.

View 5 Replies View Related

Hide / Unhide One Row At A Time?

Dec 1, 2012

I have this file in which I have 25 empty rows in between different categories. On the side of each category there is a plus sign which is supposed to unhide an empty row below the last used one, and a minus sign which should hide the last empty row within the category (i.e. mobilization, earthworks...). I faced many problems and tried to simplify it as much as possible (trying the code for only one category, msgboxes with the values to understand what was going on) but no luck. The problem that I have been stuck at the moment is that when I try to count the rows from C73 to the last used row, it bypasses the hidden ones. I have a mess of a code and a print screen which I am attaching. printscreen.jpg

VB:
Private Sub Worksheet_BeforeDoubleClick( _
ByVal Target As Range, Cancel As Boolean)
Dim rInt As Range

[Code].....

View 3 Replies View Related

Hide Row Based On 0 Or Unhide When Less Than 0

Oct 16, 2013

Trying to hide a rows based on value being inserted to A1 = 0 (if value is >0, then unhide), but it's not working.

Private Sub Worksheet_Change(ByVal Target As Range)
If Range("A1").Value = 0 Then
Rows("33:42").EntireRow.Hidden = True
Else
Rows("33:42").EntireRow.Hidden = False
End If

View 6 Replies View Related

Hide / Unhide Certain Rows

Apr 3, 2014

I highlighted a selection of rows and clicked HIDE, now I want to unhide certain rows and when I highlight the rows above and below and right click - unhide nothing happens. I need to Unhide to find something.

View 2 Replies View Related

Hide/Unhide The Columns

Jun 16, 2009

I've come across a spreadsheet where certain rows and columns (typically top rows or left columns) are hidden; however, there's no way to unhide them (the unhide function is grayed out) and it doesn't seem to be protected or no visible macros/vba on the file.

View 3 Replies View Related

Number & Row Hide/unhide

Sep 11, 2009

when i type 100 in the cell & press enter it automatically change to 1..and when i type 1000 i change to 10.

i have select B1 to B5 then hide it.then i click A1.how do it unhide it?

View 5 Replies View Related

ADVERTISEMENT