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.

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.

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.

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.

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.

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.