I have a sheet which contains a several data validation dependant lists which
will be used amoung several people from distant locations.They will fill in
all the fields and submit the sheet to other departments. I would like to
create a control such as a button which would delete all data after
information has been submitted so a blank sheet can be used several times
without affecting the validation lists.Is this possible?

I'm not sure exactly what you're after but try this:
Sheets("Sheet1").Cells.ClearContents

Charles Chickering

jk wrote:
I have a sheet which contains a several data validation dependant lists which
will be used amoung several people from distant locations.They will fill in
all the fields and submit the sheet to other departments. I would like to
create a control such as a button which would delete all data after
information has been submitted so a blank sheet can be used several times
without affecting the validation lists.Is this possible?

Hi,
I want a reusable sheet without the user to manually clear out all the
entries and delete the data validation lists. How would i incorporate the
code into a button that i can place on the sheet?


"Die_Another_Day" wrote:

I'm not sure exactly what you're after but try this:
Sheets("Sheet1").Cells.ClearContents

Charles Chickering

jk wrote:
I have a sheet which contains a several data validation dependant lists which
will be used amoung several people from distant locations.They will fill in
all the fields and submit the sheet to other departments. I would like to
create a control such as a button which would delete all data after
information has been submitted so a blank sheet can be used several times
without affecting the validation lists.Is this possible?

You should unlock all the data input cells, leaving the labels and formulae
locked, protect the worksheet, and then just add code like DAD gave you to
the button.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"jk" wrote in message
...
Hi,
I want a reusable sheet without the user to manually clear out all the
entries and delete the data validation lists. How would i incorporate the
code into a button that i can place on the sheet?


"Die_Another_Day" wrote:

I'm not sure exactly what you're after but try this:
Sheets("Sheet1").Cells.ClearContents

Charles Chickering

jk wrote:
I have a sheet which contains a several data validation dependant
lists which
will be used amoung several people from distant locations.They will
fill in
all the fields and submit the sheet to other departments. I would like
to
create a control such as a button which would delete all data after
information has been submitted so a blank sheet can be used several
times
without affecting the validation lists.Is this possible?

Thanks Bob. I have a small problem in that if i protect the sheet so the
column labels wont be deleted as well, I am unable to run the code. I have
tried protecting and unprotecting that one cell with the control but it give
me a run time error.Can this control buttom use additional code to prevent
that or placed somewhere else?

"Bob Phillips" wrote:

You should unlock all the data input cells, leaving the labels and formulae
locked, protect the worksheet, and then just add code like DAD gave you to
the button.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"jk" wrote in message
...
Hi,
I want a reusable sheet without the user to manually clear out all the
entries and delete the data validation lists. How would i incorporate the
code into a button that i can place on the sheet?


"Die_Another_Day" wrote:

I'm not sure exactly what you're after but try this:
Sheets("Sheet1").Cells.ClearContents

Charles Chickering

jk wrote:
I have a sheet which contains a several data validation dependant
lists which
will be used amoung several people from distant locations.They will
fill in
all the fields and submit the sheet to other departments. I would like
to
create a control such as a button which would delete all data after
information has been submitted so a blank sheet can be used several
times
without affecting the validation lists.Is this possible?

Could you post the code that gives you the problem?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"jk" wrote in message
...
Thanks Bob. I have a small problem in that if i protect the sheet so the
column labels wont be deleted as well, I am unable to run the code. I have
tried protecting and unprotecting that one cell with the control but it
give
me a run time error.Can this control buttom use additional code to prevent
that or placed somewhere else?

"Bob Phillips" wrote:

You should unlock all the data input cells, leaving the labels and
formulae
locked, protect the worksheet, and then just add code like DAD gave you
to
the button.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"jk" wrote in message
...
Hi,
I want a reusable sheet without the user to manually clear out all the
entries and delete the data validation lists. How would i incorporate
the
code into a button that i can place on the sheet?


"Die_Another_Day" wrote:

I'm not sure exactly what you're after but try this:
Sheets("Sheet1").Cells.ClearContents

Charles Chickering

jk wrote:
I have a sheet which contains a several data validation dependant
lists which
will be used amoung several people from distant locations.They
will
fill in
all the fields and submit the sheet to other departments. I would
like
to
create a control such as a button which would delete all data
after
information has been submitted so a blank sheet can be used
several
times
without affecting the validation lists.Is this possible?

