I have a protected sheet that contains information that sometimes is viewed and other times is modified.
In cell Y49 (locked cell) I need to show the date in which any of the unlocked cells (modifiable while the sheet is protected) was modified.
I am using the Microsoft Date and Time Picker Control 6.0 (SP4) from the more controls option on the developer Ribbon. The issue I have is when I protect the sheet I lose the functionality?
I have ranges selected as editable by user when the protect is on - this includes the linked cell to the date picker but it still doesn't work.
What settings should I use in order to allow a user to sort data in a table when some of the cells are locked?
When I protected the sheet I ticked the option to allow user to sort data but it doesn't work...
I'm using Excel 2010 and I've got a chart on a worksheet and the worksheet needs to be protected. I'm able to manipulate the chart in any fashion EXCEPT the position of the Data Labels. Is this a bug in Excel 2010?
View 3 Replies View RelatedI have created a simple userform that is linked to a button on sheet1. When the data in the userform is submitted, I set it up so it goes into a database under sheet2. The problem is, I don't want anyone modifying the entries in the database. Usually I would protect the sheet with a password, but when I do that, there is an error when the userform is submitted. The only thing I can think of is to hide sheet2, which doesn't seem like a great solution.
Is there any way to protect my database from being modified yet still allow the userform to be linked to it?
I am using Excel 2010 and I have a password protected workbook with password protected sheets that uses several macros. Most of them, in order to run, have to un-protect the sheet and then re-protect it again. This has been accomplished easily enough by adding ActiveSheet.Unprotect Password:= "mypassword" and ActiveSheet.Protect Password:= "mypassword" to the appropriate places in the script. All of my macros, which do various things like sorting and moving data, deleting blank rows, displaying dialog boxes containing warning messages etc. run fine.
My problem is this: when I password protect the sheets manually, I have checked the following options in the "Protect Sheet" dialog box. Under "Allow users of this worksheet to" I have checked 1)Select unlocked cells and 2)Format cells. After entering my password and closing the dialog box my sheet is protected, but I can edit cells in the manner my allowances permit. However, once I run any of the macros that un-protect and re-protect the sheet, I remain able to select and edit unlocked cells (practically, for my purposes, this means that I can input data which will appear in the default font size and color of the sheet) but I cannot format cells (which, practically, for my purposes would allow me to occasionally change the font color and size of the data). Naturally, after running a macro, the other cell-formatting options are unavailable to me as well. Is there any way to get my manual selections to remain in place after running a macro that functions as mine do? Or is there any way to make my manual selections the default settings for a protected sheet?
I use my workbook to track sales data from one store to the next. I use my workbooks to compare data from year to year. Each year's data is displayed on a separate worksheet. '2013' has 2013's data, '2014' has 2014's. On the 2014 sheet, I have a Prior Year's Sales that pulls data from the previous year's spreadsheet using a formula which I just drag down each day I enter sales. I would like to automate this process and have the VBA code check for today's date and automatically pull the previous year's sales data from the '2013' sheet and put it in the appropriate cell on the '2014' worksheet. I hope I have explained this well enough to understand. I've included a link to my workbook for reference.
I had to use dropbox since I can't post a file over 1 MB. The file size is around 1.25 MB.
I have spent ages creating a work diary and have protected the sheets using VBA however on finishing the sheet one important thing that needs to happen is the ability to copy and paste information from the boxes to another worksheet. i.e MONDAY - SUNDAY
View 1 Replies View RelatedDoes anyone know if there is a way to unprotect a sheet. The person who created this spreadsheet no longer works for our company and although we can see the spreadsheet, the formulas are hidden so we can't figure out where the data is being pulled in from. Any ideas?
View 9 Replies View RelatedSheet 1 contains a column titled "name" and 6 columns to the right of name titled "sat", "sun", "mon"........ the col titled name is not the first col in the work sheet.
In each row I enter the guest name under the name col and I enter a room number (example: 1A, 1B, 2A, etc...) in the column to the right that coresponds with the day the guest will be staying.
Name Sat Sun Mon Tue Wed Thu Fri
Mr. Smith 1A 1A 1A 1A
Mr. Jones 3B 3B 3B
Ms. Tiller 4A 4A 4A 4A 4A 4A 4A
Sheet 2 is in the same workbook and looks something like this:
Room # Sat Sun Mon Tue Wed Thu Fri
1A
1B
2A
2B
3A
3B
I want it to look at sheet 1 and populate the date columns with the guest name from sheet 1
I tried this formula =IF('Sheet1'!G:G="1A",'Sheet1!$F:$F,"Available")
It seems to work on the first row but I have problems with any rows below that. Basically it acts like there is no data in the rows below.... I think the fact that the names on sheet 1 are not alphabetical is creating part of my problem? I tried a lookup formula but it seems like it has to be the first column in the worksheet and it has to be alphabetical to work like that.
In the attachment you will see an example of what I am trying to accomplish. What I am trying to do is find VBA code that will move data by date from an (Entry) sheet to a (Historical) sheet. I want to enter a date in B3 on the (Entry) sheet. I then enter data into C3. What I would like to happen is when the data is entered into C3 the sheet goes and finds the same date that I entered in B3 and copies that data from C3 into Column E of the (Historical) sheet.
Test3.xlsm
is there a code to still allow me to move between sheet tabs by using [alt] pg up or [alt] pg dn on a protected sheet?
What code would I use to test if the sheet is protected or not? In other words:
If Activesheet.Protection = True Then
MsgBox "You have no authority!!!"
Else
' Some code here
End If
I have a workbook whose worksheets use protection (don't want my calculations stomped on).
When the Protection is on, the search function does not work. You call it up, write what you are looking for, it accepts it, and then when you tell it to search it ignores you.
When you turn the protection off, the search function works just fine.
HOWEVER....Other workbooks I have with protection on its sheets do not share this problem, just this one workbook. And the problem is on all sheets in this workbook. The problem is not on any of the sheets in the other workbooks. I can't see anything different between them, but then I may not know what to look at.
I wanted to know:
Q1. How can we insert new rows in a protected sheet?
For example: In the attached sample protected excel sheet "insert rows.xlsx" , if for Employee ID and Name fields, the user has more no. of records than provided, how can he insert new rows considering the fact that Company ID and Name field are locked.
Or any way to address this issue via some other method like adding a scrollbar,etc.
I've locked certain columns on my worksheet so that users cannot overtype target dates etc. I've password protected the worksheet.
The password protection means that for some reason the users can't use the autofilters that are on the header row.
Can someone help me solve this problem; I still need the locked cells and password protection on the sheet but the success of the sheet depends on users being able to filter for specific rows using the autofilter....
I have protected several sheets in my workbook to protect the forumlas and data for being tempered with by the users.
When I try to run my macro, I get an error message stating:
Runtime '1004'
You can not use this command on a protected sheet
And the debug is pointing to this part of my code
When I protect my sheet and have the option to allow the users to autofilter the sheet, how come it doesn't work? I want to allow people to autofilter the data in the worksheet but not modify the contents of the cell.
View 9 Replies View RelatedI have a dropbox located on a sheet which other information needs to remain protected.
How do we give access to users to the dropbox while the remainder of the sheet is protected?
I have a question relating to the use of autofilter in a protected sheet.
My code is working fine in setting the autofilter and locking down the sheet but a user then loses the ability to use the (Data - Filter - Show All) menu button.
Is there a way to have the worksheet protected, allow the use of the auto filters, and allow the use of the show all button?
Code below incase it is of relevence ...
I am looking for VBA code that will unprotect the Workbook, prompt the user for the number of rows to be added, find the last row in the Worksheet, insert the requested number and reprotect the Worksheet. I would like the location where the rows are inserted to NOT be dependent on the "active" cell.
View 6 Replies View RelatedI have a protected sheet to avoid delete of functions. In a specific area, however, i have merged cells of five columns in rows. Because in these lines i have write some text and overcomes the rows, after, what i do is: i unmerge the rows and choose 3 rows from 5 columns together and merge. Thus, the text is in one framework. In fact, every time, i want to merge different regions, but always i want to merged any areas who have marked with the mouse. Then, i delete the text and i need to redo the context of three lines, three separate lines. I hope you understand what i need.MergeAndUnMerge.xlsm
View 1 Replies View RelatedI want to allow user copy(Ctrl + C) and paste(Ctrl + V) a row. because some columns are locked and the sheet is protected. so, when user doing so, a warning dialog is show and can't do the paste.
so, I want to make it available in code. what I want to do is add some codes in this function:
VB:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Application.CutCopyMode = True Then 'there are someing in clipboard
Sheet1.Unprotect 'unprotect sheet, so can paste
End If
and in Worksheet_Change function, I protect the sheet again.
But I find that when I do Sheet1.Unprotect , the clipboard is empty! I just want to allow user can copy and paste a row, the locked cell's value can be ignored.
I'm trying to manage my Stock by using protected sheet, but i stuck with this "row adding with formula" stuff.
The user can only write in the colored cell, but everytime the user add new row, the formula doesn't copying by itself
I attach the file below with no password, if it's protected, the password is none, just press enter ( password : "" )
Book2.xls
We are needing to do a two tier sort in a protected spreadsheet. Is this possible?
The two columns that need to be sorted have protected cells between them.
If there a way to ad a button that will automatically do this sort for the user... as in can I add a button some how in one of the empty cells that will do it?
Here is the sheet. [URL] ..........
Looks like I need to sort column A thru Z, except W.
I am working on an excel table in my worksheet contains formulas and locked cells. lets say table range is from column "a" to "f" and there is formula on cell in columns "c" and "f". I just locked cells in column "c" and "f" to prevent people changing the formula accidentally. but when the sheet is protected i am not able to insert or delete rows as the rows has locked cells in columns "c" and "f".
I tried the below macro to unlock the sheet, insert row and lock the sheet again:
[Code] .....
So by running the macro it asks the password and do the job but the issue is it needs password and i do not want people to know my password. I don't want them able to unlock the workbook and make any undesired changes. I want them to be allowed insert or delete rows and input data in unlocked cell. I want the sheet to be locked all the time and only when people want to insert or delete a row it be unlocked and be locked again after inserting or deleting whit no need to insert password in this case only.
I have a sheet protected and filters enabled. I use this code to enable sort and filters:
[Code] ........
Nevetherless, when I try to sort, I receive this message in excel: "The cell or chart you re trying to change is on a protected sheet.
To make changes, click Unprotect Sheet in the Review tab (you might need a password)."
I am using Outlining on a sheet called "Today".The sheet is manually protected but now the outlining does not work as it says sheet protected.Is there a code i can use so that i can still have the Outlining working in a protected sheet.
View 5 Replies View RelatedI would like to be able to add data to a protected sheet, only from a form without unprotecting the sheet first. Below is the code.
View 5 Replies View RelatedI have a protected sheet. Under normal circumstances, once the sheet is protected, the facility to choose the font goes away. You can allow it by putting a check against 'Format Cells' in the protection dialogue. But then, the user can format all aspects of the cell.
What if I want the user to be able to format only the font (type and size) and nothing else in the cell or in a range of cells. Is there a procedure or VBA for that?