Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    Excel


Advertisements:










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.


View Complete Thread with Replies

Sponsored Links:

Related Forum Messages:
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.

View Replies!   View Related
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.


View Replies!   View Related
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.

View Replies!   View Related
Unhide/Hide Sheet Based On Value In Cell
I have a sheet called "Summary", there is a cell, AR8, of that sheet that is linked to other sheets. If AR8 of "Summary" has a value other than 0 I would like it to unhide. But if the value goes back to 0 again I would like it to hide itself again.

View Replies!   View Related
Hide & Unhide Rows Based On Cell Value
I realize there are many Hide/Unhide requests. Although, believe me when I state that I have checked into the matter, and tried to solve it on my own.

If any of you fine forum dwellers could point me in the right direction, I would greatly appreciate it.
I am trying to achieve the following:

Two Macros:
#1:
To hide rows(entire spreadsheet) based on cell value in Column C (value is '0')
Here is a code (from this forum) that seems to be the least complex/confusing. I don't understand the "AC2" value. Would it not be A2? Or does it signify a certain range?


Sub HideRows()
With Sheets("Store Snapshot")
If Range("AC2").Value = 2 Then Rows("13:15").EntireRow.Hidden = True
If Range("AC2").Value = 1 Then Rows("13:15").EntireRow.Hidden = False
End With
End Sub

#2:
To UNHIDE the rows that Macro #1 Hid.

View Replies!   View Related
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


View Replies!   View Related
Hide & Unhide Columns Based On A Cell Value
worksheet I am working on at the moment, basically if row 5 has a 0 displayed I want that column to hide, but if row 5 has text of any value displayed I want it to unhide, the range is E5 to BA5 across.

I have draft VBA code as follows:

View Replies!   View Related
Macro To Hide/unhide Rows Based On Cell Value
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 Replies!   View Related
Hide/unhide Number Of Columns Based On Cell Value
If I have a value in A1 and run a macro [button], I want to have it hide a number of rows.

Each number is one column.

eg if A1 = 2 then B:C are visible, D:IV Hidden
if A1 = 3 then B:D are visible, E:IV Hidden
if A1 = 4 then B:E are visible, F:IV Hidden

up to 200 columns.

I tried it as a select case, but it is limited to just 22 cases which obviously not enough.

View Replies!   View Related
Automatically Hide/Unhide Columns Based On Cell Text
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 13 Then Exit Sub
If InStr(Target.Value, "Other (specify in next column)") Then
Columns("N").Hidden = False
ElseIf WorksheetFunction. CountIf(Columns("M"), "Other (specify in next column)") = 0 Then
Columns("N").Hidden = True
End If
End Sub

but I have a lot of columns that I need to perform as above and I have put the code together as below

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next

If Target.Column = 13 And InStr(Target.Value, "Other (specify in next column)") Then
Columns("N").Hidden = False
ElseIf WorksheetFunction.CountIf(Columns("M"), "Other (specify in next column)") = 0 Then.................

Using the above code, when I selected more than one cell anywhere in my workbook and pressed delete I was bugging out with a runtime error 13 message. You can see from the above code that I inserted "On Error Resume Next" - this got rid of the runtime error 13 message, but now when I select more than one cell and press delete, hidden columns are incorrectly revealed in my worksheet. how I can extend the working code at the top of this posting so that it works for a number of different columns in my Worksheet i.e. without the runtime error 13 occurring and without columns being incorrectly revealed.

View Replies!   View Related
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 Replies!   View Related
Hide Sheets, Then Need Password To Unhide
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 Replies!   View Related
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.

View Replies!   View Related
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.

View Replies!   View Related
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) ?


View Replies!   View Related
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.

View Replies!   View Related
Hide Column Based On Another Sheets Cell Value
There are 2 sheets in the excel worksheet. in one sheet there would be drop downs
the 2nd sheet will have 4 columns A,B,C,D) Depending on the selection in sheet1. If a1 = Y then columnA in the 2nd sheet should be hidden. If a1 = N then columnB in the 2nd sheet should be hidden. how can this be done..The key is the columns to be hidden are on a different sheet and that is where I am having the difficulty.

