Deleting cell content based on entry in one cell

[mikerules]

I have a sheet that I fill out with customer data then print and start over with the next customer. This requires me to tab and delete through the sheet before starting the next entry and I am wondering if there is some way to auto clear the unlocked cells based on a single entry IE when we entered new data in the 1st field this would clear the unlocked cells and make them ready for new data?

[Jakobshavn]

Very easy.........post a small sample

[mikerules]

I can't post the worksheet but basically I have a worksheet that is very detailed and it has about 20 randomly placed cells that are unlocked and require entry for each new customer. There are a few cells that are unlocked but don't change with each customer. The first point of entry is the Client name and then all the unlocked cells that follow are the ones I want to clear and make ready for new information.

[newdoverman]

I used to do this with a cash receipt form with many hundreds of entries. I had a template sheet.

1. Create a template worksheet. You will only copy this worksheet and never fill it in.

2. Copy the template worksheet and paste into a new worksheet.

3. When the worksheet is filled and printed and you want to start again, select the whole worksheet and delete the contents.

4. Go to the TEMPLATE and copy it

5. Return to the "working" worksheet and paste.


This whole process can be automated by recording a macro to do the work.

[mikerules]

I would prefer not to copy and paste. Is a macro the only other solution? I don't think I am to fluent in macro's

[newdoverman]

You don't have to be fluent in VBA to do this. Start the Macro Recorder and record the actions to copy the template worksheet then paste into your work worksheet. Assign a keyboard shortcut or create a button attached to the macro to make the macro run and you are finished


Can you post your workbook? It would be easier to help you.

Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[mikerules]

Thanks for your help. I did create a macro but one problem is that most of the cells are merged and the macro won't work. Do I need to put both halves of all merged cells in the macro? Many of the merged cells are multiple cells merged together so I guess I need to know if I need to put all the cells into the macro or I guess I can do it by range where that is possible?

[newdoverman]

If you are using Merge and Center, for single rows, it is better to select the cells that you would have used Merge and Center and right click and choose Format Cells, click on the Alignment tab, Horizontal, and select Center across Selection. This will have the same look as horizontally merged cells but without a lot of the trouble caused by them.

You might want to re-record the macro and start off by un-protecting the worksheet then go through the process of clearing the cells then re-protect the worksheet to finish.

[mikerules]

I am going through the alignment process and a problem is this

I have 10 cells across that before were merged 2 together and with a border thus creating 5 across. I undid the merge and selected 2 cells and centered section as you suggested. When I enter data in the most left cell it centers it across all 10 when I only want it in the 2. after I enter the first data the 10 cells have the data in the center and the whole thing has a border. Is there a way for this not to happen? I did highlight just the 2 cells before I aligned them

I should add that it is happening because the whole section is "centred across section" even though I did them 2 cells at a time. If I just make the alignment general in the 3rd one then the first 2 hold the number. The problem is I do need the whole row set up side by side

[mikerules]

"You might want to re-record the macro and start off by un-protecting the worksheet then go through the process of clearing the cells then re-protect the worksheet to finish".

If I have taken all the merging out related to the cells I put in the macro will it not work without doing the above?

[newdoverman]

Without seeing what you are working with, I don't think that I can advise you further as I would just be guessing at what the problem really is.

[mikerules]

Here is a version. If you click C10 you will see the problem

[mikerules]

Also when I closed the file I lost the macro. Is there a reason it would not save?

[newdoverman]

I think that I see what you are referring to. I recorded a macro that deleted the contents of each unlocked cell and will run with the press of the button.

Does this get close at all to what you want? The worksheet is protected --- without a password.

[newdoverman]

You have to save the workbook as Macro Enabled type (xlsm)

[mikerules]

OK great thanks. Did you have any idea how to fix the selection so that the entry does not go into the full row? Will I need to enter a blank cell between each two?

[mikerules]

A question about macros. If I do want my clear button and I have merged cells will it work if I enter all of the cell ranges in the merged area. So lets say L4 M4 N4 are merged with the data in the merged cell showing as L4. If I just put L4 in the macro it wont work but if I enter the range L4:N4 will the macro then clear the range?

[newdoverman]

Re-record the macro and try skipping past that cell.

It is easier to re-record than to try and edit the macro if you haven't any VBA experience programming. I have tried it myself and messed things up.

[mikerules]

Thanks for all your help I got everything resolved

[newdoverman]

Thank you for the feedback.

Good luck with your project.

[newdoverman]

Thank you for the feedback.

Good luck with your project.

I just thought of using Active X text boxes...linked to cells to overcome the merging of cells in rows 10 and 11. The only thing is that the TAB key will not go from box to box but the whole cell is available.

[mikerules]

Thanks for your additional thoughts on this. From an entry stand point I prefer to have the cells merged so in the macro on each line I entered the range within the merged area if it was merged and this worked and allowed the clear button to do its job. I'm learning as I go here Thanks again ...very much