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.

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" <[email protected]> wrote in message
news:[email protected]...
>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.

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(xlCellTypeConstants).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.

this wouldn't work for me with the sheet protected

--


Gary


"cush" <[email protected]> wrote in message
news:[email protected]...
> 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(xlCellTypeConstants).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.

sorry. try this

Sub ClearData()
ActiveSheet.Unprotect "Password"
Range("TimeSheetData").SpecialCells(xlCellTypeConstants).ClearContents
ActiveSheet.Protect "Password"
End Sub



"Gary Keramidas" wrote:

> this wouldn't work for me with the sheet protected
>
> --
>
>
> Gary
>
>
> "cush" <[email protected]> wrote in message
> news:[email protected]...
> > 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(xlCellTypeConstants).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.
>
>
>

for each cell in Range(B:T).SpecialCells(xlConstants)
if cell.locked = False then
cell.ClearContents
end if
Next

--
Regards,
Tom Ogilvy


"bobreese" <[email protected]> wrote in message
news:[email protected]...
> 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.