Sheets("Sheet1").Cells.ClearContents

The code works well in that it clears the entire sheet and it leaves my drop
down data validation lists intact so the sheet can be reused again and
again.The actual problem is i want to protect the column labels like you
instructed but after protecting the sheet, it basically disables the control
button as well.

"Bob Phillips" wrote:

Could you post the code that gives you the problem?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"jk" wrote in message
...
Thanks Bob. I have a small problem in that if i protect the sheet so the
column labels wont be deleted as well, I am unable to run the code. I have
tried protecting and unprotecting that one cell with the control but it
give
me a run time error.Can this control buttom use additional code to prevent
that or placed somewhere else?

"Bob Phillips" wrote:

You should unlock all the data input cells, leaving the labels and
formulae
locked, protect the worksheet, and then just add code like DAD gave you
to
the button.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"jk" wrote in message
...
Hi,
I want a reusable sheet without the user to manually clear out all the
entries and delete the data validation lists. How would i incorporate
the
code into a button that i can place on the sheet?


"Die_Another_Day" wrote:

I'm not sure exactly what you're after but try this:
Sheets("Sheet1").Cells.ClearContents

Charles Chickering

jk wrote:
I have a sheet which contains a several data validation dependant
lists which
will be used amoung several people from distant locations.They
will
fill in
all the fields and submit the sheet to other departments. I would
like
to
create a control such as a button which would delete all data
after
information has been submitted so a blank sheet can be used
several
times
without affecting the validation lists.Is this possible?

Come on, the whole code.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"jk" wrote in message
...
Sheets("Sheet1").Cells.ClearContents

The code works well in that it clears the entire sheet and it leaves my
drop
down data validation lists intact so the sheet can be reused again and
again.The actual problem is i want to protect the column labels like you
instructed but after protecting the sheet, it basically disables the
control
button as well.

"Bob Phillips" wrote:

Could you post the code that gives you the problem?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"jk" wrote in message
...
Thanks Bob. I have a small problem in that if i protect the sheet so
the
column labels wont be deleted as well, I am unable to run the code. I
have
tried protecting and unprotecting that one cell with the control but
it
give
me a run time error.Can this control buttom use additional code to
prevent
that or placed somewhere else?

"Bob Phillips" wrote:

You should unlock all the data input cells, leaving the labels and
formulae
locked, protect the worksheet, and then just add code like DAD gave
you
to
the button.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"jk" wrote in message
...
Hi,
I want a reusable sheet without the user to manually clear out all
the
entries and delete the data validation lists. How would i
incorporate
the
code into a button that i can place on the sheet?


"Die_Another_Day" wrote:

I'm not sure exactly what you're after but try this:
Sheets("Sheet1").Cells.ClearContents

Charles Chickering

jk wrote:
I have a sheet which contains a several data validation
dependant
lists which
will be used amoung several people from distant locations.They
will
fill in
all the fields and submit the sheet to other departments. I
would
like
to
create a control such as a button which would delete all data
after
information has been submitted so a blank sheet can be used
several
times
without affecting the validation lists.Is this possible?

This was the code given to me by DAD in the first message of this thread. It
may be small but since code and modules is absoutely new to me, it works to a
degree.I just need to clear out the sheet from the control button while
maintaining row "A" which has the column headings.

"Bob Phillips" wrote:

Come on, the whole code.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"jk" wrote in message
...
Sheets("Sheet1").Cells.ClearContents

The code works well in that it clears the entire sheet and it leaves my
drop
down data validation lists intact so the sheet can be reused again and
again.The actual problem is i want to protect the column labels like you
instructed but after protecting the sheet, it basically disables the
control
button as well.

"Bob Phillips" wrote:

Could you post the code that gives you the problem?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"jk" wrote in message
...
Thanks Bob. I have a small problem in that if i protect the sheet so
the
column labels wont be deleted as well, I am unable to run the code. I
have
tried protecting and unprotecting that one cell with the control but
it
give
me a run time error.Can this control buttom use additional code to
prevent
that or placed somewhere else?

"Bob Phillips" wrote:

You should unlock all the data input cells, leaving the labels and
formulae
locked, protect the worksheet, and then just add code like DAD gave
you
to
the button.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"jk" wrote in message
...
Hi,
I want a reusable sheet without the user to manually clear out all
the
entries and delete the data validation lists. How would i
incorporate
the
code into a button that i can place on the sheet?


