Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I sort locked cells in Excel?
I've created a worksheet with one column of data (hospital room numbers) that
always remain constant. I have 5 columns where data (numbers) will be entered daily. I have a calculation column where the numbers in the previous 5 columns are totaled (hidden column). I then have a last column that returns a specific word based on the value in the previous calculation column. I need the first column with room numbers, the hidden column with the calculation, and the last column with the word value to be locked so they cannot be manipulated. I can do all those things very easily. The problem is that a user will have to enter data in the middle 5 columns, and then sort the spreadsheet, and Excel will not allow a sort using the locked fields. Any suggestions? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I sort locked cells in Excel?
Cathy,
Well im a newbie but i had almost same prob as you with locked cells, i had a macro they needed to run but would'nt cause they dont have formatting rights once the doc is protected. Well i had to put this line at the begining of my macro ActiveSheet.Protect UserInterfaceOnly:=True if you have it protected with a pass theres another line that uses ur password, however in my case i just took off my pass and just protected the form. "Cathy" wrote: I've created a worksheet with one column of data (hospital room numbers) that always remain constant. I have 5 columns where data (numbers) will be entered daily. I have a calculation column where the numbers in the previous 5 columns are totaled (hidden column). I then have a last column that returns a specific word based on the value in the previous calculation column. I need the first column with room numbers, the hidden column with the calculation, and the last column with the word value to be locked so they cannot be manipulated. I can do all those things very easily. The problem is that a user will have to enter data in the middle 5 columns, and then sort the spreadsheet, and Excel will not allow a sort using the locked fields. Any suggestions? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I sort locked cells in Excel?
Thanks - I'll see if I can make that work. I guess I'm missing the logic of
why locking fields but choosing "sort" when protecting the worksheet would not work. Guess that's why I'm a nurse and not a programmer...... "Redskinsfan" wrote: Cathy, Well im a newbie but i had almost same prob as you with locked cells, i had a macro they needed to run but would'nt cause they dont have formatting rights once the doc is protected. Well i had to put this line at the begining of my macro ActiveSheet.Protect UserInterfaceOnly:=True if you have it protected with a pass theres another line that uses ur password, however in my case i just took off my pass and just protected the form. "Cathy" wrote: I've created a worksheet with one column of data (hospital room numbers) that always remain constant. I have 5 columns where data (numbers) will be entered daily. I have a calculation column where the numbers in the previous 5 columns are totaled (hidden column). I then have a last column that returns a specific word based on the value in the previous calculation column. I need the first column with room numbers, the hidden column with the calculation, and the last column with the word value to be locked so they cannot be manipulated. I can do all those things very easily. The problem is that a user will have to enter data in the middle 5 columns, and then sort the spreadsheet, and Excel will not allow a sort using the locked fields. Any suggestions? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I sort locked cells in Excel?
Perhaps this will offer a solution.
Start by protecting your worksheet again. If you are going to protect it with a password, write it down exactly as it must be. Next use Tools | Macro | Record New Macro give it a nice friendly name like SortStuff (or not) and go through the exact process you would have to go thru by hand: start by unprotecting the worksheet, including entering the password if needed, then choosing the area to be sorted and then using the Data | Sort function with whatever choices you need to make, end by hitting [Esc] to unselect the area and maybe even choosing a convenient cell to end up in, and finally Protect the sheet again. stop recording the macro. Now, if you used a password to protect the sheet, you are going to have to modify the macro that was created. If you didn't use one, you're good to go on right now. To edit the macro, choose Tools | Macro | Macros and pick the one you recorded and click the [Edit] button. First look for a line that looks much like this: ActiveSheet.Unprotect and go to the end of it, hit a space and add this to the line Password:="mypassword" so it should end up looking like ActiveSheet.Unprotect Password:="mypassword" next look for the line that protects the sheet again: ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True go to the end of that line and add this , Password:="mypassword" It should look like this when you finish (this is all actually on one line) ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, Password:="mypassword" substituting your actual password for mypassword in both places. Close the VB Editor. You're done there. Back on your sheet put a text box from the Drawing tools and enter some text in it like "Click Here to Sort". You could make the first row kind of tall and stick the box in that area for convenience. To get to the Drawing tools, choose View | Toolbars | Drawing it will appear down at the bottom of the Excel window. Right-click on the text box (you can now drag it around to position it neatly on the sheet) and choose Assign Macro and point it to the macro you recorded earlier. Voila! The user can click that to automatically unprotect the sheet, sort the data, and put the sheet back into protected mode without having to ever know the actual password. You can also right-click on that text box and choose Format and dress it up by giving it some color. This should offer adequate protection from casual, accidental alteration of your worksheet. An industrious individual might open up the VB Editor and see your code and use the password there to unprotect and modify the sheet. But you can protect your VB Code also, with yet a different password (definitely write it down so you don't forget it because if you lose it, you can't change the code). To do this, follow these steps: Press [Alt]+[F11] to get back into the VB Editor. Choose Tools | VBAProject Properties and click on the [Protection] tab Check the box labeled "Lock Project for Viewing" and give it a password (this password is separate from the one used to lock the worksheet) and close the VB Editor again. Now there's little chance that someone will "pick the lock", at least not accidentally. "Cathy" wrote: I've created a worksheet with one column of data (hospital room numbers) that always remain constant. I have 5 columns where data (numbers) will be entered daily. I have a calculation column where the numbers in the previous 5 columns are totaled (hidden column). I then have a last column that returns a specific word based on the value in the previous calculation column. I need the first column with room numbers, the hidden column with the calculation, and the last column with the word value to be locked so they cannot be manipulated. I can do all those things very easily. The problem is that a user will have to enter data in the middle 5 columns, and then sort the spreadsheet, and Excel will not allow a sort using the locked fields. Any suggestions? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I sort locked cells in Excel?
I'll try to give that a try. Thanks for your help.
"JLatham" wrote: Perhaps this will offer a solution. Start by protecting your worksheet again. If you are going to protect it with a password, write it down exactly as it must be. Next use Tools | Macro | Record New Macro give it a nice friendly name like SortStuff (or not) and go through the exact process you would have to go thru by hand: start by unprotecting the worksheet, including entering the password if needed, then choosing the area to be sorted and then using the Data | Sort function with whatever choices you need to make, end by hitting [Esc] to unselect the area and maybe even choosing a convenient cell to end up in, and finally Protect the sheet again. stop recording the macro. Now, if you used a password to protect the sheet, you are going to have to modify the macro that was created. If you didn't use one, you're good to go on right now. To edit the macro, choose Tools | Macro | Macros and pick the one you recorded and click the [Edit] button. First look for a line that looks much like this: ActiveSheet.Unprotect and go to the end of it, hit a space and add this to the line Password:="mypassword" so it should end up looking like ActiveSheet.Unprotect Password:="mypassword" next look for the line that protects the sheet again: ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True go to the end of that line and add this , Password:="mypassword" It should look like this when you finish (this is all actually on one line) ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, Password:="mypassword" substituting your actual password for mypassword in both places. Close the VB Editor. You're done there. Back on your sheet put a text box from the Drawing tools and enter some text in it like "Click Here to Sort". You could make the first row kind of tall and stick the box in that area for convenience. To get to the Drawing tools, choose View | Toolbars | Drawing it will appear down at the bottom of the Excel window. Right-click on the text box (you can now drag it around to position it neatly on the sheet) and choose Assign Macro and point it to the macro you recorded earlier. Voila! The user can click that to automatically unprotect the sheet, sort the data, and put the sheet back into protected mode without having to ever know the actual password. You can also right-click on that text box and choose Format and dress it up by giving it some color. This should offer adequate protection from casual, accidental alteration of your worksheet. An industrious individual might open up the VB Editor and see your code and use the password there to unprotect and modify the sheet. But you can protect your VB Code also, with yet a different password (definitely write it down so you don't forget it because if you lose it, you can't change the code). To do this, follow these steps: Press [Alt]+[F11] to get back into the VB Editor. Choose Tools | VBAProject Properties and click on the [Protection] tab Check the box labeled "Lock Project for Viewing" and give it a password (this password is separate from the one used to lock the worksheet) and close the VB Editor again. Now there's little chance that someone will "pick the lock", at least not accidentally. "Cathy" wrote: I've created a worksheet with one column of data (hospital room numbers) that always remain constant. I have 5 columns where data (numbers) will be entered daily. I have a calculation column where the numbers in the previous 5 columns are totaled (hidden column). I then have a last column that returns a specific word based on the value in the previous calculation column. I need the first column with room numbers, the hidden column with the calculation, and the last column with the word value to be locked so they cannot be manipulated. I can do all those things very easily. The problem is that a user will have to enter data in the middle 5 columns, and then sort the spreadsheet, and Excel will not allow a sort using the locked fields. Any suggestions? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Linking Groups of cells between workbooks | Excel Discussion (Misc queries) | |||
Excel locked cells | Excel Discussion (Misc queries) | |||
Unable to sort in Excel because of merged cells | Excel Discussion (Misc queries) | |||
I want to sort selected cells in Excel, not entire rows. | Excel Discussion (Misc queries) | |||
How do I sort letters before numbers in Excel? | Excel Discussion (Misc queries) |