Hide/ Unhide Sheets With Macro
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?
View Complete Thread with Replies
Related Forum Messages:
Macro To Unhide Then Hide Sheets
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.
Macro To Unhide / Hide Sheets With Combobox Selection
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) ?
Hide/unhide Selected Range In All Sheets
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.
Capture Cells From One Sheet Into Another. Hide/Unhide Sheets
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.
VBA To Hide/Unhide Sheets Based On Cell Value
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.
Macro To Hide/Unhide Worksheets
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).
Macro To Hide/unhide A Row
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.
If True Then
Sheets("Show_n_Deliver_Sold").Rows("7:7").EntireRow.Hidden = True
If False Then
Sheets("Show_n_Deliver_Sold").Rows("7:7").EntireRow.Hidden = False
Hide Unhide Rows Via Macro
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?
Hide/Unhide Columns With Macro
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.
Hide/unhide Col Macro Based On Cell Value
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
Conflict Between Auto Save&close Macro And Show/hide Sheets Macro
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.
Could someone provide me with the VBA to 'toggle' between HIDING and UNHIDING columns within one macro
e.g. toggle between this hiding and unhiding the following
Selection.EntireColumn.Hidden = True
Hide/Unhide The Columns
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.
I have a spreadsheet that has 28 columns for time entries. Typically only the first 12 columns are used, so I would like to hide the remaining 16 columns (which makes the spreadsheet much more user-friendly). It would probably be nearly impossible to teach all of them how to Unhide the remaining columns (and re-Hide), plus I would like to use the full-screen function when employees enter thier times. I would like to use a form control in the column heading so that when the employees 'check' it, it will Unhide and then re-Hide the columns. Any way to do this? Seems like a VB thing to me (out of my league, but would be happy to add one in!).
Hide And Unhide A Combo
I 'd like to open a xls where a combo appears only when i select a specific cell.
I created a combo in excel 97 (combobox1) and also i wrote some code for its behavior but it doesnt work.
Private Sub workbook_load()
Private Sub ComboBox1_Change()
If ActiveCell.Activate = E4 Then
Hide, Unhide Rows And Count!?
I have a code that will hide all rows that have a "-" in Colum A, leaving me with the useful data.
However, this data is pulled from a front sheet that has data added to it.
What I would like my code to do is to relook at all the hidden cells, and if their value has changed from "-" to what ever the data may be then to unhide that row, displaying the data.
The code I'm using currently is: ....
Code To Hide/Unhide CommandButtons
I have a file which contains many worksheets with many buttons on them for doing administrative work when updating data. I want to be able to click on a button on my "admin" worksheet to hide all the other buttons on the other worksheets. This is hide the buttons from the view of 'readers' so that they will not be tempted to click on a button to see what it might do. I have tried several different variation of coding, but can not seem to get the "right" object to work with so as to set the code to be xxxx.visible=false. I seen references to Shapes, Shape, Control Toolbox buttons objects, CommandButton, etc.
I would really appreciate if someone could give some pointers on how to designate in VB code, which will lend itself of future updates, the following:
in a toogle button, loop through the series of worksheets making each command button on that sheet set visible = false. Then for the other portion of the toogle repeat the process, but set visible = true.
Hide And Unhide A Group Of Cells
Is there a way to hide certain cells using the protect sheet option? Checking the hidden box on the format cells dialogue doesn't seem to do it. I know you can do it by entering ;;; in the custom formating but then if you have to remember which ones you did that too. I would like to be able to hide and unhide a group of cells easily.
Hide/Unhide Rows With Checkbox
I've had luck with hiding rows with a checkbox, but I cannot get them to unhide when I uncheck the box.
Here's my scenario - (using Excel 2000) In an estimating spreadsheet I have a print range of A1:N74. What I'd like to do is add a checkbox or button so I can hide and unhide rows based on a null or zero in column range b5:b62. This is for printing purposes, but I can't get a 'beforeprint' event to work either. All I need is box checked and cells hide, box unchecked and cells show (unhide).
Dim Rng As Range
Dim MyCell As Range
Set Rng = Range("B5:B62")
For Each MyCell In Rng
If MyCell.Value = "" Then
MyCell.EntireRow.Hidden = True
Hide/Unhide Row Event
It seems there may be an event or something that happens when a row is hidden/unhidden.
I have this simple function that gives TRUE / FALSE if the Cell's Row or Column is Hidden.
Public Function IsVisible(MyRange As Range)
IsVisible = ((Rows(MyRange.Row).Hidden = False) And _
(Columns(MyRange.Column).Hidden = False))
And a formula in a cell
When I hide/unhide the ROW - the formula recalculates automatically without having to do the F2 Enter thing..
But when I hide/unhide the COLUMN, it does not recalculate automatically. I have to do the F2 Enter thing to get it to recalculate.
Even putting Application.Volatile in there doesn't do it.
I'm just curious why it recalculates when the row is hidden/unhidden but not when the column is hidden/unhidden? Is there a hidden event that could be used? I often see posts of people trying to prevent hiding rows.
Hide / Unhide Column With A Button
Using Excel 2007, I figured out how to insert a Toggle Button and program it to hide or unhide my columns F and G (which show cost data for products we sell) depending on the state of the button.
What I'd like to add to the button is text and functionality as follows:
- Columns F and G are shown, Toggle button IS NOT depressed and the button says "Hide Cost"
- Columns F and G are hidden, Toggle Button IS depressed and the button says "Show Cost"
I figured out how to change the text on the button, but can't figure out how to change it based on the button state. I know there has to be a way to do this.
Toggle Hide/Unhide Rows
I have a macro that works fine with just numbers as a test run when I use it in the desired workbook/worksheets (where the values are returned via Indedx/match functions) there appears to be a small problem. (Below is the relevant post.)
Background is that I load raw data into a monthly account sheet which has the appropriate cost centre codes for allocating expenses.
I then use a vlookup in combination with indirect to place the summary data by costcentre code into annual worksheet.
I then have a summary sheet for printing purposes so I can print out a single month expenses by code to staple to the appropiate monthly costcentre statement. This is done by using Index & match functions looking at the annual summary sheet
What seems to be happening is that the code is sometimes recognizing formulas as a value & therefore showing zero values when I want only values 0 (as I sometimes have minus values).
Also sometimes when I go to unhide the all the rows it also is recognising some formulas as having value.
I am using Xl 2003.
Open, Hide, Unhide, Close
this has to be the easiest thing ever. I want to (on opening one file) open another, and hide it (the second file), and then when closing the first file, I want to close the second file as well....
Hide/unhide Rows With Buttons
I am using several toggle buttons to hide/show rows of data related to the specific toggle buttons. I also want to hide and show the toggle buttons as well. When I try to do that the data underneath the toggle buttons is hidden but the toggle buttons don't hide with the cells.
A solution for the problem that I am having would be to have the ability to click on a cell as if it were a toggle button and hide/show rows of data when clicked.
If there is a way to hide/show buttons as well would work too.
Auto Hide/Unhide Rows
I'm using this VBA code which is hiding cells in rows 59:111. Some of these cells have formulas and some don't.
Private Sub Hide_Unhide()
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
For i = 59 To 111
Rows(i).Hidden = (Cells(i, "b").Value = 0) + (Cells(i, "b").Value = "")
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Here is the problem I'm running into: I want this to run in the background so the user won't have to click anything to hide/unhide the cells whenever the options in the drop down list are changed and the data expands/collapses.
Code TO HIDE Or UNHIDE Row
I am using the code below on my spreadsheet. What is does is hides Row #1 until Row 41 is reached. When 41 is reached Row 1 appears. Unfortunately (for me) I need to alter this code and was wondering if anyone could tell me if it is possible. First the
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Rows(1).Hidden (Target.Row < 41) Then
Application.ScreenUpdating = False
Rows(1).Hidden = Target.Row < 41
Application.ScreenUpdating = True
What I would now like to happen is that Row 1 stays hidden UNTIL Row 16 is OFF Screen. Can that be done? What has happened is that my row 16 contains the heads for my input table. I have now had to set the rows to resize to accommodate data input. If no rows are resized then Row 16 stays visible until I reach row 40. When row 41 is reached then Row 1 appears which contains my headers as well. I hope this makes sense to someone...
SO, IF I can get a code that would keep row one hidden until Row 16 is off screen then it will not matter what rows expand.
Hide Unhide Row With CheckBox ..
I want to create a check box which will hide/unhide a given row depending on if selected or not.
I can create a macro to hide or unhide the row, but I can't figure out how to do both depending on whether the check box is selected or not.
Hide And Unhide A Column From A Checkbox
I want the option of printing my form with and without a certain column. I need to hide a column when CheckBox.value=True and unhide a column when CheckBox.value=False
I keep getting errors:
Block If without End IF
End If without Block If
Else without If, when I use an else for the second If statement
Private Sub CheckBox1_Click()
Dim DescriptionCell As Range
Set DescriptionCell = ActiveSheet.Range("B:B")
If CheckBox1.Value = True Then
.EntireColumn.Hidden = True
If CheckBox1.Value = False Then
.EntireColumn.Hidden = False
Hide/unhide Autoshape Object
I have drawn an arrow on a worksheet using the Autoshape menu on the Drawing Toolbar. Now I'd like to write some code that will hide this arrow (make it not visible). Later on in my code the arrow should become visible again. The idea is that users will not see this arrow untill certain actions have been performed.
How To Hide/unhide Rows Based On Cell Value
I'm using column A as a reference. My data starts in cell A3.
Whenever theres a zero in any cell in column A I would like that row to hide.
If the row was hidden and the value changes to any number greater than zero I need the row to unhide.
I can record two macros for the hide/unhide part of this but I dont understand how to run them based on cell value.
Hide/Unhide Columns Based On Cell Value
I've attached one speadsheet :- "VBA.xls". The following conditions are to be done:-
1. When D2=1, Column "F","G" are visible & Column "I","J","L","M" are hidden.
2. When D2=2, Column "I","J" are visible & Column "F","G","L","M" are hidden.
3. When D2=3, Column "L","M" are visible & column "F","G","I","J" are hidden.
Hide/Unhide Sheet When A Certain Cell Is Changed
I am trying to create some code that will work each time a cell is updated. For simplicity, when cell A1 on Sheet 1 is greater than or equal to 25,000, I want Sheet 2 and 3 to be shown (they will already have been hidden). When A1 on Sheet 1 changes to less that 25,000, I want only Sheet 4 and 5 to be shown. I want to be able to change the number back and forth in A1 so only Sheets 2 and 3 show when A1 is >= 25,000, and only Sheets 3 and 4 to be shown then A1 < 25,000. I can't seem to find how to hide sheets based on a cell changing. I can only find how to permanently hide sheets.
Hide/Unhide With Auto-changing Object
I have an Excel schedule with subtotals and between 1 and 50 rows under each subtotals. The subtotals contain totals of the rows below them. Is it possible to link code to an object (say a 'down arrow' next to each subtotal) and when it is hit, it unhides the rows beneath the subtotals? At the same time the 'down arrow' changes to an 'up arrow' and when that it hit it re-hides the rows.
There is also the possibility that the user will unhide rows and move down the spreadsheet unhiding/rehiding other areas before coming back and wanting to re-hide the first area he revealed. When the spreadsheet is closed, all the arrows should revert to 'down arrows' and all the non-subtotals' rows should be hidden.
Hide/Unhide Rows From Name In List Box
I have a list box on a worksheet that contains the following names: field, vessel, pipe, structural. I would like to be able to unhide certain rows based
on the name shown in the list box. For example, if I choose field it would
unhide rows 3 thru 12, if i choose vessel it would unhide rows 13 thru 20, so on so forth. If nothing is choosen in the list box then rows stays hidden.
Hide & Unhide Columns With Password
I have a range of columns ( J:AH) which i have to hide from the user interface.In order to unhide, the editor must enter a password. He/ She can only enter the password thrice. After 3 times, if it is still wrong, the whole workbook must be closed. Is this possible? If not, I'll settle for something less complicated.
Hide/Unhide Shapes Along With Rows/cells
I have an workbook with many sheets. each sheet contains loads of checkboxes, dropdowns, option buttons, groupboxes ... and they are created using a macro when the workbook is opened. all this works fine. Now I've grouped different rows based on their level of importance. Here's the glitch. whenever I select a group level, the rows get hidden, but not the shapes & objects present on them. Instead, they just jump to the row above or below and overlap the other shapes.
Toggle Button To Hide/unhide Rows
I have a command button that hides rows based on zero value in Col B then a 2nd btn to unhide those rows. The challenge is that as I want this code for several workbooks that will have data of varying row lengths & not all the zero values will be in the exact same place for each workbook or each month. Having done a little research on the Board (& googled) I have not found an answer to my challenge. So I am sure someone will point me in the right direction -
Private Sub CommandButton1_Click()
BeginRow = 3
EndRow = ActiveSheet.UsedRange.Rows.Count
ChkCol = 2
Range("B3:B" & EndRow).EntireRow.Hidden = True
For RowCnt = BeginRow To EndRow
If Cells(RowCnt, ChkCol).Value 0 Then
Cells(RowCnt, ChkCol).EntireRow.Hidden = False
Hide/Unhide Rows Based On 1 Cell
I'm trying to find a way to hide rows based on a condition in one cell.
Basically I have a pull-down list with Yes or No in it. I want to hide rows 52 through 57, if No is selected, and make the rows reappear if Yes is selected.