View Replies!   View Related
Hide/unhide Button Based On Data Validation
I am creating a disclaimer in one of my sheets.

Cell I15 contains 'Are you a resident for tax purposes?' Cell N15 has the data validation list of Yes or No.

Then I have a button below which covers rows18, 19 and 20.

Is there a way I can have this button hidden until Cell N15 = Yes?

View Replies!   View Related
Macro To Hide/unhide Rows Based On A Returned Value
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 Replies!   View Related
Hide & Unhide Rows Based On Condition
Attached is a portion of the file I will be working with, just to illustrate what I would like to do.

I have a macro that copies and pastes data - it uses these Tasks as templates. The macro copies these rows for each task that is required under the headings (Task #1 - Task#?). What I would like is to hide the rows under Station Maintenance Task #0 and it's 6 rows that are associated with it, as well as Task #0 under CMS and the 12 rows associated with it. In order for the macro I already have work properly is that these would be unhidden for the macro to run and then re-hidden afterward.

I searched on the forum for this topic, and found hide/unhide where you would put in the beginning row and ending row - however since there will be numberous rows added in, this type of defining rows would not work.

View Replies!   View Related
Unhide/hide Multiple Columns Based On Date
I need to show hidden columns based on the date I entered. For example, if I entered "1/1/1990" on a1 as the starting date and "4/30/1990" on b1 as the ending date. I want Excel to show the columns that are covered by the date, thus it shows Jan, Feb, March and April. How do I do that? Here's an example attachment. In here Sheet 1 is the starting point, the highlighted cells is where I enter the date. the Result sheet shows what I want Excel to show me when I have a date entered.

View Replies!   View Related
VBA Code To Hide Unhide A Number Of Rows
I am trying to create an input sheet with a specified number of rows. My plan was to hide all the empty rows in the table and ask how many rows were required in the table. A button would then be pressed to unhide all the relevant rows thus giving a table of the correct size. My best effort so far looks something like this:

Sub UnhideRows()
Dim i As Integer
Dim myRow As Integer
myRow = Range("A1") + 2
Application. ScreenUpdating = False
For i = Selection.Rows.Count To 1 Step -1
If WorksheetFunction. Sum(Selection.Rows(i)) > myRow Then
Selection.Rows(i).EntireRow.Hidden = True
End If
Next i
Application.ScreenUpdating = True
End Sub

the cell to give the size of the table is A1 and the table starts in A3. The first cell in each row has an index number (=row() - 2), all other cells would be empty to begin with. My attempt did not work.

View Replies!   View Related
Hide/unhide Columns Based On Dropdown List Answer
I am a newbie to Excel. I really appreciate if someone could help me here and this is very urgent as I have a project going on.

I have a cell say B3, which is a dropdown list I created using Data Validation. It only lists down Yes or No.

What I need is when you click on Yes in B3, i need columns C to I to unhide. If answer is No, column C to I should be hidden. By default, the columns will be hidden.

Can you please help? I tried many times but not successful. Appreciate if someone will be able to walk me through details. If you want to see the sample spreadsheet, I can sent it to you by email. I tried Data> Outline group but they are not happy with this.

View Replies!   View Related
VBA Script To Hide And Unhide Specific Columns Within A Worksheet
Need the VBA script to hide/unhide specific columns within a worksheet.

View Replies!   View Related
Hide/Show Sheets Based On Cell Values & Validate Entry
excal VBA programming.I have attached the file name "help" for your easy explanation purpose.

1. Is it possible to hide sheet nos. 1,2,3,4 & unhide the sheet as wished by me by puting the value (1or 2 or 3 or 4) in B3 cell.

2.There are per day production rate in E18 to E22 cell. Now whenever I will give value in H18 or H19 or H20 or H21 or H22, it will check whether the value is same with the respective E 18 or E19 or E20 or E21 or E22 cell. If both the values are not equal then give a message box "WARNING!!! YOUR VALUE IS NOT SAME". Can it be possible by creating VBA programming.

View Replies!   View Related
Automatically Name Sheets Based On Range & Unhide Them
I found this very nice code on this site that changes the names of my sheets based on the first sheet, works great. What I want though is to have the sheets be unhidden when their name changes from the generic name "sheet1" to whatever else we call it. Can this code be manipulated to do that? I want the sheets whose names do not change to stay hidden, the others to unhide.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim lCount As Long

If Target.Cells.Count > 1 Then Exit Sub

On Error Resume Next
If Not Intersect(Target, Range("A2:A11")) Is Nothing Then
For lCount = 2 To 11
If Sheets(lCount).Name <> Me.Name Then
Sheets(lCount).Name = Me.Cells(lCount, "A")
End If
Next lCount
End If

View Replies!   View Related
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.

View Replies!   View Related
VBA To Hide Rows Based On Cell Content
If I have data in range A1:Z99 I want to write a macro to hide all the rows that have a zero in the A column.

View Replies!   View Related
Hide Or Unhide Worksheets From Cell Values In Sheet 1
I want to set up a few workbooks with 11 sheets. Sheet 1 will in effect be an index, with True or False values in say cells D5:D14. The True/False values will be generated by form control check boxes in some of the workbooks and by IF(TODAY()>xxxx formulas or similar in other workbooks.

I then want to be able to hide or unhide sheets 2-11 based on the True/False values in cells D5:D14 in sheet 1.

View Replies!   View Related
Hide/Unhide Columns Depending On A Specific Cell Value
I am looking or a code, (Or formula if one exists) to hide or un-hide columns depending on what is entered into a cell. I have attached an example to help clarify what i mean. Basically i want:

If A2 is empty, hide columns C, D, E & F
If A2 = Apple, Unhide columns C & D, but keep E & F hidden
If A2 then becomes Banana, rehide C & D and unhide D&F

View Replies!   View Related
Hide Sheets Based On Criteria
i would like to hide different sheets at one shut. i tried this, but did not work:

Dim sh As sheet
For Each sh(1 3 5 7 9).Visible =xlSheetVeryHidden

View Replies!   View Related
Hide Sheets Based On User Using VB In 2007
I see this forum and the example works perfectly in Excel 2003.. But we are using Excel 2007 now and it doesn't appear to be recognizing the user and is always refering to the "else" in the vb codes. Does this code for not work in 2007? Oh and I am refering to Post #10 on there with the attachment that will hide different sheets depending on which user is getting on the file.

View Replies!   View Related
Hide Sheets Based On Data In Cells
I want to hide a sheet in an excel workbook based on a Yes or No answer in the first sheet in the file. Is there an easy way to do this?

The above operation will be repeated up to 10 times, but the decision to hide or not hide each sheet will depend on only one answer.

In some cases I want to hide several sheets based on one answer.

The overall objective is to have a flexibly sized workbook suitable for a range of users who will not be faced with sheets which are not relevant for their individual circumstances.

View Replies!   View Related
Vba To Enable Macros And Hide Certain Sheets
1) Force user to enable macros.
2) keep three worksheets very hidden all the time.
3) passwrd protect vba

