Locking Sheet Tabs
Nov 28, 2006Is there a way I can lock the sheet tabs so they cannot be viewed or turned on by anyone except me?
View 9 Replies
ADVERTISEMENT
Locking Different Cell Ranges Then Locking Whole Sheet With Macros
Jan 4, 2010I have been assigned the task of creating our new integrated time and leave sheet in Excel 2007, previously we had 2 sheets, one for times and one for leave.
The problem I have is that I need to protect the authorisaton columns therre are 2 one for AM and one for PM, so that only Managers can authorise leave by inputting a password and then initialing the leave.
What I then need to do a the end of each month is for the manager to be able to click a Button with a Macro which will then lock the whole worksheet so that employees cannot change their times after the manager has checked the sheet.
I can lock the authorisation columns and password protect but I have to enable protection on the sheet, when I do this I cannot then figure out a way of running a macro which will then protect the whole sheet and assign a password to it so it cannot be changed after being checked and signed off.
I have tried to create a macro (button) to remove the protection on the 2 columns and then reapply protection to the whole sheet with another button but to no avail.....
If anyone has any suggestions I would be eternaly greatful.
Passwords on the sheet for the different buttons are abc or cobra.
You can download my Spreadsheet here.
[url]
Locking Cells And Protecting Sheet
Feb 14, 2007A1 is number of days available
this sheet goes to a routing recepient from me who fills A2 and B2. C2 and D2 are calculated.
A2 has starting date
B2 has ending date
C2 is days between these two dates
D2 is number of days left i.e. A1-C2
I want to lock and protect cells A2,B2,C2 and D2 so that these cannot be changed when I route it back to get next set of dates to be filled by the same person.
Locking Sheet So User Cannot Add Or Delete Date
Mar 6, 2007i want to lock the excel sheet..so user cant add any data or delete it...only through VBA form????
View 9 Replies View RelatedLocking One Cell If Another Had A Value In It (conditional Locking)
Oct 10, 2008I recently posted a thread on locking one cell if another had a value in it, and vice versa for the other cell. I got this great response (thanks se1429!) and it works GREAT, but I failed to mention that the worksheet is password protected. It asks for a password when I enter a value in one of the cells. I just need help adjusting this code so I can put my password in the code and allow the worksheet to unlock and lock at will by using this password.
View 9 Replies View RelatedSum Across Sheet Tabs
Nov 25, 2008Is it possible to Sum across sheet tabs, if so how?
View 8 Replies View RelatedCollating Tabs Into One Sheet
Dec 11, 2013I have a spreadsheet with around 250 tabs, all with a small amount of information on laid out int he same format. My question is can i collate this into one long tab?
View 5 Replies View RelatedHiding Sheet Tabs
Oct 13, 2008Hiding sheet tabs. can you hide tabs on excel?
View 4 Replies View RelatedLock Sheet Tabs Using VBA
Oct 13, 2008I need to get rid of the option to DELETE a sheet. However, when I password protect the WORKBOOK (which does this fine) none of the Macro buttons will execute, even with Macro security settings set to ENABLE ALL.
Looks like I will need some VBA code to not allow any of the sheets in the workbook to be deleted.
Referencing Sheet Tabs
Oct 7, 2005Is there a way to use the "value" of a name in a drop-down list to reference a sheet tab name in a formula?
A1 has a drop down list. When a name (Bob's Sales) is selected from the drop down list, B1 shows the value of cell D5 from sheet "Bob's Sales".
Alphabetize Sheet Tabs
Feb 10, 2005I've got a bunch of worksheets that I'd like to re-arrange in alphabetical order. Anyway to do this quickly? Or do I just have to drag and drop them myself? (WXP, Excel XP).
View 9 Replies View RelatedSheet Tabs In 2007
Oct 7, 2007Have designed a spreadsheet in 2003 at work and got home to work on in it using my machine which has 2007 loaded.
Have hidden the sheet tabs on the 2003 sheet and can not find the option in 2007 to make them visible again.
I realise I can ctrl-pgup and pgdn but I got a fair bit of movement from sheet to sheet to do and that wont be practical.
Excel Sheet Tabs On The Top Row
Sep 25, 2008is there a way by which i can have Excel sheet tabs on the top rather than currently at below.
View 9 Replies View RelatedSheet Tabs Count
Feb 1, 2009I know excel has a built in function for this. But it won't do what I need for this below.
Say, I have 9 worksheets open. In cell N7 on sheet 1 I need it to count the worksheet tabs.
But the dilema is to only count the tabs that will equal sheet 1, sheet 2, sheet 3, sheet 4, sheet 5, Notice, I have a space before the number. Other sheets have a specific names to them which I do not want to count.
So if sheets 1 thru 3 are visible with the other named sheets visible cell N7 will count 3
How To Get 3D Sum Against Increasing Number Of Sheet Tabs
Mar 9, 2014My wife and I run an apartment complex with 2 employees, a manager and a maintenance man.
We use excel to produce a paystub for these employees twice a month.
There are a number of variables including incentives for renting apartments, overtime, variable hours in a pay period etc.
We have a separate spreadsheet for each employee and a separate sheet tab for each pay period named for the pay date... e.g. the first paystub had one sheet named "Jan 15"... the next pay period the paystub had 2 sheets: "Jan 31" and "Jan 15". The third paystub had 3 sheets and so on.
The paystubs have a column with the numbers for the pay period and then a column with the year to date sum (for straight pay, over time, federal withholding, social security, medicare, state withholding, etc.)
Right now I edit the formulas for all these sums for each payperiod. e.g. =sum('Feb 15':'Jan 15'!B35) for the third pay period is editted to =sum('Feb 28':'Jan 15'!B35) for the fourth pay period.
I would like to be able to just copy the prior spreadsheet and not have to find and replace values in 11 spreadsheet cells for each pay period.
I have figured out how to get the name of the current spreadsheet to appear in a cell on that sheet using VBA... but not how to get that cell content to be part of the arguments for =sum(. on the formula bar...
2000 Sheet Tabs Missing
Jan 21, 2010I am pulling my hair out trying to figure this out. Any help at all is greatly appreciated. I created a spreadsheet for a friend to track his profit/loss for his business; he is not very computer literate so I created separate tabs for each month. I have no idea what he did but the horizontal scroll bar and all of the tabs are not visible, he is using excel 2000 on windows XP. Unfortunately we are not in the same area so I can’t go look at his screen. When he emailed the file back to me and I opened it, it looks fine. Also when he opens any other file it does the same thing. I have gone through all of the obvious causes, everything is correct in the tools, options.
View 8 Replies View RelatedMultiple Rows Of Sheet Tabs?
Aug 15, 2013I have a lot of sheets, in my workbook. It keeps expanding, as I add them. How do I make multiple rows of sheet tabs, to keep from having to scroll across all the time?
View 4 Replies View RelatedHiding Tabs At The Bottom Of Sheet
Jul 23, 2014I am using the following codes to hide the menu, formula bar and the headings. One more thing I need to do but do not know how - hiding the tabs at the bottom of the sheet. The idea is not to let users to right click and unhide other sheets.
'Hide main menu
Application.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",False)"
'Hide headings
ActiveWindow.DisplayHeadings = False
'Hide formula bar
Application.DisplayFormulaBar = False
Delete All Sheet Tabs Apart Form The First 3
Nov 5, 2008Just created 1800 sheets by not closing a loop, just wondering if anyone has a quick solution to delete all of these apart from the first three.
View 4 Replies View RelatedConsolidate All Of The Tabs In The Master Sheet
May 19, 2009I have a file that will have a maximum of about 25 tabs in it. They are all the same format - Column A have account codes, Column B have the account name and Column C through to Column AD have figures. Column B has a drop down menu, so not all tabs will have the same account code on the same row.
I thought that I would create another spreadsheet in the file and call it a master sheet with the same formats as all the other tabs but this master file will have all of the account codes available and will not have a drop down menu in column B. What I wanted to do was consolidate all of the 25 tabs in the master sheet.
Example Worksheets
TAB 1
Acc Code Desc Amount
1________a___10
2________b___10
TAB 2
Acc Code Desc Amount
1________a____10
2________b____10...........
How To Rotate Sheet Tabs 90 Degrees
Feb 23, 2012How do I rotate sheet tabs 90 degrees?
View 1 Replies View RelatedAutomatically Rename Sheet Tabs
Nov 3, 2009I have a workbook with multiple tabs. On sheet one a person chooses a date to initialise a formula. That date is then copied to sheet two and placed in cell D5. The date is projected out for two weeks and then the next fornight starts on sheet three and so on. The start date for each new fortnight appears in cell D5 of each sheet. I want that information to be the name of the tab for sheet two, three etc. I have put this code into the "This Workbook" section:
Private Sub Worksheet_Change(ByVal Target As Range)
' Renames all worksheet tabs with each worksheet's cell A1 contents.
'If cell A1 has no content, then that tab is not renamed.
For i = 1 To Sheets.Count
If Worksheets(i).Range("$D$4").Value "" Then
Sheets(i).Name = Worksheets(i).Range("$D$4").Value
End If
Next
End Sub
I have also tried:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$D$4" Then ActiveSheet.Name = Left(Target.Value, 10)
End Sub
I then go back into sheet one and change the date and nothing happens. I seem to be missing something. Can anyone please help?
Copy Worksheet To End Or Far Right Of Sheet Tabs
Feb 27, 2009This macro helps me create a blank worksheet and automatically names it from the list in column A. Unfortunately, it adds the worksheets to the left of my 'master' sheet called "Tab Names". When I try adding "after" when it adds a new sheet, I keep getting a "compile error: Expected: end of statement". I am positive you will know what steps I'm missing.
' Vendor List Subroutine to
' Open New Worksheet called Tab Names worksheet
' With data from Tab Names
' Rename sheet to name from list and start again
' For all names in list
Sub NewTabsFromList()
Dim cCell As Object, i As Integer 'Two variables cCell (current Cell) object and i (standard counting integer)
Cells(1, "A").Select 'Ensure that we start each time at the top of the worksheet
Application. ScreenUpdating = False 'Turn of screen updates whilst macro is running
For Each cCell In Range(Cells(1, "A"), Cells(1, "A").End(xlDown)) 'Will run our code through each cell with text
Set NewSheet = Sheets.Add(Type:=xlWorksheet) 'Add new worksheet
NewSheet.Name = "Tab Names Worksheet" 'rename worksheet
Sheets("Tab Names worksheet").Name = cCell.Value
Next cCell
End Sub
Sheet Tabs Not Visible In 1 Workbook
Dec 28, 2006I know this is probably an easy one, but can anyone help, I have a spreadsheet with aboout 8 worksheet tabs, for some reason they have disappeared I have emailed the spreadsheet to my work email and everything is fine, does anyone out there know what I have done. I have checked the tools options and view tab as informed and the sheet tabs are definately checked, also I have ensured that the tabs are not hidden.
View 9 Replies View RelatedMacro To Prompt Protect Sheet Box On All Tabs?
Jun 20, 2014Macro to Run all tabs in a workbook and prompt a pop up asking about sheet protection for each tab, such as Select locked cells, and select unlocked cells.
View 8 Replies View RelatedAny Way To Stack Sheet Tabs In Multiple Rows?
Apr 30, 2013I have a spreadhseet with lots of sheets. Instead of scrolling across to see ones out of view, it would be really neat if the view of the sheets could be stacked to show them on multiple rows.
Can this be done?
VBA is an option if I need to go down that route.
Group Multiple Sheet Tabs Into One Master Tab?
Mar 10, 2014Is there a posibility that using VBA i can group sheettabs (Sheet 1, Sheet 2, Sheet 3) into 1 single master tab (ex. "MASTER") ?
Since I will be having 20 sheets in a excel file I would like to group them together.
Activate Sheet And Disable Tabs On Open
Dec 6, 2008I am trying to create an event macro that would deactivate column and row headers when the workbook is launched and activate a sheet. i am not managing to do this,
View 2 Replies View RelatedCopy Cells From Multiple Tabs To One Sheet?
Feb 20, 2012I need to have cells from sheets to automatically transfer to a separate total sheet...
So in other words for every tab/sheet I have...I need cell B10, once populated, to automatically transfer to a designated cell on a separate Total tab/sheet...
I tried =Sheet2!B10...but when I copy it into the next cell it reads...=Sheet2!c10...what I need is for the sheet to change...but the cell to stay the same...
Merge Data From Many Many Tabs (worksheets) Into A New Sheet
Feb 26, 2008Basically I inherited a Excel Workbook which has 128 different tabs, unsurprising this is almost impossible to manage.
My requirement is to merge all of the 128 tabs into 1 tab 'Sheet1'.
The macro should keep going even if there are blanks, it should move onto the next tab only when there are over 10 consecutive blanks in Column B.
I have tried searching the forum but nothing seems to cater for the specifics of this case. I would really appreciate some help. I mean really, i am in a mad panic thinking i will have to do 128 tabs manually otherwise.