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


Hide/show Multiple Sheets By Macro?

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 Complete Thread with Replies

Sponsored Links:

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

View Replies!   View Related
Hide & Show Sheets Automatically
I would like to be able to use the before save event to hide some sheets before the save then after unhide some sheets. So that the user carrys on with the sheets they had before saving but when the document is reopened the correct sheets are hidden.

This is what I have so far but unfortunately when you click close and then save changes it runs the before save code and then goes around in circles, reasking the user if they want to save changes

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim ws As Worksheet

Cancel = True
Application.EnableEvents = False
Sheets("Protected Content").Visible = True
For Each ws In Worksheets
If ws. Name <> "Protected Content" Then ws.Visible = False
Next ws
For Each ws In Worksheets
ws.Visible = True
Next ws
Sheets("Protected Content").Visible = False
Application.EnableEvents = True

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/Show Sheets Chosen In ComboBox
Sub ComboBox1_Chg()
For Each Sheet In Worksheets
If Sheet. Name <> "CoverPage" And Sheet.Name <> Sheets("CoverPage").ComboBox1 Then
Sheet.Visible = False
Else: Sheet.Visible = True
End If
Next Sheet
End Sub

It works if I step through it (F8) but the ComboBox doesn't work. It's named ComboBox1, and in the properties the ListFillRange shows all of the names in the list in the ComboBox correctly.

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
Hide & Show Rows Based On Multiple Conditional Formatting
I am trying to hide/show entire rows of a range based on the conditional formatting in the row. I want all rows with at least one overdue training cell (indicated by a red cell) displayed, and rows with no overdue training hidden. The conditional formatting formulas vary greatly, but always result in a white (unchanged), yellow, or red cell. Here is a sample picture for reference:


The CF formulas vary based mostly on two major factors: the frequency of the requirement found in Column "C" (Monthly, Quarterly, Semi-Annual, or Annual Requirement), and the personnel's arrival on site or date of departure (wheels up) found in Rows("3:4"). Each training class has two rows. The first row indicates the last time the class was completed, and the second row shows when it is due next. Both rows have to be displayed/hidden based on the second row's conditional formatting. Here is the code I am using right now: ...

View Replies!   View Related
Macro To Hide And Show All Tabs
I have looked through alot of this board and see the codes to hide all tabs old and new and code for each sheet.

My question is:
Is there a macro that can be used to do this as well. Say a button to hide all tabs and a button to show all tabs or does it only hide/show when the workbook is re-opened?

View Replies!   View Related
Macro Hide / Show Rows
I'm trying to create a macro that will hide all the rows where the value in column E is equal to zero.

I'm currently using rows 1:700, but I may add to it.

View Replies!   View Related
Hide & Show Columns Macro
I have a simple macro that I have been using to hide columns in a very large spreadsheet. Essentially, the user has access to buttons that allow him to choose between a variety of the most commonly used views. For some reason, when I add columns and adjust the code to hide/reveal these columns, I get:

"Run-time error '1004' - Unable to set the Hidden property of the range class"

with the Debugger highlighting the code for "BO:DC". This problem occurs for several of the similar buttons, including toggle buttons, that hide/reveal columns. I am aware that custom views can be created in the drop-down menu, but I wanted to keep these buttons on the sheet as a quick means of moving from view to view and toggling columns between hidden and revealed.

Private Sub CBMonographMLA_Click() ...

View Replies!   View Related
Macro To Show & Hide Comments
I have a standard laid out spreadsheet, but in column C, D and E, there are comments in each cell which are filled with a lot of text. I would like to set a macro so that whenever an 'a' is in column A, the comments for that row are shown/hidden (the 'a' being a tick symbol in Webdings). So if I place an 'a' in A4 and A23, and click a show/hide button, it causes the comments in C4, D4, E4; C23, D23 and E23 to be displayed, and when it is clicked again (or it could be a separate button), they disappear. If no row has an 'a' in the A column, then the button has no action. I have recorded a Macro of me showing and hiding comments, but of course that is for a specific row.