I have been using this code to force user to enable macros but this unhides all sheets other than "macros not enabled" sheet. I need three worksheets to remain very hidden all the time. How do I do this.

Option Explicit
Dim ws As Worksheet
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Worksheets("Macros Not Enabled").Visible = True
For Each ws In Worksheets
If ws.Name "Macros Not Enabled" Then ws.Visible = xlVeryHidden
Next
End Sub
Private Sub Workbook_Open()
For Each ws In Worksheets
If ws.Name "Macros Not Enabled" Then ws.Visible = True
Next
Worksheets("Macros Not Enabled").Visible = xlVeryHidden
End Sub

View Replies!   View Related
Hide & Show Sheets Based On Names
I made one file with 13 sheets.

sheet1 tab name is : MAIN
and other sheet tab name like following
2. xyz-Sales
3. xyz-Rev
4. xyz-SSN
5. xyz-ddn
6. abc-Sales
7. abc-Rev
8. abc-ddn
9. abc-ssn
10. ddd-sales
11. ddd-Rev
12. ddd-ssn
13. ddd-ddn

In Main sheet There are 3 buttons

1 . XYZ
2. abc
3. ddd

when user press on xyz button then only xyz sheets (like sheet 2 to 5) are shows to user and other sheets are very hide

if user press abc button then only abc sheets (like sheet 6 to 9) are shows to user and other sheets are very hide

