I 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.
how to lock a sheet in Excel 2007 so that no one can navigate to that sheet.
I have a sheet. At the top I have a pivot that essentially lists all our Depts. Below is all kinds of data and calculations dervided via: vlookups on the dept chosen above. The sheet rerfreshes itself each time a new dept is chosen.
Well, they wanted certain cells protected. I did the ole: Tools then: Protection: then: Allow Users to Edt ranges and added the ranges that should be left unprotected after protection is applied. Of course the pivot was left unprotected.
Once you do the: Protect Sheet and and try to click the pivot dropdown to select a dept...it remains locked!!! An error pops up and says you can not have a pivot open on a protected sheet. WHY!! The range is open!!!
I need the user to be able to select their Dept from a dropdown and then my vlookups work off that.
I have Book having 11 Tables.
In each Tables in Column F i have formula like., =INDEX(Sheet2!A:A,MATCH(Jyotindra!G10,Sheet2!B:B,0))
I want to lock only column F for all Tables. and hide formula
All other columns and cell should be free to excess or edit.
How can i do that for all sheet at once?
Some of the sheets in my spreadsheet contain some data and graphs with a few macros that are run when a button is pressed. At the bottom I have some comments with a macro that is run to timestamp when these comments are updated or edited.
What I would like to do is lock the entire sheet so that the data and graphs can't be tampered with, apart from the comments part at the bottom because I'd like to be able to have the comments editable. So far I have managed to do that, but when I edit a comment and the timestamp code is run it creates a run-time error 1004. I could just remove the timestamp code I guess but I'd like to keep it there... figuring that there must be a solution.
How can I work around this problem ? Either by being able to keep the sheet locked and still have the timestamp code run, or have it temporarily unlock the sheet - run the code - and then re-lock the sheet.
I am trying to Protect a Sheet to lock the width of just one column. Does the lock apply to all the columns in the worksheet or can it apply to a single column only. I can't seem to get this to lock just one column-if it is possible.
View 1 Replies View RelatedI've got a work book with numourous password protected sheets which i quite often have to update (as i use around 10 of these with at least 10 sheets in each its time consuming). I've had several goes at creating 2 macros to unlock and then lock the sheets (with my password). I've managed to create on that unlocks the sheets using the password but i can't get a macro to lock the sheets up again using the password.
View 9 Replies View RelatedI have a Workbook that I use to log invoices and credit notes and it is password protected so that the cells that contain formulas are locked and therefore not selectable.
What I wanted to know is. Is it possible to lock other cells depending on what values are in certain cells?
Let me explain. I have Columns G & H which contain the heading 'Credit Note Number' & 'Credit Note Amount'. Now in columns I & J I have the heading 'Invoice Number' & 'Invoice Amount' respectively.
What I have done is to make a condional formatting so that If I put a value in cells in column G it will change the colour of the adjacent cells in columns I & J, likewise If I fill in cells in column I the conditional formatting will change the colour of adjacent cells in coulumn G & H. This helps me to see whether a row contains a credit note or an invoice.
What I would ideally like is for a Macro that would automatically locks cells G & H in a particular row that I am currently working on if I enter data into the cell in column I. Likewise if I am entering data in column G in the row I am currently working on for a credit note, then lock columns I & J in the row I am currently in.
Any Ideas how this can be done? Also can locking and unlocking of cells be done in VBA while the sheet is protected or would the macro need to temporarily unprotect sheet, lock the cells then protect sheet again?
PS: I have uploaded a Jpeg of what my conditional formatting does to try and illustrate what it is I need.
I have a worksheet with employees names and their overtime hours worked that week. I would like to add some code to a button that will prompt for a password. If that password matches a predefiened password then lock a range of cells and place a time stamp next to the button and maybe change the button color of the button to Red. Now if the person with the password wants to change the locked cells, they can click the button again and enter the password and the button will turn green so they can make the changes. I would like the time stamp to be updated every time the cells are locked. Also wanted to have one button for every work week so about four buttons per worksheet. Here is an example of the worksheet. The range of cells I want to lock are the ones in bold.
View 3 Replies View RelatedIs it possible to Sum across sheet tabs, if so how?
View 8 Replies View RelatedI 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. can you hide tabs on excel?
View 4 Replies View RelatedIs 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".
I'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 RelatedIs there a way I can lock the sheet tabs so they cannot be viewed or turned on by anyone except me?
View 9 Replies View RelatedHave 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.
is there a way by which i can have Excel sheet tabs on the top rather than currently at below.
View 9 Replies View RelatedI 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
My 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...
I 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 RelatedI 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 RelatedI 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
Just 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 RelatedI 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 do I rotate sheet tabs 90 degrees?
View 1 Replies View RelatedI 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?
This 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
I 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 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 RelatedI 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.