Unhide All Sheets That Do Not Contain Certain Words In Sheet Name

Mar 11, 2009

I pulled some code off the internet to “force” users to enable macros by hiding all sheets except a “Welcome Page”. It works great except that it also unhides sheets that are meant to be hidden by when the workbook is opened.

How would I adjust the following code so any sheets/ tabs containing the words “New Project” are not automatically unhidden? Below is the code pertaining to unhiding. I did not paste the code in its entirety as it is fairly long.

Option Explicit

Const WelcomePage = "Macros"

Private Sub ShowAllSheets()
Dim ws As Worksheet

For Each ws In ThisWorkbook.Worksheets
If Not ws.Name = WelcomePage Then ws.Visible = xlSheetVisible
Next ws

Worksheets(WelcomePage).Visible = xlSheetVeryHidden
End Sub

View 9 Replies


ADVERTISEMENT

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

Oct 9, 2009

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

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

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

View 10 Replies View Related

Replace All The Times That These Words Appear In The Rest Of The Sheet With The Words In Column B

Sep 20, 2006

I have a column of words in Column A and I want to replace all the times that these words appear in the rest of the excel sheet with the words in Column B. If someone has already answered a similar problem link me to the thread because I can't find anything.

View 5 Replies View Related

Unhide Only Certain Sheets - Not All

Nov 17, 2012

I am trying to use the macro I downloaded from OZ Grid called "Enable Macros" to check if macros are enabled. Its works beautifully, except for the fact that it will unhide all sheets if macros are enabled. I would like to only unhide certain sheets. The sheet names are:

Sheet11(Weight & Balance)
Sheet5(Nav Log)
Sheet2(Database)

I believe this is the part of the code that must be modified, but I'm not sure how:

Code:
Public bIsClosing As Boolean
Dim wsSheet As Worksheet

Sub HideAll()

Application.ScreenUpdating = False
For Each wsSheet In ThisWorkbook.Worksheets

[Code] ..........

Also, I am planning on locking this workbook. If this macro can still run if the workbook is locked?

Link to where the macro can be downloaded:

View 8 Replies View Related

Unhide All Sheets

Dec 5, 2006

I receive a lot of excel files and would like a macro that I can run to unhide any and all hidden sheets. The sheets will have different tab names. I looked on this forum and could not find the answer to this question, usually I can.

View 5 Replies View Related

Unable To Unhide Sheets

Oct 21, 2009

I have a workbook with several sheets that I created some time ago. I have hyperlinks on the main sheet which point to other sheets in the same workbook which can be viewed when using the hyperlink, however the linked sheets are hidden. Somehow in this workbook I removed both the horizontal scroll bar and the lsiting of sheet names. I can not figure out how to get them back so I can unhide and or add new sheets.

View 4 Replies View Related

VBA To UnHide / Hide Sheets

Feb 20, 2014

I have a workbook with about 20 worksheets in it.

6 are visible
3 are hidden

the remaining are very hidden

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

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

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

View 6 Replies View Related

Password To Unhide Sheets

Sep 25, 2009

I wanted to know if there is a way to lock some sheets in a workbook to prevent anyone to view them. I have a workbook with sheets that have a database, it is shown the frontsheet, and when a person selects an option it will appear a sheet with the data selected initialy. I wanted the other sheet to be hidden and locked so the one goes to the unhide menu and selects a sheet to unhide a msgbox appears to ask a pass code.

View 3 Replies View Related

Require Password To Unhide Certain Sheets?

Mar 14, 2014

I have a file with the following sheets: P1, P2, P3, Inventory. This file is sent to different people (users Ab, Ad, An) and P1-P3 sheets are set as "VeryHidden" because we want to avoid for example that user "Ab" can see or unhide certain sheets.

I'm wondering if it's possible to request a password when opening the document then unhide certain sheets according with that password, there will be 3-6 users.

View 2 Replies View Related

Different Password To Unhide The Hidden Sheets.

Nov 22, 2008

I have 5 sheets, 3 of them are hidden, how can i put a 3 different password to unhide the hidden sheets.

View 6 Replies View Related

Hide/ Unhide Sheets With Macro

Nov 6, 2008

I want to run 2 different macros:

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

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

View 6 Replies View Related

Unhide Multiple Sheets Using Drop Down?

Oct 7, 2011