i don't want to use

Sheet2.Visible = xlSheetVeryHidden

i want to use finde xyz sheet tab name and shows and other are hide.

View Replies!   View Related
Hide Rows Based On Condition - Multiple Sheets
I want by using some code I've seen on this forum or using the macro writer and then tweaking the code. So with that said, I've written the attached code but I know there is probably an easier way to write it. It cycles through about 12 sheets using the same below code, but I didn't list that code.

Sub Hide_Rows()
Dim i As Integer
For i = 3 To 418
Sheets("AFA - UMBI").Select
If ActiveSheet. Range("b" & i).Value = "2008-2" Then
Rows(i & ":" & i).EntireRow.Hidden = True
ElseIf ActiveSheet.Range("b" & i).Value = "2008-3" Then
Rows(i & ":" & i).EntireRow.Hidden = True
ElseIf ActiveSheet.Range("b" & i).Value = "2008-4" Then
Rows(i & ":" & i).EntireRow.Hidden = True
ElseIf ActiveSheet.Range("b" & i).Value = "2009-1" Then
Rows(i & ":" & i).EntireRow.Hidden = True
ElseIf ActiveSheet.Range("b" & i).Value = "2009-2" Then
Rows(i & ":" & i).EntireRow.Hidden = True
ElseIf ActiveSheet.Range("b" & i).Value = "2009-3" Then
Rows(i & ":" & i).EntireRow.Hidden = True
ElseIf ActiveSheet.Range("b" & i).Value = "2009-4" Then
Rows(i & ":" & i).EntireRow.Hidden = True
End If
Next i
End Sub

View Replies!   View Related
Hide Sheets Based On Sheet Tab Color
I am making a new stock card system to keep track of stock at work for a new storeman working in my department. What I want to be able to do it hide/show sheets bases on their colour, the reason I want to do this is because I want the new storeman to be able colour code products and only show certain ones at any one time to stop him from having to sort through loads of sheets, but also for him to be able to add extra sheets and asign them a colour and not need me to modify code and put it into category X So in summary, I want code that will hide all sheets that colour is red or blue etc and not need updating when new sheets are added.

View Replies!   View Related
Hide Button Or Textbox Based On Caption Across Multiple Sheets
I have a workbook with 31 sheets. Each sheet has 15 textbox button that call macros.
I would like to hide 1 textbox on all sheets until another macro is called. I know how to get the textbox names to be able to hide them, But because these text boxes were copied most of the are the same name, however on severl sheets they are different names "Textbox 4 on most sheets but it could be textbox 34 on others. The ones I want to hide all have the same text label. Is it possible to get VBA to return the label text.

View Replies!   View Related
Hide/Unhide Toggle
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

Sub HideColumns()

Range("G:G,I:I,AB:AV").Activate
Selection.EntireColumn.Hidden = True

End Sub


View Replies!   View Related
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.

View Replies!   View Related
Hide/Unhide Columns
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!).

View Replies!   View Related
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()
ComboBox1.AddItem ("YES")
ComboBox1.AddItem ("NO")
End Sub
Private Sub ComboBox1_Change()

If ActiveCell.Activate = E4 Then
ComboBox1.Activate
End If
End Sub

View Replies!   View Related
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).

View Replies!   View Related
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: ....

View Replies!   View Related
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.

View Replies!   View Related
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.

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 Replies!   View Related
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.


View Replies!   View Related
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).

Sub CheckBox1_Click()
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
End If
Next MyCell
End Sub

View Replies!   View Related
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))
End Function
And a formula in a cell
=IsVisible(A1)

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.

View Replies!   View Related
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.

View Replies!   View Related
Copyright 2005-08 www.BigResource.com, All rights reserved