"Die_Another_Day" wrote:

I'm not sure exactly what you're after but try this:
Sheets("Sheet1").Cells.ClearContents

Charles Chickering

jk wrote:
I have a sheet which contains a several data validation
dependant
lists which
will be used amoung several people from distant locations.They
will
fill in
all the fields and submit the sheet to other departments. I
would
like
to
create a control such as a button which would delete all data
after
information has been submitted so a blank sheet can be used
several
times
without affecting the validation lists.Is this possible?

But you said you got an error when protecting/unprotecting, not in the bit
DAD gave you. Without seeing all your macro code I can't begin to help. Why
the reticence?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"jk" wrote in message
...
This was the code given to me by DAD in the first message of this thread.
It
may be small but since code and modules is absoutely new to me, it works
to a
degree.I just need to clear out the sheet from the control button while
maintaining row "A" which has the column headings.

"Bob Phillips" wrote:

Come on, the whole code.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"jk" wrote in message
...
Sheets("Sheet1").Cells.ClearContents

The code works well in that it clears the entire sheet and it leaves
my
drop
down data validation lists intact so the sheet can be reused again and
again.The actual problem is i want to protect the column labels like
you
instructed but after protecting the sheet, it basically disables the
control
button as well.

"Bob Phillips" wrote:

Could you post the code that gives you the problem?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"jk" wrote in message
...
Thanks Bob. I have a small problem in that if i protect the sheet
so
the
column labels wont be deleted as well, I am unable to run the
code. I
have
tried protecting and unprotecting that one cell with the control
but
it
give
me a run time error.Can this control buttom use additional code to
prevent
that or placed somewhere else?

"Bob Phillips" wrote:

You should unlock all the data input cells, leaving the labels
and
formulae
locked, protect the worksheet, and then just add code like DAD
gave
you
to
the button.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing
direct)

"jk" wrote in message
...
Hi,
I want a reusable sheet without the user to manually clear out
all
the
entries and delete the data validation lists. How would i
incorporate
the
code into a button that i can place on the sheet?


"Die_Another_Day" wrote:

I'm not sure exactly what you're after but try this:
Sheets("Sheet1").Cells.ClearContents

Charles Chickering

jk wrote:
I have a sheet which contains a several data validation
dependant
lists which
will be used amoung several people from distant
locations.They
will
fill in
all the fields and submit the sheet to other departments.
I
would
like
to
create a control such as a button which would delete all
data
after
information has been submitted so a blank sheet can be
used
several
times
without affecting the validation lists.Is this possible?

Thanks for the understanding of my lack of knowledge in coding.The only code
that i see on sheet 1 is :

Private Sub CommandButton1_Click()
Sheets("Sheet1").Cells.ClearContents
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

End Sub
This is from worksheet, selection change option. All i would like is the
clearing of the sheet with the exception of row # 1 , columns "A" thru "G"
which contain the column headings.Does above help in any way?


"Bob Phillips" wrote:

But you said you got an error when protecting/unprotecting, not in the bit
DAD gave you. Without seeing all your macro code I can't begin to help. Why
the reticence?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"jk" wrote in message
...
This was the code given to me by DAD in the first message of this thread.
It
may be small but since code and modules is absoutely new to me, it works
to a
degree.I just need to clear out the sheet from the control button while
maintaining row "A" which has the column headings.

"Bob Phillips" wrote:

Come on, the whole code.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"jk" wrote in message
...
Sheets("Sheet1").Cells.ClearContents

The code works well in that it clears the entire sheet and it leaves
my
drop
down data validation lists intact so the sheet can be reused again and
again.The actual problem is i want to protect the column labels like
you
instructed but after protecting the sheet, it basically disables the
control
button as well.

"Bob Phillips" wrote:

Could you post the code that gives you the problem?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"jk" wrote in message
...
Thanks Bob. I have a small problem in that if i protect the sheet
so
the
column labels wont be deleted as well, I am unable to run the
code. I
have
tried protecting and unprotecting that one cell with the control
but
it
give
me a run time error.Can this control buttom use additional code to
prevent
that or placed somewhere else?

"Bob Phillips" wrote:

