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?