I am making a workbook that populates most of the data off of a data sheet, but i need it to hide the 10 other sheets until they are needed. I was hopint there would be a way to hide all but the data sheet, then use either a drop down box or if necessary a checklist (NOT my first choice, but i'll do it if i have to) to select the sheets that the tech will need to print or e-mail. Is there a way to do this:

View 1 Replies View Related

Unhide Rows In Multiple Sheets?

Oct 12, 2011

Is it possible to unhide a row in one sheet and the same line can be un hidden automatically ini 2 other sheets within the same workbook

View 1 Replies View Related

Unhide Sheets Using Dropdown List

Nov 26, 2011

How do I unhide sheets using a drop down list? For example I have two sheets one named East and the other West. I want to choose east from a drop down and have it unhide the sheet.

View 9 Replies View Related

Macro To Unhide Then Hide Sheets

Sep 29, 2008

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

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

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

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

View 9 Replies View Related

Hide Sheets, Then Need Password To Unhide

Oct 6, 2008

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

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

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

View 9 Replies View Related

Unhide Specified Multiple Sheets In A Workbook

Jun 27, 2006

I would like to unhide specified multiple sheets in a workbook at one go. What I'm doing now is something like this:

Sheets("Source 1").Visible = True
Sheets("Source 2").Visible = True
Sheets("Source 3").Visible = True
Sheets("Source 4").Visible = True

I managed to get the array function when I want to hide the sheets:

Sheets(Array("Source 1", "Source 2", "Source 3", "Source 4")).Select
ActiveWindow.SelectedSheets.Visible = False

but I can't do this when I want to unhide them. I do not want to unhide all sheets in the workbook, only those I specify in the code. How can I use the something like the array code to unhide the sheets.

View 9 Replies View Related

Excel Unhide Sheets When Check Is Selected?

Sep 19, 2013

I am trying to create a spreadsheet which will open with sheet1 showing 'Terms and conditions' with 2 check box's. Once the check box's are checked I then want subsequence sheets to unhide. E.g check box 1 ticked - will unhide sheet 2 - (containing the report) check box 2 ticked - will unhide sheet 3 - (containing the data)

Basically, by the use of check box's I am trying to get the user to read and agree to terms/disclaimer of the use of data. Therefore can the workbook also always open with the one sheet 'terms and conditions' showing only.

View 2 Replies View Related

VBA Code - Unhide Multiple Sheets With Password?

May 18, 2011

Am trying to find the VBA code to "ask a user for the password to unhide multiple sheets/tabs"

I have got the script for unhiding all sheets which is:

Sub Sheets_Unhide()
Dim sh As Worksheet
For Each sh In Worksheets
sh.Visible = True
Next
End Sub

What i would like though is for it to prompt with a password first before the code is executed for unlocking 50 odd sheets.. I know its not 100% fool proof, but would do for what i need!

View 6 Replies View Related

VBA To Hide/Unhide Sheets Based On Cell Value

Jun 2, 2008

I have a workbook which has roughly 50 sheets. What I'm trying to do is automatically hide/unhide sheets based on the cell values in the first sheet. So in sheet1 cell A1 i would a value of FALSE which would trigger sheets1, 2, & 3 to hide, when that value changes to TRUE then those same sheets would unhide. I need to replicate that for the 10 corresponding sets of sheets, but for each grouping of sheets a different cell in sheet1 would be the trigger, cell A2 = sheets 4 - 10, cell A3 = sheets 11 - 20, etc.

View 9 Replies View Related

Automatically Name Sheets Based On Range & Unhide Them

Aug 20, 2008

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

Unhide Sheets/Worksheet With Variable CodeNames

Sep 27, 2006

I want to make a hidden sheet visible. Except that i want to use the name of the sheet (the thing that stays the same even when you change the sheet tab name). I also want to select the name based on a variable called Year which the user adds elsewhere (eg as 2006).

wf = "WF_Edin_" + Year 'So for 2006 this would read WF_Edin_2008
wf.Visible = True

The above doesnt work as wf becomes a string which i dont think i want do i?

View 7 Replies View Related

Hide/unhide Selected Range In All Sheets

Aug 10, 2007

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

View 2 Replies View Related

Unhide Sheet Based On Named Range In That Sheet

May 6, 2012

Is there any way (in vba code) to unhide the sheet of a given named range?

If would come before the following line of code whee CurReference is already defined

Application.Goto Reference:=CurReference

View 1 Replies View Related

Excel 2007 :: Hide And Unhide Sheets Using Checkboxes?

May 13, 2014

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

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

View 2 Replies View Related

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

May 14, 2014

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

View 8 Replies View Related

Hide Selected Sheets And Not Unhide Without Entering Password

Mar 18, 2014

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

View 10 Replies View Related

Macro To Unhide / Hide Sheets With Combobox Selection

Jun 18, 2007

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

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

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

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

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

View 9 Replies View Related

Macro - Replace Words In Sheets?

Oct 31, 2012

Trying to do:

Where ever "New Orleans Saints" shows up in my sheet. I want to replace it with "NO"

Where ever "Washington Redskins" shows up in my sheet. I want to replace it with "WAS"
.
.
.
This continues on for 32 different teams.

View 4 Replies View Related

Auto Duplicate Row Contents From 1 Sheet To 2nd Sheet If Column Contains Set Words

Dec 19, 2008

I want to end up with is when I type text in any row of the Master sheet, I would like the entire contents of each row to automatically be repeated on the worksheet of the same name (as per column O). For example row 5's contents from the Master sheet would duplicate into the sheet named Accommodation. In addition, should the details ever change in the Master, this would need to be reflected in the individual sheets also. My only concern is that I'd like the individual sheets to fill from the top rather than match the exact row number position from the Master, ie row 5's contents to be placed into row 2 of the Accommodation sheet seeing as it's the first occurrence, and row 6's would go into row 3 etc.

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved