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?

ADVERTISEMENT

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 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 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

Summing Variable Values Across Variable Sheets In Multiple Columns

Jun 27, 2014

I need a macro that will create a sheet at the end of the workbook.

Sum data from a variable amount of sheets and display that data on the created sheet.

Here is a step by step:

Starting on sheet 5.

Column D has a variable amount of part numbers in it. These part numbers would be different between the ascending sheets.

Column T, U, V has an inputed number in it that would need added up across all duplicate part numbers in all the sheets.

(Note: The data would also need started on row 4. Everything above row 4 is headers)

Here is a small example:

D E T U V
13019090W Part A1
68705500 Part B1
64202900 Part C-11
59634600 Part D1
26005300W Part E1

I need the macro to start with sheet #5(starting on row 4). Check to see if there is data in column T, U or V. If there is, to create a new sheet at the end. And copy the entire line into that sheet (starting on row 4).

After that, to check every sheet after (excluding the newly created one, starting on row 4) for data in Column T, U and V. And then check for duplicates in Column D on the newly created sheet. If there is a duplicate to add/subtract that number in Column T, U and V to the SUM in column T, U and V in the newly created sheet. If there is no duplicate, to copy the entire line to the new sheet.

So that when finished. On the new sheet, you have the SUM of T, U and V for everything that has data in T, U or V for all of the previous sheets, plus the entire line of the first instance (excluding the first 4 sheets).

View 2 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

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

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

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

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

Cannot Unhide Columns In A Worksheet

Feb 27, 2009

