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
ADVERTISEMENT
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?
View 6 Replies
View Related
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
Sep 24, 2012
I have a TextBox and a ComboBox on a worksheet, is it possible to hide them with option buttons?. So only one box shows at a time.
Option Button1 show TextBox
Option Button2 show ComboBox
View 2 Replies
View Related
Feb 1, 2013
I have a worksheet that allows you to choose 1-20... depending on the selection it should unhide specific rows but I keep getting errors...
Code that will allow you to unhide other worksheets in the same book????
View 1 Replies
View Related
Feb 28, 2007
i have a combobox and have A, B, C, as options in the combobox
then I have a table from row 10 to row 50
if A is selected from the combobox, I want excel to show only rows 10 to 20 and hide rows 20 to 50
if b is selected from the combobox, I want excel to show only rows 20 to 30 and hide rows 10 to 20 & 30 to 40
if c is selected from the combobox, I want excel to show only rows 30 to 40 and hide rows 10 to 30 & 40 to 50
in other words, i want all rows 10 to 50 hidden at all times, until a selection A, B, C is made in which i want thoes related rows to be shows and all other rows between rows 10 to 50 hidden.
View 9 Replies
View Related
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
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
Sep 27, 2011
I have 25 sheets in the workbook and a combobox on the main page, The combobox references a range of 1-25 that represents the 25 hidden pages. right now i can get the sheets to unhide one at a time based on the selection e.g. combobox option 1 will unhide sheet 1 but the sheet are representing sites in a design so i need to have the option to select multiple sites in the combobox option so for example if i select 5 then sheets 1-5 should unhide. I hope I've explained that clearly.
The other question or option would be to just duplicate sheet 1 based on the combobox selection e.g. selection 5 duplicates sheet 1 5 times.
View 9 Replies
View Related
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
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
Dec 16, 2006
I know there are a lot of hide row threads... i've searched for ages... but (unfortunately) i can't a solution that addresses my problem and I'm stuck. Please help!
I have a data between rows 10 and 1310 and I would like to use a combo box option to select and view certain rows of data only. I don't want to use auto filter because i have a separate macro using an auto filter on the same data - they won't play together.
So in short - combo box will contain values from column A. When value is selected from combo box hide all non matching rows based on column A values.
So i have added a combo box to sheet1 and populated it with the following workbook code...
Sub Workbook_Open()
With Sheets("Sheet1").ComboBox1
.AddItem "Select Product Type"
.AddItem "Apples"
.AddItem "Mangoes"
.AddItem "Oranges"
.AddItem "Clothes pegs"........
View 9 Replies
View Related
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
May 14, 2014
i want to hide and unhide headings from all sheets at once (Excel 2013)
View 8 Replies
View Related
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
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
Jul 8, 2014
I have created a UserForm that has a ComboBox and depending on the number selected I want it to show that number of Labels/TextBoxes...
So if I select "0" nothing is shown, if I select "1" one set of Labels/TextBoxes is shown, select "2" and two sets of Labels/TextBoxes are shown... but also if I have selected "2" and then select "1" I want the second set to be hidden again...
Also I know I should have renamed the Label/TexBoxes to make it easer but I was adding things and making it up as I went along...
I'm using Excel 2010 on windows 7.
Code:
Sub UnHide_NewRoutings()
If (Engineering.ComboBox2.value) = "0" Then
Engineering.Label4.Visible = False
Engineering.TextBox5.Visible = False
Engineering.Label9.Visible = False
Engineering.TextBox9.Visible = False
[Code] ..........
View 3 Replies
View Related
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
Feb 9, 2010
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 5 Replies
View Related
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
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
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
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
Nov 27, 2012
I want to set up a macro that will hide and unhide columns.
View 9 Replies
View Related
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
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
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
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
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
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
