Hiding sheet tab names

I created an automated workbook where I need to keep the sheet tab name
hidden from the user.  I went into Tools-Options-View and unchecke
Sheet Tabs.  Then I protected the workbook and the sheet yet the use
can still go into Tools-Options-View and re-check the Sheet Tabs t
view them again.

How do I lock the user out of viewing the sheet tabs? :confused

--
Message posted from http://www.ExcelForum.com

0
8/13/2004 7:17:22 PM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
511 Views

Similar Articles

[PageSpeed] 42

You could use the "very hidden" property that prevents 
users from viewing hidden worksheets without using VBA:

Dim ws2 As Worksheet
For Each ws2 In ThisWorkbook.Worksheets
   If ws2.Name <> "Output" Then
       ws2.Visible = xlSheetVeryHidden
   End If
Next ws2

This will hide every worksheet except "Output".

HTH
Jason
Atlanta, GA

>-----Original Message-----
>I created an automated workbook where I need to keep the 
sheet tab names
>hidden from the user.  I went into Tools-Options-View 
and unchecked
>Sheet Tabs.  Then I protected the workbook and the sheet 
yet the user
>can still go into Tools-Options-View and re-check the 
Sheet Tabs to
>view them again.
>
>How do I lock the user out of viewing the sheet 
tabs? :confused:
>
>
>---
>Message posted from http://www.ExcelForum.com/
>
>.
>
0
jason.morin (561)
8/13/2004 7:37:19 PM
As I said in another section, use
Application.commandbars("worksheet menu
bar").Controls("Tools").Controls("Options...").Enabled=false
in the workbook_open event and
Applilcation.commandbars("worksheet menu
bar").Controls("Tools").Controls("Options...").Enabled=True
in either the workbook_BeforeClose event or in the Workbook_Deactivate event

Bob Umlas
Excel MVP

"bill_s1416 >" <<bill_s1416.1axymw@excelforum-nospam.com> wrote in message
news:bill_s1416.1axymw@excelforum-nospam.com...
> I created an automated workbook where I need to keep the sheet tab names
> hidden from the user.  I went into Tools-Options-View and unchecked
> Sheet Tabs.  Then I protected the workbook and the sheet yet the user
> can still go into Tools-Options-View and re-check the Sheet Tabs to
> view them again.
>
> How do I lock the user out of viewing the sheet tabs? :confused:
>
>
> ---
> Message posted from http://www.ExcelForum.com/
>


0
rumlas (268)
8/13/2004 7:37:40 PM
Bill,

If you want to good and hide the sheets, go to the VBE (Alt-F11), locate
your project (workbook) in the Project Explorer (View - Project Explorer),
select a sheet to be hidden, View (menu bar) - Properties.  Find the Visible
property, and change it to xlSheetVeryHidden.  Be careful, as after you've
done that, it may select the next sheet down, and you may not realize it.
This is a persistent property (doesn't require a macro to set it again upon
the next opening of the workbook), unlike some others.

The only way a user can get to the sheets now is to use the same procedure
(or write a macro) to reset the Visible property.  You can lock the project
(Tools - VBA Project Properties - Protection) for some protection.

-- 
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

"bill_s1416 >" <<bill_s1416.1axymw@excelforum-nospam.com> wrote in message
news:bill_s1416.1axymw@excelforum-nospam.com...
> I created an automated workbook where I need to keep the sheet tab names
> hidden from the user.  I went into Tools-Options-View and unchecked
> Sheet Tabs.  Then I protected the workbook and the sheet yet the user
> can still go into Tools-Options-View and re-check the Sheet Tabs to
> view them again.
>
> How do I lock the user out of viewing the sheet tabs? :confused:
>
>
> ---
> Message posted from http://www.ExcelForum.com/
>


0
no3012 (142)
8/13/2004 9:49:00 PM
Reply:

Similar Artilces:

Insert file name into Cell
Is there a way to insert the file name into a cell, rather than on header/footer? Hi Bonny, 1996FEDT.XLS =MID(CELL("filename",A1),FIND("[",CELL("filename",A1),1)+1,FIND("]",CELL("filename",A1),1)-FIND("[",CELL("filename",A1),1)-1) D:\driveM\excel\TAXES\1996FEDT.XLS [Sheet1] =SUBSTITUTE(SUBSTITUTE(CELL("filename",A1),"[",""),"]"," [") & "]" for more information, worksheet examples, and coding examples for pathname, filename, sheetname and combinations of ...

How do I get rid of the PERSONAL.XLS sheet from a workbook?
Everytime I want to "X" out of excel, I always have to also close the PERSONAL.XLS sheet Normally the Personal file is hidden If you use Windows | Hide then you will never need to close it again OR you could locate it in the XLSTART folder and just delete it - hope it has no macros that you need to use best wishes -- Bernard Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme "Arencia" <Arencia@discussions.microsoft.com> wrote in message news:9E91F069-881E-4D83-A224-8B4E78365949@microsoft.com... > Everytime I want to "X" out of...

Calculate Formulas in Highlighted Cells Only--not whole sheet
In office 97 I was able to recalc only the cells highlighted (control + L) as opposed to the entire sheet or workbook. In Excel 2002, how do I recalc only the cells I have highlighted? Thanks I don't recall this shortcut in xl (any version). I'm guessing that you had a macro that did something special. In xl2002, you could use a macro like this (assigned to ctrl-shift-l) Option Explicit Sub testme() Selection.Calculate End Sub But I think I've read posts that calculating a single range can be bad--it can screw up the calculation dependencies. I think that Charles Will...

getting value of named range
I have a named range. Lets say it goes over lots of cells. A1 to E20 Can I get the values out as a string? Single string. I have tried some things like: excel.Workbooks(MyWorkbook).Worksheets(MyWorksheet).Range(MyRange).value any ideas whoops, this should be in programming sorry "greg" <iuouh@ghfhg.com> wrote in message news:%23Ip5wsw0IHA.4164@TK2MSFTNGP03.phx.gbl... >I have a named range. Lets say it goes over lots of cells. > A1 to E20 > > Can I get the values out as a string? Single string. > > I have tried some things like: > > excel.Wo...

how do i make it so that when a sheat is selected either via link or tab, that xlLastCell is selected. the last on the sheet.
how do i make it so that when a sheat is selected either via link or tab, that xlLastCell is selected. the last on the sheet. In VBA Editor, create a module. Paste this there Option Explicit Dim X As New EventClass Public Sub Auto_Open() Set X.App = Application End Sub Then, insert a Class Module, rename it to EventClass, and paste this there: Option Explicit Public WithEvents App As Application Private Sub App_SheetActivate(ByVal Sh As Object) Sheets(Sh.Name).Select ActiveCell.SpecialCells(xlLastCell).Select End Sub It should work (XL2000). "Daniel" <...

list box using defined name
I went through the directions for creating a "named" list (went to insert->name->define->added cells->clicked add->clicked close) I named my list "testname" I go to the cell I want to be a list box, then do the following ->data->validation->allow list->type "=testname" -> click ok I get an error message, is there something I'm doing wrong? Hi the way i do it in data validation is data / validation / allow = list, click in the source line and press F3, this displays the list of names, i then choose the one i want and excel ...

How do I change a data label on an xy scatter to a unique name as.
The only way I have figured out is to create a separate series for each data point, which is tedious for 100+ points...any ideas? Hi, Try one of these free addin to link cells to data labels. This way you can have a single data series rather than multiples. Rob Bovey's Chart Labeler, http://appspro.com John Walkenbach's Chart Tools, http://j-walk.com Cheers Andy SOSCIENT wrote: > The only way I have figured out is to create a separate series for each data > point, which is tedious for 100+ points...any ideas? -- Andy Pope, Microsoft MVP - Excel http://www.andyp...

From Name
Is there a way to get all the from names the same when downloading credit card transactions. I might have 10 different froms if I have purchases from 10 different Home Depot stores. I understand that this info might be important but would also like to just have the from be Home Depot not Home Depot store 425. Maybe the additional info can go into the memo field. ...

RECORDSET file name length limit?
Here's a snippet of the code I'm using: oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=" & strFilePath & ";" & _ "Extended Properties=""text;HDR=Yes;FMT=Delimited""" Set oRS = CreateObject("ADODB.RECORDSET") 'Now actually open the text file and import into Excel oRS.Open "SELECT * FROM " & strFilename, oConn, 3, 1, 1 When I do the oRS.Open, it fails to find the file. I renamed the file to something short...

Dynamicly change spreadsheet tab names depending on cell value
Is it possible to change tab names dynamicaly ? For example i have 31 tab (1 for each day of the month) These tabs are named - 1,2,3 e.t.c Is it possible to define rule that would ad "!" to day which is weekend day. In other words is it possible to dynamicly change tab name depending from value of cell ? If this is possbile how do i do this ? Right-click on the spreadsheet tab, select View Code and paste this in:- Private Sub Worksheet_Activate() ActiveSheet.Name = Range("A1").Value End Sub To test this I put the date in cell B2 and this formula into A1:- =TEX...

Excel Sheet
Good morning, Need help with: Code to delete all rows in Column A below active cell Turn off warnings in a macro, specifically I am copying a spreadsheet to a new one, and close original, don't want pop up box to ask if file needs to be saved etc. Thanks Cristina Sub DeleteRows() Application.DisplayAlerts = False Range(ActiveCell.Offset(1, 0), "A65536").EntireRow.Delete Application.DisplayAlerts = True End Sub -- Best Regards, Luke M "Christina" <Christina@discussions.microsoft.com> wrote in message news:D5D0E04F-8261-4809-B93...

SumProduct over multiple sheets
Hello The below formula does one sheet. I have ten. Is there a way to have it sum all ten for me that does not require a long formula Thanks for your help =SUMPRODUCT(('Line7'!$C$15:$C$114=C7)*('Line7'!$G$15:$G$114='Numbers'!$S$9)*('Line7'!$AF$15:$AF$114)) I'd use 10 different formulas. Then sum them in an eleventh formula. In fact, if you put each of those formulas in a dedicated cell (say A1 of each individual sheet), you could use a formula like: =sum('line1:line10!a1) As long as all the other sheets are between line1 and line10. Jack wr...

Creating Data Tables off-sheet
Does anyone know how to create data tables in a sheet other than the sheet containing the input data? Someone showed me the trick a long time ago and it has escaped my mind. Thanks in advance for any clues or complete solution. Cheers! Atem Do you mean that your data is in sheet1 and you want to put the data table containing some of that data in sheet2? If so, you can just reference sheet1 in your formulas in sheet2. =sheet2!A50 =sheet2!A50-sheet2!B50 etc etc Lacty wrote: > Does anyone know how to create data tables in a sheet other than the > sheet containing the input data? Some...

Insert a description in the task name column and not showing the s
I am new to Office Projects 2007 and want to insert desription of projects in the task name column but do not want the days, start and finish dates to appear in respective columns. Thankyou Any line will be interpreted as a task, and you cannot avoid the dates and bars. First, try not putting anything on a line which is not a task (or a summary or a milestone). It is not a good place for free-form text, and there are better ways such as in the Notes field. But, that said, it easy to hide text in the dates, durations etc. Just format it to be white. To hide bars, just open...

Hiding Tabs
I am creating an interactive catalogue in excel and to make it look a lot neater i would like to know how i can hide the worksheet tabs, numbers and letters at the sides and the toolbars so that when it is opened by someone all they see is the catalogue itself and not the toolbars etc... :confused: -- beng120 ------------------------------------------------------------------------ beng120's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=23188 View this thread: http://www.excelforum.com/showthread.php?threadid=394855 beng120 Wrote: > I am creating an inte...

Further Show Hide Problem with VBA
Hi again! Bob kindly gave me a suggestion for using VBA to show /hide using a single button. This worked fine until I decided I wanted to have another custom area off to the side. The problem being that when I try to return from this third view not all rows columns are showing. I thought I might get round this by including an unhide for all rows and columns to create a normal view to go from but then the toggle views don't seem to do anything. Any help greatly appreciated DonH Call Unl Application.ScreenUpdating = False Rows("25:75").EntireRow.Hidden = Not Rows(&qu...

Name a sheet = to value in a cell
Is there a way to have the name on the worksheet tab show the value in a particular cell. For example: I want the sheet tab name to be the same as the customer name in Cell A1 without having to type in the name for each sheet/Customer Hi Copy this in the Thisworkbook module Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) If Target.Address = "$A$1" Then If Target.Value <> "" Then On Error Resume Next Sh.Name = Target.Value On Error GoTo 0 End If End If End Sub If you change...

Outlook puts ' in the address names
Hi, When I reply to an email, the to address is sent like this For example Joe Smith is sent as 'Joe Smith' with single quotation mark around. Is there a way to disable that and just send as Joe Smith without quotation mark Thanks No. -- Diane Poremsky [MVP - Outlook] Author, Teach Yourself Outlook 2003 in 24 Hours Coauthor, OneNote 2003 for Windows (Visual QuickStart Guide) Author, Google and Other Search Engines (Visual QuickStart Guide) Outlook Tips: http://www.outlook-tips.net/ Outlook & Exchange Solutions Center: http://www.slipstick.com Join OneNote Tips mailing li...

HOW TO: Plot a Team Name on a chart based on two values
Hi, I have the following table exmaple, what I like to do is to plot the team as where their values cross, e.g. for TEAM 01 where 5 and 60 cross on a graph having Revenue as the Y axis and Clients as the X axis. COLUMN A COLUMN B COLUMN C TEAMS CLIENTS REVENUE TEAM 01 5 60 TEAM 02 10 120 TEAM 03 15 180 TEAM 04 6 72 TEAM 05 12 144 TEAM 06 18 216 TEAM 07 7 84 TEAM 08 14 168 TEAM 09 21 252 TEAM 10 8 96 Any guidence, much appriciated. I've done this using Ron Bovey's XY Chart Labeler www.appspro.com "Kevin McCartney" <KevinMcCartney@discussions.microsoft.com> wrot...

Setting Worksheet Name = to Cell in Worksheet
I'm looking for a way to set the worksheet name equal to a cell in th worksheet itself. I'm currently importing from Crystal Reports int Excel and I want to name the worksheet name to a referenced cell -- arwhitle ----------------------------------------------------------------------- arwhitley's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1631 View this thread: http://www.excelforum.com/showthread.php?threadid=27706 Hi with activesheet .name=.range("A1").value end with -- Regards Frank Kabel Frankfurt, Germany "arwhitley" ...

I need a time sheet template that verifies time entered against sy
Try posting this again with your question in the message text instead of the subject. Your question got truncated. "Bob Powell" wrote: > ...

GP 10 Named Printers being ignored on Citrix
I set up Named Printers on four Citrix servers, using my id as the template user. I followed the steps in David Musgrave's blog post from last August, but it still ignores the default printer I set up. This only seems to happen for me and another person (or others aren't telling me about it). The user's default printer from their workstation is also being ignored. For example, if someone logs into GP, then goes to the Print Setup, it shows an "XPS Document Writer" as the default printer. One would think that even if it doesn't pick up the Named Printers ...

To change the tab color in forms
I am new to the CRM version 3.0 i want to change the background color of tab & text color also Hi: AFAIK there is no supported way to do this. John. This message sent from Windows Vista! "mady" <mady@discussions.microsoft.com> wrote in message news:EA664EFD-4183-4121-A1EF-4A1B857F72C9@microsoft.com... >I am new to the CRM version 3.0 > i want to change the background color of tab & text color also ....but there's a couple of unsupported ways. You can apply the style you want in the onload event. The tab id's are numbered from zero to 7: tab0Tab...

Sort names and e-mails from one column
Hi Just wondering if any could help me with a problem I have sorting names and e-mails from one column. I have an excel spreadsheet and in column A it has a list of names and e-mail addresses. I need to split them into 2 columns emails in one names in another for importing into another program. Is this possible? The emails are all odd and the names even if this helps. A B C 1. email 2. name 3. email 4. name 5. email 6. name Thx in advance for any help. -- jul3s ------------------------------------------------------------------------ jul3s's Profile: http://www.ex...

Remove white border across the Tab control
I am implementing a tab sheet but I AM SEEING A WHITE BORDER around the whole tab control which looks quite odd. To remove the border I added the code in PreCreateWindow cs.style &= ~WS_BORDER; But this does not work.I also tried to override DrawItem but this does not work either. void CMainTabCtrl::DrawItem(LPDRAWITEMSTRUCT lpDrawItemStruct) { char szTabText[100]; TC_ITEM tci; memset(szTabText, '\0', sizeof(szTabText)); tci.mask = TCIF_TEXT; tci.pszText = (LPWSTR)szTabText; tci.cchTextMax = sizeof(szTabText)-1; GetItem(lpDrawItemStruct->itemID, &...