View Replies!   View Related
Correct Syntax To Hide Multiple Sheets
I have over 60 sheets within a workbook. There are some sheets that I want to hide or unhide depending upon the macro. I have the MR and searched in several areas but keep coming up blank with how to either select or hide these sheets.

This is from the MR:

Sheets(Array("Process", "Utilities", "CodeRef", "DataRef (3)", "DataRef (2)", "DataRef", "Dept Summary New", "Summary_Dept", Summary_ Monthly")).Select

When I try to use this in the code it errors out.

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 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 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
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
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
Run VB Macro On Multiple Sheets
Hi i have this macro below which works perfectly to clear the contents of column A. I will have multiple worksheets all named from 1,2,3,4,5 upto 50.

What i would like to do is modify this script below so it will run on the worksheets labelled above.

View Replies!   View Related
Run Same VBA Macro Code On Multiple Sheets
I am trying to run the following code on multile sheets in a workbook. so far this code is working fine for one worksheet. Can someone help me modify this so it runs on multiple sheets. There can be more then 1 worksheet in a workbook depending upon data ...

View Replies!   View Related
Run A Macro In Multiple Sheets At The Same Time
I have to run the macro in over 75 sheets every month and they are divided in few workbooks. The workbooks have 2 summary sheets and then the sheets I need to run the macro. Can you help me to run the macro in all of these workbooks running it just once?

Sub TelcoTicketsCleaning()
' TelcoTicketsCleaning Macro
' Macro recorded 6/13/2007 by EQUANT
Dim lastrow As Long
lastrow = Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

For r = lastrow To 46 Step -1 'Remove rows with DSL, ISDN and PSTN services..................

View Replies!   View Related
Macro For Printing Multiple Sheets
Print sheet 1, 3 and 7. Always print sheet 1, however only print sheet 3 and 7 if there in these sheets are values in the cells from row 8 and below.

(If that is to complicated it would be ok if the condition for printing sheet 3 and 7 is that there's a value in e.g. cell A8.)

I managed to create this script that allows me to print sheets 1, 3 and 7, however I can't seem to find out where to put the if-statement (I suppose that's how you do it?). Here's the script i created so far:

View Replies!   View Related
Creating Multiple Sheets From A Macro
I am creating a vacation calendar for all of my associates. I have 763 employees so, i want to run a macro that will react a worksheet for each of them. I will have an employee list that will create the sheets and I will have a VLOOK Up to update and pull information when we have new hires come on board.

View Replies!   View Related
Running Macro On Multiple Sheets
I would like to run the code below on selected sheets in my workbook:

Range("B9:AW38, AZ9:BE38, b3").ClearContents

I tried using this but it doesn't work:

Sub clear()
Sheets( Array("sheet A", "sheet B", "sheet C")).Select
Range("B9:AW38, AZ9:BE38, b3").ClearContents
End Sub

View Replies!   View Related
Macro - Copy Value Only From Multiple Sheets Into One Sheet
This Macro works fine to copy data from multiple sheets into one master sheet, but it is also copying the formula. How can I change this Macro so that it is copying and pasting VALUE only?

Sub Combine()
Dim J As Integer
On Error Resume Next
' work through sheets
For J = 4 To Sheets.Count ' from sheet 3 to last sheet
Sheets(J).Activate ' make the sheet active
Selection.CurrentRegion.Select ' select all cells in this sheets
' select all lines except title
Selection.Offset(1, 0).Resize(Selection.Rows.Count - 1).Select
' copy cells selected in the new sheet on last line
Selection.Copy Destination:=Sheets("Combined").Range("A65536").End(xlUp)(2)
End Sub

View Replies!   View Related
Macro: Print Ranges From Multiple Sheets
I am trying to write what I thought would be a simple macro to print out specific areas of my worksheets. I have shown the code below; the line causing the problem I have highlighted in RED. I am getting the following error message: "Select method of range class failed".

Reading other posts here. I think this may have something to do with the macro being assigned to a command button in one worksheet (AY114) and I am trying to get the macro to run on both the worksheet that the command button is in (AY114) as well as another worksheet (AY062).

Sub CommandButton1_Click()
ActiveSheet.PageSetup.PrintArea = "$A$4:$J$53"
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
ActiveWindow.SmallScroll Down:=45
ActiveSheet.PageSetup.PrintArea = "$A$56:$M$151"......................

View Replies!   View Related
Multiple Data Sheets In One Macro Model
I have: + three raw data sheets (needed as 3 lots of data with 25K records in each). + I also have a model that I want to take each record from each sheet, populates some cells in a seperate calculation sheet and then gets a result - which it adds to a total (array).Finally I have + a clever macro that shows me what % of the way through I am (i.e. displays the number of record processed out of the 75K (3*25K) total)

While I have got the model to work - there is some untidy code used where I start my clever % macro for x = 1 to 75K then repeat the same instructions for running the model for each section i.e. sheet 1 : 1 to 25000, then the same instructions for sheet 2, 1 to 25000 etc etc. and then end it I want to know if there is any clever coding that will let me go from one sheet to another when there is an empty cell struck on the active sheet?

View Replies!   View Related
Macro - Copy Multiple Sheets To Master
Have 52 weekly spreadsheets in a workbook and would like to create a macro to copy them into a master spreadsheeet in the same workbook plus, would like the master to be continually updated with any changes that are made to the individual spreadsheets.

View Replies!   View Related
Macro To Apply Subtotals To Multiple Sheets
I am trying to loop through all of my worksheets in my workbook to apply a subotal to each of the sheets. I can get it to work with applying to just one named sheet, but I cannot get the loop to work. The sheets named "data" and "PriceList" do not need the subtotal applied.

Below is the code I am using:

Sub SubTotals()
Dim LastRow As Long
Dim wsDst As Worksheet

View Replies!   View Related
Macro To Create Multiple Graphs From X Sheets
I am trying to use VB to create 50 charts each with 3 lines using data from 3 different excel spreadsheets in the same workbook. I am able to create one chart with 3 lines using data from the three spreadsheets, this is good. However I exprience problems when i try to insert a loop to create 50 charts that correspond the the rows in each spreadsheet. I am new to VB and am very inexperienced with VB. In each worksheet my data is arranged in rows. row 1 of each worksheet has the header information and rows 2 through row 50 have my corresponding data. for example A2 lists the name and F2 through T2 lists the data. I have tried a bunch of different things but nothing seems to alllow me to be able to create multiple charts which correspond to each row of data?

Sub Chart2PPT()
Dim arow As Integer
Dim acol As Integer
Dim StartPoint As Integer
Dim EndPoint As Integer
Dim rStartPoint As String
Dim rEndPoint As String
StartPoint = 2
EndPoint = 4
For arow = 2 To 5
rStartPoint = "f" & StartPoint
rEndPoint = "t" & EndPoint
ActiveChart.ChartType = xlLine 'Type of graph........................

View Replies!   View Related
Macro: Insert Multiple Sheets & Name As Cell
I found a macro code in this forum (Macro: Insert Sheet & Name As Cell Text first empty cell it should stop, but I do not know how to do that. (for example: If cells text is: A1: DOG, B1: CAT, C1: MOUSE then macro should create worksheets named DOG, CAT and MOUSE). I would save code to macro.xls file, in that file it would be also worksheet named LIST with cells names A1: DOG, B1: CAT, C1: MOUSE... in first row. But I would like to run macro on other files, so this new worksheets would be created in that new file and not in macro.xls where where macro and LIST are saved.

Sub AddSheets()
Dim strName As String
strName = Sheets(1).Range("A1")
Sheets.Add After:=Sheets(Sheets.Count)
ActiveSheet.Name = strName
End Sub

View Replies!   View Related
Slow Macro - Multiple Vlookups To External Sheets
I'm working on a macro which compares values in particular cells across different versions of a sheet. Unfortunately there are quite a lot of values, and my code is running very slowly.

I'm trying to get the data in a grid, with the file version across the top, and down the left the project name. For each file version I need to look up a specific value for every project listed.

At the moment, my macro is creating a seperate formula for each cell which does a vlookup on the project name to the specific external file.

The relevant bit of code is below

At the moment its runnig to slow by several orders of magnitude, Working on a 4 by 10 grid it takes about a minute, and I'm going to need it to be able to handle a 50 by 800 grid.

While the code is running, the CPU is not maxed out, so I'm assuming that its the calls to the file system which are taking all the time.

Application.ScreenUpdating = False

Dim ccount As Integer
Dim rcount As Integer
Dim sFilename As String
Dim sPath As String

With ActiveSheet
For ccount = 1 To Range("c1").End(xlToRight).Column - 2

sPath = Left(FileNamesList(ccount), InStr(FileNamesList(ccount), "Pipeline ~") - 1)

sFilename = Right(FileNamesList(ccount), Len(FileNamesList(ccount)) - InStr(FileNamesList(ccount), "Pipeline ~") + 1)

For rcount = 1 To Range("A65536").End(xlUp).Row - 2

by SDB.xls]Pipeline'!$A$1:$AO$300,3,0)

Cells(rcount + 2, ccount + 2) = "=VLOOKUP(""" & Cells(rcount + 2, 1) & """,'" & sPath & "[" & sFilename & "]Pipeline'!$A$1:$AO$300,3,0)"

Next rcount
Next ccount

End With

View Replies!   View Related
Lookup (macro To Search All Of Column In Multiple Sheets For A Date That Has Been Put Into An Input Box)
i want a macro to search all of column A in multiple sheets for a date that has been put into an input box.

the matching dates have data corresponding to them in that row. i want the date and data from the row to then be copied into a new sheet.

View Replies!   View Related
Show/Hide Comment With VBA
My Excel>Preferences>View>Comments is set to Comment Indicator Only.

Working from keyboard and mouse, when I click on a cell holding a comment ($B$52) , the comment appears. When I click on a different cell the comment disappears.

When I run the code
End Sub
the cell is selected, but no comment appears.

When I run
With Range("B52")
.Comment.Visible = True
End With
End Sub
the comment remains visible even after I click off of B52.
I get the same behaviour when I use Application.Goto rather than Select.

I would like to write a routine that
1) creates a comment for cell B52
2) selects B52
3) shows the comment in B52
< VB routine ends >
4) comment disappears when user clicks on different cell.

Does this require event code?

View Replies!   View Related
Hide/Show More Cells
I have two options in a Cell as Applicable and Not Applicable. This is in a Drop-down menu and what I want is that when I choose Applicable, it should bring up additional 5 or 6 cells for me to complete. And if I choose Not Applicable, the additional cells should not show up. The default would be Not Applicable.

View Replies!   View Related
Hide Userform & Show Another
how to creat a userform with a button (1) to show anouther userform with a button (2). you click the button on 1 and it shows 2. then when you click the button on 2 and it shows 1. I'm having a problem with the second part.

View Replies!   View Related
Hide Or Show Zeros
Is there a method of formatting specific cells to show zeros? Or indeed I could turn on show zeros in options and then hide the ones I don't want. Any tips / advice on how to selectively display (or hide) zeros appreciated.

View Replies!   View Related
Hide UserForm Via Top X & Show Another
I bet you're getting sick of the "X" questions, and I have done an exhaustive search but the search filter kills searching for (X) or 'X' or "X" .. but nothing I did find is what I am looking to do. I have any # of userforms that when you click the "X" in the top right side, it will hide the userform... once.. Is there a fix to the code below to make it do it every time its clicked?

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
If CloseMode <> 1 Then Cancel = 1
If CloseMode = 1 Then Cancel = 1
End Sub

Like I said, I don't want to disable it, I tried that off of one of the posts I found and thats just plain annoying, I just want it to hide the one userform and show the other. This will do it once and then you can click it all you want and it wont do anything.. see the annoying trait above.. this lasts until you exit the program.. so the X button can be hit repeatedly though out the day. The userform 15 is visible, userform 11 isn't shown, IE not behind userform 15.

View Replies!   View Related
Show Hide Rows On Click
if it is possible to hide the contents of a column that would only show once clicked on?

That way i could have a column called "Keywords - click to open" & the contents would only show once clicked on?

View Replies!   View Related
Using Conditions To Show/hide Worksheets
I'm creating an excel workbook that will allow any business or accounting student to go in and create their four year plan while giving them all of their requirements for their particular degree.

One of the features I wanted to have with this program is that it wouldn't display all the worksheets for each major, but rather the ones you choose that you are associated with. I was going to have a drop down menu on the first page that allows you to choose your majors, those being accounting, or business administration with its 5 focuses.

If I just allow all the worksheets to be shown, there would be 6 in total and I thought it might get confusing for a student who's only taking one of the 6. My hope is that on the first page, when you choose one of the options, it would automatically display the hidden worksheet that goes along with that major.

View Replies!   View Related
Hide Or Show Cells When Printing...
I have a workbook that I want to show or hide a selection of cells depending on another cell when printing it.


If cell x = A2A then display a certain cell set if anything else then exclude cell set from printing.

View Replies!   View Related
Hide / Show Columns By CheckBox
I have a userform labels role_selection with 5 checkboxes. Each checkbox with the caption labels as follow: Students, Teachers, Below Average, Customer, and Friend.

In addition, a 3 worksheets each labels as follow: Main page, General, and Final.

The General worksheet has the same name as the 5 checkboxes (Students, Teachers, Below Average, Customer, and Friend).

My question is how can i write a program to determine which checkbox is selected and compare the selected checkbox caption to the names that already existed in the general sheet. If the name exists show that name and hide the rest ...

View Replies!   View Related
Dynamically Hide/show Rows
I have a spreadsheet that has been set up with totals at row 1010. The data that the spreadsheet contains only goes up to row 159. However data will continue to be added row by row over time. At the moment I have to keep hiding and unhiding rows to check the totals. Is there a way so that the spreadsheet will automatically hide all but five rows between the last row with any data in and the totals at the bottom of the spreadsheet?

View Replies!   View Related
Hide/Show Image By Month
I have a form where users will fill out data over the course of the month. When all required data is submitted, I have an image of a check mark show up to indicate that the month's information is complete.

How can I schedule the images to reset to

image.visible = False

when the workbook is opened for the first time the following month? Auto Merged Post;I've done some code that will get this done and put it in a workbook_open module. It will look at the destination fields for data for the current month and if they are empty, make the referenced image invisible.

But I am running into an error. It is saying "Object Required" and when I hover it says "false = false"

Here is the sample of the

Private Sub Workbook_Open()
With Sheets("Checklist")
For Each c In . Range("A6", .Range("A69"))
If Month(c) = Month(Now()) And Year(c) = Year(Now()) And c.Offset(0, 3) = "" Then
Image1.Visible = False
End If
Next c
End With
End Sub

View Replies!   View Related
Hide Show Blank Rows
I have a lengthy column containing text information. Within this column are various gaps. ie: several cells with no information.


..... Row F

1...text info
2...text info
5...text info
6...text info
8...text info

I would like to create a simple macro (switched via toggle switch) whereby it hides/unhides the rows containing cells with no text information.

It should also be mentioned that this column contains various background color formatting, for both empty and text cells.

View Replies!   View Related
Show / Hide UserForms Wizard
I have created several userforms within my excel spreadsheet and i want to be able to click a command button on one form which will then hide the current form and show the next form. In previous spreadsheets i have used the following:

Private Sub CommandButton1_Click()
End Sub

However, i am having trouble with this spreadsheet as when i am typing my code after the Form1. the hide command (and show) does not appear in the little pop scroll bar that predicts what command you plan to use. (If i type it regardless then it doesnt execute)

What is even stranger is that if i write a sub routine in a module such as:

Sub Test_Form()
End Sub

And then call it:

Private Sub CommandButton1_Click()
Call Test_Form
End Sub

View Replies!   View Related
Hide/Show Cells With Checkbox
code to write down to make a checkbox hide the text in a few cells..

What I want is..

When I tick the check box I want it to show me the text in the cells (C11:D11 to be specific) and then when I untick it I want it to hide the text so all you can see is the background colour.

Here is the attatchment of what I have so far:

View Replies!   View Related
Hide & Show Shape On Worksheet
I've been trying to make use of 'Run "doit", but the macro stops when it gets to my sheets called 'RST' and 'RST Pivot'. What I'm I doing wrong?

Sub DoIt()
Application. ScreenUpdating = True
With Sheet1.Shapes("Rectangle1")
.Visible = msoTrue = (Not Sheet1.Shapes("Rectangle1").Visible)
End With
'Toggling sheets Forces Rectangle 1to show while code is running
Sheets("RST Pivot").Select
End Sub

Also, do I need to change 'With Sheet1.Shapes' to reflect the actual sheet name?

View Replies!   View Related
Hide And Show Objects In Userforms
I have a commandbutton that when clicked, it would make monthview1 visible. I can do this via the following

Private Sub CommandButton3_Click()
MonthView1.Visible = True
MonthView1.Value = Date
End Sub

However, I do not know what code to add so that when the same commandbutton is clicked, the monthview object would hide.

View Replies!   View Related
ToggleButtons For Hide And Show Rows
I have a worksheet with 4 sections of rows. Each section I call Goal 1 - Goal 4. I want to show each section and hide the other sections by clicking on a button. I have used a column, P, to denote which Goal a row belongs to by entering 1, 2, 3 or 4 accordingly. This is the code I am using.

Private Sub ToggleButton1_Click()
With ToggleButton1
.Caption = "Goal 1"
End With
Dim rCell As Range
If ToggleButton1.Value = True Then
For Each rCell In Range("P2:P99")
rCell.EntireRow.Hidden = rCell > 1
Next rCell
3) Else
Range("P2:P99").EntireRow.Hidden = False
End If
End Sub

I have three questions -

1) This code works to show Goal 1 and by changing the >1 value to <4 I can make it work for Goal 4 - but I can't work out how to show the other Goals, 2 and 3.

2) Is this code an efficient way of doing what I want?

3) How can I make the "up/down" state of the toggle button actually relate to whether or not I am showing a particular goal? That is, if I click Goal 1 and then click Goal 4, I am showing Goal 4 but both buttons stay in the "down" state. I want the Goal 1 button to automatically return to it's "up" state when I click on another button.

View Replies!   View Related
Hide & Show UserForm Controls
I need some code that will do the following in a UserForm:

If option1 is selected = textbox1 is visible, textbox2/combo1 is hidden
If option2 is selected = textbox2 is visible, textbox1/combo1 is hidden
If option3 is selected = combobox1 is visible, textbox1/textbox2 is hidden

View Replies!   View Related
Hide Or Show An Image Based On A Cell Value
I have a spreadsheet with two bitmap images inserted into it. In cell E2, I will enter a number, either a 1 or a 2. if I enter a 1, I want only the first image to be visible. If I enter a 2, I only want the second image to be visible. Is there a way to accomplish this (hopefully without the need for macros)? I've attached a spreadsheet as an example of what I'm trying to do. Also, note that I'd like the images to be stacked on top of each other so that they show up in the same place regardless of wether there's a 1 or a 2 in cell E5

View Replies!   View Related
Copyright 2005-08, All rights reserved