You should unlock all the data input cells, leaving the labels
and
formulae
locked, protect the worksheet, and then just add code like DAD
gave
you
to
the button.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing
direct)

"jk" wrote in message
...
Hi,
I want a reusable sheet without the user to manually clear out
all
the
entries and delete the data validation lists. How would i
incorporate
the
code into a button that i can place on the sheet?


"Die_Another_Day" wrote:

I'm not sure exactly what you're after but try this:
Sheets("Sheet1").Cells.ClearContents

Charles Chickering

jk wrote:
I have a sheet which contains a several data validation
dependant
lists which
will be used amoung several people from distant
locations.They
will
fill in
all the fields and submit the sheet to other departments.
I
would
like
to
create a control such as a button which would delete all
data
after
information has been submitted so a blank sheet can be
used
several
times
without affecting the validation lists.Is this possible?

Forget the protection, and try this

Private Sub CommandButton1_Click()
With Me.UsedRange
.Offset(1, 0).Clear
End With
End Sub


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"jk" wrote in message
...
Thanks for the understanding of my lack of knowledge in coding.The only
code
that i see on sheet 1 is :

Private Sub CommandButton1_Click()
Sheets("Sheet1").Cells.ClearContents
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

End Sub
This is from worksheet, selection change option. All i would like is the
clearing of the sheet with the exception of row # 1 , columns "A" thru "G"
which contain the column headings.Does above help in any way?


"Bob Phillips" wrote:

But you said you got an error when protecting/unprotecting, not in the
bit
DAD gave you. Without seeing all your macro code I can't begin to help.
Why
the reticence?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"jk" wrote in message
...
This was the code given to me by DAD in the first message of this
thread.
It
may be small but since code and modules is absoutely new to me, it
works
to a
degree.I just need to clear out the sheet from the control button
while
maintaining row "A" which has the column headings.

"Bob Phillips" wrote:

Come on, the whole code.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"jk" wrote in message
...
Sheets("Sheet1").Cells.ClearContents

The code works well in that it clears the entire sheet and it
leaves
my
drop
down data validation lists intact so the sheet can be reused again
and
again.The actual problem is i want to protect the column labels
like
you
instructed but after protecting the sheet, it basically disables
the
control
button as well.

"Bob Phillips" wrote:

Could you post the code that gives you the problem?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing
direct)

"jk" wrote in message
...
Thanks Bob. I have a small problem in that if i protect the
sheet
so
the
column labels wont be deleted as well, I am unable to run the
code. I
have
tried protecting and unprotecting that one cell with the
control
but
it
give
me a run time error.Can this control buttom use additional
code to
prevent
that or placed somewhere else?

"Bob Phillips" wrote:

You should unlock all the data input cells, leaving the
labels
and
formulae
locked, protect the worksheet, and then just add code like
DAD
gave
you
to
the button.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing
direct)

"jk" wrote in message
...
Hi,
I want a reusable sheet without the user to manually clear
out
all
the
entries and delete the data validation lists. How would i
incorporate
the
code into a button that i can place on the sheet?


"Die_Another_Day" wrote:

I'm not sure exactly what you're after but try this:
Sheets("Sheet1").Cells.ClearContents

Charles Chickering

jk wrote:
I have a sheet which contains a several data
validation
dependant
lists which
will be used amoung several people from distant
locations.They
will
fill in
all the fields and submit the sheet to other
departments.
I
would
like
to
create a control such as a button which would delete
all
data
after
information has been submitted so a blank sheet can be
used
several
times
without affecting the validation lists.Is this
possible?

Thanks Bob,
The code leaves row 1 intact from A thru G but it wipes out the data
validations lists that exist from row 2 to 100 columns A thru G. The first
code that DAD provided left those lists intact but wiped out the headings.Is
there a way to somehow freeze or exclude the row one headings from any
deletion?

"Bob Phillips" wrote:

Forget the protection, and try this

Private Sub CommandButton1_Click()
With Me.UsedRange
.Offset(1, 0).Clear
End With
End Sub


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"jk" wrote in message
...
Thanks for the understanding of my lack of knowledge in coding.The only
code
that i see on sheet 1 is :

Private Sub CommandButton1_Click()
Sheets("Sheet1").Cells.ClearContents
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

End Sub
This is from worksheet, selection change option. All i would like is the
clearing of the sheet with the exception of row # 1 , columns "A" thru "G"
which contain the column headings.Does above help in any way?