The workbook contains 5 worksheets, 4 of which will unhidden just fine; the remaining worksheet ("AnnualBudget" refuses to unhide columns AD to IV. Of course, this is the sheet that contains data in those hidden cells and I cannot get to the data. The sheet is unprotected and all freeze panes removed.

I have copied this sheet to a new sheet in the same workbook:

If I copy only formulas and number formats, the new sheet has all columns unhidden. If I copy formats, I have the same issue with the new worksheet. It’s like these columns are forever locked from view.

FYI I am using Excel 2007 with a 2003 compatability worksheet

A copy of the workbook is attached. Any idea what’s happening?

View 6 Replies View Related

Unhide Worksheet Menu Bar

Apr 30, 2009

i was trying to hide my worksheet menu bar, i was searching the web & i got this macro

View 3 Replies View Related

Auto Unhide Worksheet

Mar 7, 2007

What I am trying to do is keep a few worksheets hidden until something is entered onto that sheet using a vlookup formula.

My Setup is that we have a daily schedule that is on "sheet 1" when we enter certain items on that sheet, vlookup allows them to appear on "sheet 4". now when an Items appears on "Sheet 4" that is when we would like the sheet to become "unhidden".

Using MVB I found - Sheets("Sheet4").Visible = True

I tried attaching an IF;then statement to it referencing cell (A5) but either it doens't work like that or the code was wrong.

View 10 Replies View Related

Hide And Unhide Worksheet

Mar 30, 2004

I put the correct cell in place $K$23, now I want to hide a worksheet called "DutyCode" when info!$k$23 is blank or has "xx" or "XX" in the cell, and then unhide "DutyCode" when the number 27 is inputed in info!$k$23

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address "$k$23" Then Exit Sub
If Target.Value = 1234 Then
Worksheets("Sheet2").Visible = True
Else
Worksheets("Sheet2").Visible = False
End If
End Sub

View 5 Replies View Related

Unhide Worksheet With Same Label Name

Jul 21, 2007

I have a workbook with quite a few worksheets, along with a summary page. The summary page has a list of names of all the worksheets in the workbook. I am still learning to use Macro. So far, I have created buttons to hide the individual worksheets (each component is on a worksheet by itself). But I can't figure out how to unhide a specific worksheet from a group of worksheets without creating just as many macros calling specific worksheet names. I don't really want to create 100s of little macros. I am sure there is a better way out there.

Here is a concept of the workbook:

On the summary page, the user supposed to click on a label ("T1000" for example), and this will unhide a worksheet with the same name (T1000). There are hundreds of these worksheets, how does it find

This is my idea of how the function supposed to work.

First determine the label text as a string.
Then locate the worksheet with the name equals to the string
then set visibility = true.

View 9 Replies View Related

UnHide WorkSheet Based On Cell Value

Mar 17, 2014

My goal when the value in cell K14 is >= 149 then UnHide the worksheet called / labled "Operational"

I have written the statement below: I am not getting a debug error nor is my statement doing what I expect i am not sure what is wrong.

Sub test1 ()
If Range("I14").Value >=149 Then
Workbook.Sheets("Operational").Visible = True
Else
Workbook.Sheets("Summary").Visible = True
End If
End Sub

I also have this statement executing when the workbook opens:

Private Sub Workbook_Open()
ThisWorkbook.Sheets("Operational").Visible = False
ThisWorkbook.Sheets("Summary").Visible = False
End Sub

View 4 Replies View Related

Worksheet CheckBox (Hide Unhide)

May 22, 2009

I am having difficulties with this one. I have a button on my worksheet (sheet1) that runs a macro.

View 4 Replies View Related

Hide / Unhide Worksheet - Code Is Not Working?

Aug 4, 2014

Within a worksheet "Page_2" I would like VBA to perform following calculation: IF(COUNTIF(R:R;1);1;2)

Basically, look into column R of worksheet "Page_2" and look if there is in the column at least one number with the value 1, if there isn't show me value 2.

Depending on the value coming out of this function, VBA needs to hide worksheet "Page_3" if the value is 2 and unhide worksheet "Page_3" if the value is 1.

Here is the code which I'm using and that is not working.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Application.WorksheetFunction.CountIf(Range("R1:R5000"), 1) = 1 Then
Worksheets("Page_3").Visible = True

[Code]...

View 2 Replies View Related

Code To Unhide A Worksheet Link To Value In Cell

Feb 5, 2012

I have a workbook with daily sales information for each week in a period (4,5 or 6 weeks) depending on the fiscal year calendar. I have a dashboard to select the weekending date in cell D7. I used mscal 7.exe to embed the calendar. I need the date to remain the same, but want the calendar to show the current date. Cell H5 shows the current period;i.e. 1,2,3,4 etc. Cell H7 shows the week number of the period (1,2,3,4,5 and 6 for December).

I need vba code to showthe appropriate week's daily sales worksheet based on the info in cell H7 on the dashboard. The week number on the dashboard page is obtained fromthe calendar worksheet with a vlookup formula. These sheets are named "week 1, week 2, week 3, week 4, week 5 and week 6". I need to assign this code to a shape with the caption Create New Week.

View 1 Replies View Related

PASSWORD/Code To Unhide Rows And Worksheet Tab

Jun 15, 2007

I have a protected sheet so specific people can only see certain things. I would like code to Unhide a tab called "Estimating" and unhide Rows 2 through 9 and rows 24, 26 and 27.

View 9 Replies View Related

Variable Sheets, Variable Input

Mar 23, 2009

I run a machine that scans parts and is able to output the scans into Excel. Each part scanned creates a new sheet, and the number of sheets is variable as the number of parts scanned depends on the size of the lot. Also, since each part scanned is going to have a different number of features, the information being output on each sheet will be variable as well.

I am trying to figure out how to write a macro that will find the first feature, find the values for that feature I am looking for, output a max and min into a final sheet, and repeat for each feature, and for each sheet. I hope this is clear.

View 3 Replies View Related

Macro To Hide / Unhide Worksheet Based On Cell Value

Jul 18, 2010

I would like to create a macro/vb code that will unhide a worksheet if cell value is greater than zero, or hide the worksheet if the cell value is zero.

I'm not too familiar with VB code and was wondering if this is possible? I have my doubts, since it means that the code must constantly be running in an endless loop, checking to see if the value has changed?

View 9 Replies View Related

VBA Script To Hide And Unhide Specific Columns Within A Worksheet

Oct 24, 2006

Need the VBA script to hide/unhide specific columns within a worksheet.

View 9 Replies View Related

Excel 2010 :: Cannot Unhide Columns By Selecting Entire Worksheet

May 29, 2012

I have Columns A to C which are hidden. I have tried to unhide these bey selecting the entire worksheet but to no avail. I am using Office 2010.

View 4 Replies View Related

ADVERTISEMENT