Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I Clear ALL unprotected data entry fields in a protected sh
I have desinged a very simple Excel Worksheet for a friend of mine. She wants
to enter all the hours from a time card on each employee. It has two weeks of data to enter so she can easily figure her payroll for each employee. It looks something like this: Column 1 Column 2 Colum 3 etc. AM Time In Data Entry Same for next 6 employees AM Time Out Data Out Same for next 6 employees Total AM Protected Formula PM Time In Data Entry Same for next 6 employees PM Time out Data Entry Same for next 6 employees Total PM Protected Formula Totoal Hours Protected Formula This is repeated for 2 weeks. All Fields are protected and locked except for Data Entry Fields. My problem is, I would like to clear the sheet after the payroll has been made and printed. If you have the work sheet protected, I have no way to clear all the Data Entry fields at once. The only way I can do it is to dlete each field individually as selecting all will not work on the unproted Data Entry fields. Please help. I have completed all the work except for this one item. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I Clear ALL unprotected data entry fields in a protected sh
select the cells and give them a range name, i just used data
then this will clear those cells ActiveSheet.Range("data").Clear -- Gary "bobreese" wrote in message ... I have desinged a very simple Excel Worksheet for a friend of mine. She wants to enter all the hours from a time card on each employee. It has two weeks of data to enter so she can easily figure her payroll for each employee. It looks something like this: Column 1 Column 2 Colum 3 etc. AM Time In Data Entry Same for next 6 employees AM Time Out Data Out Same for next 6 employees Total AM Protected Formula PM Time In Data Entry Same for next 6 employees PM Time out Data Entry Same for next 6 employees Total PM Protected Formula Totoal Hours Protected Formula This is repeated for 2 weeks. All Fields are protected and locked except for Data Entry Fields. My problem is, I would like to clear the sheet after the payroll has been made and printed. If you have the work sheet protected, I have no way to clear all the Data Entry fields at once. The only way I can do it is to dlete each field individually as selecting all will not work on the unproted Data Entry fields. Please help. I have completed all the work except for this one item. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I Clear ALL unprotected data entry fields in a protected sh
With Gary's solution you will need to select a large number of individual
cells and then name the aggregate ( a bit of a nusance). The following will ease the task. First select the entire table (without any labels - just the data) and name it, something like TimeSheetData Then use the following macro: Option Explicit Sub ClearData() Range("TimeSheetData").SpecialCells(xlCellTypeCons tants).ClearContents End Sub This will clear all non-formula cells. "bobreese" wrote: I have desinged a very simple Excel Worksheet for a friend of mine. She wants to enter all the hours from a time card on each employee. It has two weeks of data to enter so she can easily figure her payroll for each employee. It looks something like this: Column 1 Column 2 Colum 3 etc. AM Time In Data Entry Same for next 6 employees AM Time Out Data Out Same for next 6 employees Total AM Protected Formula PM Time In Data Entry Same for next 6 employees PM Time out Data Entry Same for next 6 employees Total PM Protected Formula Totoal Hours Protected Formula This is repeated for 2 weeks. All Fields are protected and locked except for Data Entry Fields. My problem is, I would like to clear the sheet after the payroll has been made and printed. If you have the work sheet protected, I have no way to clear all the Data Entry fields at once. The only way I can do it is to dlete each field individually as selecting all will not work on the unproted Data Entry fields. Please help. I have completed all the work except for this one item. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I Clear ALL unprotected data entry fields in a protected sh
this wouldn't work for me with the sheet protected
-- Gary "cush" wrote in message ... With Gary's solution you will need to select a large number of individual cells and then name the aggregate ( a bit of a nusance). The following will ease the task. First select the entire table (without any labels - just the data) and name it, something like TimeSheetData Then use the following macro: Option Explicit Sub ClearData() Range("TimeSheetData").SpecialCells(xlCellTypeCons tants).ClearContents End Sub This will clear all non-formula cells. "bobreese" wrote: I have desinged a very simple Excel Worksheet for a friend of mine. She wants to enter all the hours from a time card on each employee. It has two weeks of data to enter so she can easily figure her payroll for each employee. It looks something like this: Column 1 Column 2 Colum 3 etc. AM Time In Data Entry Same for next 6 employees AM Time Out Data Out Same for next 6 employees Total AM Protected Formula PM Time In Data Entry Same for next 6 employees PM Time out Data Entry Same for next 6 employees Total PM Protected Formula Totoal Hours Protected Formula This is repeated for 2 weeks. All Fields are protected and locked except for Data Entry Fields. My problem is, I would like to clear the sheet after the payroll has been made and printed. If you have the work sheet protected, I have no way to clear all the Data Entry fields at once. The only way I can do it is to dlete each field individually as selecting all will not work on the unproted Data Entry fields. Please help. I have completed all the work except for this one item. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I Clear ALL unprotected data entry fields in a protecte
sorry. try this
Sub ClearData() ActiveSheet.Unprotect "Password" Range("TimeSheetData").SpecialCells(xlCellTypeCons tants).ClearContents ActiveSheet.Protect "Password" End Sub "Gary Keramidas" wrote: this wouldn't work for me with the sheet protected -- Gary "cush" wrote in message ... With Gary's solution you will need to select a large number of individual cells and then name the aggregate ( a bit of a nusance). The following will ease the task. First select the entire table (without any labels - just the data) and name it, something like TimeSheetData Then use the following macro: Option Explicit Sub ClearData() Range("TimeSheetData").SpecialCells(xlCellTypeCons tants).ClearContents End Sub This will clear all non-formula cells. "bobreese" wrote: I have desinged a very simple Excel Worksheet for a friend of mine. She wants to enter all the hours from a time card on each employee. It has two weeks of data to enter so she can easily figure her payroll for each employee. It looks something like this: Column 1 Column 2 Colum 3 etc. AM Time In Data Entry Same for next 6 employees AM Time Out Data Out Same for next 6 employees Total AM Protected Formula PM Time In Data Entry Same for next 6 employees PM Time out Data Entry Same for next 6 employees Total PM Protected Formula Totoal Hours Protected Formula This is repeated for 2 weeks. All Fields are protected and locked except for Data Entry Fields. My problem is, I would like to clear the sheet after the payroll has been made and printed. If you have the work sheet protected, I have no way to clear all the Data Entry fields at once. The only way I can do it is to dlete each field individually as selecting all will not work on the unproted Data Entry fields. Please help. I have completed all the work except for this one item. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I Clear ALL unprotected data entry fields in a protected sh
for each cell in Range(B:T).SpecialCells(xlConstants)
if cell.locked = False then cell.ClearContents end if Next -- Regards, Tom Ogilvy "bobreese" wrote in message ... I have desinged a very simple Excel Worksheet for a friend of mine. She wants to enter all the hours from a time card on each employee. It has two weeks of data to enter so she can easily figure her payroll for each employee. It looks something like this: Column 1 Column 2 Colum 3 etc. AM Time In Data Entry Same for next 6 employees AM Time Out Data Out Same for next 6 employees Total AM Protected Formula PM Time In Data Entry Same for next 6 employees PM Time out Data Entry Same for next 6 employees Total PM Protected Formula Totoal Hours Protected Formula This is repeated for 2 weeks. All Fields are protected and locked except for Data Entry Fields. My problem is, I would like to clear the sheet after the payroll has been made and printed. If you have the work sheet protected, I have no way to clear all the Data Entry fields at once. The only way I can do it is to dlete each field individually as selecting all will not work on the unproted Data Entry fields. Please help. I have completed all the work except for this one item. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I clear 'form' content from an unprotected cell without mac | Excel Worksheet Functions | |||
CLear Data Entry Form Fields | Excel Discussion (Misc queries) | |||
How to clear content from unprotected Field | Excel Worksheet Functions | |||
Macro to clear contents of unprotected cells AND drop down boxes | Excel Discussion (Misc queries) | |||
Copy of protected document was unprotected in 97 and protected in 2002 | Excel Programming |