"Bob Phillips" wrote:

But you said you got an error when protecting/unprotecting, not in the
bit
DAD gave you. Without seeing all your macro code I can't begin to help.
Why
the reticence?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"jk" wrote in message
...
This was the code given to me by DAD in the first message of this
thread.
It
may be small but since code and modules is absoutely new to me, it
works
to a
degree.I just need to clear out the sheet from the control button
while
maintaining row "A" which has the column headings.

"Bob Phillips" wrote:

Come on, the whole code.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"jk" wrote in message
...
Sheets("Sheet1").Cells.ClearContents

The code works well in that it clears the entire sheet and it
leaves
my
drop
down data validation lists intact so the sheet can be reused again
and
again.The actual problem is i want to protect the column labels
like
you
instructed but after protecting the sheet, it basically disables
the
control
button as well.

"Bob Phillips" wrote:

Could you post the code that gives you the problem?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing
direct)

"jk" wrote in message
...
Thanks Bob. I have a small problem in that if i protect the
sheet
so
the
column labels wont be deleted as well, I am unable to run the
code. I
have
tried protecting and unprotecting that one cell with the
control
but
it
give
me a run time error.Can this control buttom use additional
code to
prevent
that or placed somewhere else?

"Bob Phillips" wrote:

You should unlock all the data input cells, leaving the
labels
and
formulae
locked, protect the worksheet, and then just add code like
DAD
gave
you
to
the button.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing
direct)

"jk" wrote in message
...
Hi,
I want a reusable sheet without the user to manually clear
out
all
the
entries and delete the data validation lists. How would i
incorporate
the
code into a button that i can place on the sheet?


"Die_Another_Day" wrote:

I'm not sure exactly what you're after but try this:
Sheets("Sheet1").Cells.ClearContents

Charles Chickering

jk wrote:
I have a sheet which contains a several data
validation
dependant
lists which
will be used amoung several people from distant
locations.They
will
fill in
all the fields and submit the sheet to other
departments.
I
would
like
to
create a control such as a button which would delete
all
data
after
information has been submitted so a blank sheet can be
used
several
times
without affecting the validation lists.Is this
possible?

Change Clear to ClearContents

Charles

jk wrote:
Thanks Bob,
The code leaves row 1 intact from A thru G but it wipes out the data
validations lists that exist from row 2 to 100 columns A thru G. The first
code that DAD provided left those lists intact but wiped out the headings.Is
there a way to somehow freeze or exclude the row one headings from any
deletion?

"Bob Phillips" wrote:

Forget the protection, and try this

Private Sub CommandButton1_Click()
With Me.UsedRange
.Offset(1, 0).Clear
End With
End Sub


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"jk" wrote in message
...
Thanks for the understanding of my lack of knowledge in coding.The only
code
that i see on sheet 1 is :

Private Sub CommandButton1_Click()
Sheets("Sheet1").Cells.ClearContents
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

End Sub
This is from worksheet, selection change option. All i would like is the
clearing of the sheet with the exception of row # 1 , columns "A" thru "G"
which contain the column headings.Does above help in any way?


"Bob Phillips" wrote:

But you said you got an error when protecting/unprotecting, not in the
bit
DAD gave you. Without seeing all your macro code I can't begin to help.
Why
the reticence?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"jk" wrote in message
...
This was the code given to me by DAD in the first message of this
thread.
It
may be small but since code and modules is absoutely new to me, it
works
to a
degree.I just need to clear out the sheet from the control button
while
maintaining row "A" which has the column headings.

"Bob Phillips" wrote:

Come on, the whole code.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"jk" wrote in message
...
Sheets("Sheet1").Cells.ClearContents

The code works well in that it clears the entire sheet and it
leaves
my
drop
down data validation lists intact so the sheet can be reused again
and
again.The actual problem is i want to protect the column labels
like
you
instructed but after protecting the sheet, it basically disables
the
control
button as well.

"Bob Phillips" wrote:

Could you post the code that gives you the problem?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing
direct)

"jk" wrote in message
...
Thanks Bob. I have a small problem in that if i protect the
sheet
so
the
column labels wont be deleted as well, I am unable to run the
code. I
have
tried protecting and unprotecting that one cell with the
control
but
it
give
me a run time error.Can this control buttom use additional
code to
prevent
that or placed somewhere else?

