Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming
|
|||
|
|||
Put Password Protection in Code
I have a workbook which will be used by several payroll clerks to enter
timesheet data for hundreds of employees. One sheet of the workbook is for data entry, and another sheet performs calculations based on the data. Among other things, the spreadsheet compiles the entry for each employee and clears the data entry form for the next employee. The compiled data is stored on a sheet titled "Compiled Totals." At the end of the day, each payroll clerk will generate a csv file based on the combined totals data. This data will be uploaded to a payroll system. This may not be the best way to do this, but what started out as a simple entry form has turned in to a mini database system. One of the requirements of my client is that the system be done in Excel so they can maintain it. My problem is that I'd like to protect the Combined Totals page so that the payroll clerks can't alter the data after they've entered it. But since I'm writing to the page, I can turn the protection on. I could programmatically protect and unprotect the page using something like the following, but is this really the best way to do this? I'm writing to the Combined Totals page after each employee is entered, so I would need to protect and protect the sheet mulitple times in the process, and I'm afraid that will be too taxing for the system. Any suggestions? ActiveWorkbook.ActiveSheet.Protect Password:=NN, DrawingObjects:=True, Contents:=True, Scenarios:=True Also, do you have to be on the active sheet to turn the protection on and off? I seem to be doing a lot of select statements in my code which is beginning to tax the system. Thanks. Connie |
#2
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming
|
|||
|
|||
Put Password Protection in Code
Also, do you have to be on the active sheet to turn the protection on
and off? No. Selects are almost never required. Selects are generated by recorded macros which rely on the user selecting and modifying things. Thiose things could just a seasily be modified without the select. In your example... Sheets("Combined Totals").Protect Password:=NN To get around teh fact taht you are modifying the page with code but you do not want the user to modify the page you can add the following to the protect Sheets("Combined Totals").Protect Password:=NN, UserInterfaceOnly:= True Which will allow code to modify the sheet but not the user... -- HTH... Jim Thomlinson "Connie" wrote: I have a workbook which will be used by several payroll clerks to enter timesheet data for hundreds of employees. One sheet of the workbook is for data entry, and another sheet performs calculations based on the data. Among other things, the spreadsheet compiles the entry for each employee and clears the data entry form for the next employee. The compiled data is stored on a sheet titled "Compiled Totals." At the end of the day, each payroll clerk will generate a csv file based on the combined totals data. This data will be uploaded to a payroll system. This may not be the best way to do this, but what started out as a simple entry form has turned in to a mini database system. One of the requirements of my client is that the system be done in Excel so they can maintain it. My problem is that I'd like to protect the Combined Totals page so that the payroll clerks can't alter the data after they've entered it. But since I'm writing to the page, I can turn the protection on. I could programmatically protect and unprotect the page using something like the following, but is this really the best way to do this? I'm writing to the Combined Totals page after each employee is entered, so I would need to protect and protect the sheet mulitple times in the process, and I'm afraid that will be too taxing for the system. Any suggestions? ActiveWorkbook.ActiveSheet.Protect Password:=NN, DrawingObjects:=True, Contents:=True, Scenarios:=True Also, do you have to be on the active sheet to turn the protection on and off? I seem to be doing a lot of select statements in my code which is beginning to tax the system. Thanks. Connie |
#3
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming
|
|||
|
|||
Put Password Protection in Code
Thanks a million. That's a big help.
Jim Thomlinson wrote: Also, do you have to be on the active sheet to turn the protection on and off? No. Selects are almost never required. Selects are generated by recorded macros which rely on the user selecting and modifying things. Thiose things could just a seasily be modified without the select. In your example... Sheets("Combined Totals").Protect Password:=NN To get around teh fact taht you are modifying the page with code but you do not want the user to modify the page you can add the following to the protect Sheets("Combined Totals").Protect Password:=NN, UserInterfaceOnly:= True Which will allow code to modify the sheet but not the user... -- HTH... Jim Thomlinson "Connie" wrote: I have a workbook which will be used by several payroll clerks to enter timesheet data for hundreds of employees. One sheet of the workbook is for data entry, and another sheet performs calculations based on the data. Among other things, the spreadsheet compiles the entry for each employee and clears the data entry form for the next employee. The compiled data is stored on a sheet titled "Compiled Totals." At the end of the day, each payroll clerk will generate a csv file based on the combined totals data. This data will be uploaded to a payroll system. This may not be the best way to do this, but what started out as a simple entry form has turned in to a mini database system. One of the requirements of my client is that the system be done in Excel so they can maintain it. My problem is that I'd like to protect the Combined Totals page so that the payroll clerks can't alter the data after they've entered it. But since I'm writing to the page, I can turn the protection on. I could programmatically protect and unprotect the page using something like the following, but is this really the best way to do this? I'm writing to the Combined Totals page after each employee is entered, so I would need to protect and protect the sheet mulitple times in the process, and I'm afraid that will be too taxing for the system. Any suggestions? ActiveWorkbook.ActiveSheet.Protect Password:=NN, DrawingObjects:=True, Contents:=True, Scenarios:=True Also, do you have to be on the active sheet to turn the protection on and off? I seem to be doing a lot of select statements in my code which is beginning to tax the system. Thanks. Connie |
#4
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming
|
|||
|
|||
Put Password Protection in Code
That worked as I said in my other post. By the way, I wanted to ask
you if it's possible to protect one cell in a sheet but allow code generated updates? I have a cell on the data entry sheet called Oracle Number. The way the payroll clerk is supposed to enter the Oracle Number is through a command button I've placed on the page. This is because the logic behind the command button includes checking that the oracle number is valid and that the clerk hasn't already entered the employee. What's happening is that some of the payroll clerks are going directly to the Oracle Number field and entering in the number, thus bypassing all of the checks and creating dups in the file. The oracle number field is an unlocked cell and there are other cells on the sheet which are unlocked (and should be, as I want the user to be able to update them.) Jim Thomlinson wrote: Also, do you have to be on the active sheet to turn the protection on and off? No. Selects are almost never required. Selects are generated by recorded macros which rely on the user selecting and modifying things. Thiose things could just a seasily be modified without the select. In your example... Sheets("Combined Totals").Protect Password:=NN To get around teh fact taht you are modifying the page with code but you do not want the user to modify the page you can add the following to the protect Sheets("Combined Totals").Protect Password:=NN, UserInterfaceOnly:= True Which will allow code to modify the sheet but not the user... -- HTH... Jim Thomlinson "Connie" wrote: I have a workbook which will be used by several payroll clerks to enter timesheet data for hundreds of employees. One sheet of the workbook is for data entry, and another sheet performs calculations based on the data. Among other things, the spreadsheet compiles the entry for each employee and clears the data entry form for the next employee. The compiled data is stored on a sheet titled "Compiled Totals." At the end of the day, each payroll clerk will generate a csv file based on the combined totals data. This data will be uploaded to a payroll system. This may not be the best way to do this, but what started out as a simple entry form has turned in to a mini database system. One of the requirements of my client is that the system be done in Excel so they can maintain it. My problem is that I'd like to protect the Combined Totals page so that the payroll clerks can't alter the data after they've entered it. But since I'm writing to the page, I can turn the protection on. I could programmatically protect and unprotect the page using something like the following, but is this really the best way to do this? I'm writing to the Combined Totals page after each employee is entered, so I would need to protect and protect the sheet mulitple times in the process, and I'm afraid that will be too taxing for the system. Any suggestions? ActiveWorkbook.ActiveSheet.Protect Password:=NN, DrawingObjects:=True, Contents:=True, Scenarios:=True Also, do you have to be on the active sheet to turn the protection on and off? I seem to be doing a lot of select statements in my code which is beginning to tax the system. Thanks. Connie |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VBA code error with Protection turned on - help please | Excel Discussion (Misc queries) | |||
Validate MsgBox Entry to Data in Cells | Excel Discussion (Misc queries) | |||
Protect Workbook vs Worksheet?? | Excel Worksheet Functions | |||
How can I remove workbook protection with no password? | Excel Discussion (Misc queries) | |||
Make Change Case in Excel a format rather than formula | Excel Worksheet Functions |