"Bob Phillips" wrote:

You should unlock all the data input cells, leaving the
labels
and
formulae
locked, protect the worksheet, and then just add code like
DAD
gave
you
to
the button.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing
direct)

"jk" wrote in message
...
Hi,
I want a reusable sheet without the user to manually clear
out
all
the
entries and delete the data validation lists. How would i
incorporate
the
code into a button that i can place on the sheet?


"Die_Another_Day" wrote:

I'm not sure exactly what you're after but try this:
Sheets("Sheet1").Cells.ClearContents

Charles Chickering

jk wrote:
I have a sheet which contains a several data
validation
dependant
lists which
will be used amoung several people from distant
locations.They
will
fill in
all the fields and submit the sheet to other
departments.
I
would
like
to
create a control such as a button which would delete
all
data
after
information has been submitted so a blank sheet can be
used
several
times
without affecting the validation lists.Is this
possible?

Thanks DAD, forgot to revert that. It was A change in one of my many
attempts at trying to understand what the Op WANTED.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Die_Another_Day" wrote in message
oups.com...
Change Clear to ClearContents

Charles

jk wrote:
Thanks Bob,
The code leaves row 1 intact from A thru G but it wipes out the data
validations lists that exist from row 2 to 100 columns A thru G. The
first
code that DAD provided left those lists intact but wiped out the
headings.Is
there a way to somehow freeze or exclude the row one headings from any
deletion?

"Bob Phillips" wrote:

Forget the protection, and try this

Private Sub CommandButton1_Click()
With Me.UsedRange
.Offset(1, 0).Clear
End With
End Sub


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"jk" wrote in message
...
Thanks for the understanding of my lack of knowledge in coding.The
only
code
that i see on sheet 1 is :

Private Sub CommandButton1_Click()
Sheets("Sheet1").Cells.ClearContents
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

End Sub
This is from worksheet, selection change option. All i would like is
the
clearing of the sheet with the exception of row # 1 , columns "A"
thru "G"
which contain the column headings.Does above help in any way?


"Bob Phillips" wrote:

But you said you got an error when protecting/unprotecting, not in
the
bit
DAD gave you. Without seeing all your macro code I can't begin to
help.
Why
the reticence?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"jk" wrote in message
...
This was the code given to me by DAD in the first message of
this
thread.
It
may be small but since code and modules is absoutely new to me,
it
works
to a
degree.I just need to clear out the sheet from the control
button
while
maintaining row "A" which has the column headings.

"Bob Phillips" wrote:

Come on, the whole code.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing
direct)

"jk" wrote in message
...
Sheets("Sheet1").Cells.ClearContents

The code works well in that it clears the entire sheet and
it
leaves
my
drop
down data validation lists intact so the sheet can be reused
again
and
again.The actual problem is i want to protect the column
labels
like
you
instructed but after protecting the sheet, it basically
disables
the
control
button as well.

"Bob Phillips" wrote:

Could you post the code that gives you the problem?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing
direct)

"jk" wrote in message
...
Thanks Bob. I have a small problem in that if i protect
the
sheet
so
the
column labels wont be deleted as well, I am unable to
run the
code. I
have
tried protecting and unprotecting that one cell with the
control
but
it
give
me a run time error.Can this control buttom use
additional
code to
prevent
that or placed somewhere else?

"Bob Phillips" wrote:

You should unlock all the data input cells, leaving
the
labels
and
formulae
locked, protect the worksheet, and then just add code
like
DAD
gave
you
to
the button.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if
mailing
direct)

"jk" wrote in message

...
Hi,
I want a reusable sheet without the user to manually
clear
out
all
the
entries and delete the data validation lists. How
would i
incorporate
the
code into a button that i can place on the sheet?


"Die_Another_Day" wrote:

I'm not sure exactly what you're after but try
this:
Sheets("Sheet1").Cells.ClearContents

Charles Chickering

jk wrote:
I have a sheet which contains a several data
validation
dependant
lists which
will be used amoung several people from distant
locations.They
will
fill in
all the fields and submit the sheet to other
departments.
I
would
like
to
create a control such as a button which would
delete
all
data
after
information has been submitted so a blank sheet
can be
used
several
times
without affecting the validation lists.Is this
possible?