Delete all

Attached is an edited version which deals with the deleting request.

Re adding the 17 sheets- do you essentially want to duplicate the 'FRONT' sheet 17 times? Are these to be named according to some convention? If you can clarify precise intentions, I might get a chance to look at the coding.

OK- just to confirm my understanding of what you're looking to achieve with the added sheets.

1. Do you want a button to automatically add these sheets? If yes, is the button to be displayed somewhere on the 'FRONT' sheet? If no, what event is to trigger the sheet addition procedure?
2. The added sheets are to link back to individual rows on the FRONT sheet-so, Sheet1 has formulas linking to row 5 of the FRONT sheet, sheet 2 links to row 6 etc. Are the links only those that you have included in the workbook version 3a i.e. you've got formulas in sheet1 cells C5,E5,G5,C6, E8 and G8. Am I correct to assume that no other formulas are required in these added sheets?
3. If you click the 'Delete all' button on the FRONT sheet for a specific row, is the linked sheet to also be deleted?
4. Do you want the added sheets to be hidden until the 'Handover' hyperlink is clicked?
5. Is there a naming convention to be used for each added sheet e.g. Details 1, Details 2 etc?

OK.

I think that the attached version is getting close to what you're looking for but I need to clarify this statement:

"the sheet remains but it should also delete the data in the no colour cells. e.g. for each sheet linked to a row, cell content for cell, C4,E4,G4,C6,E6,G6,C8,E8,G8, merged cells (C-G) in rows 10,12,14,16,18,20,22 and 24"

In the sample sheets you supplied, there were only formulae in cells C4,E4,G4,C6,E8,G8. You seem to indicate that there are going to be formulas in other cells.
To accelerate insertion of formulas, I've added a button at bottom left of the FRONT sheet which uses a macro to automatically add the relevant formulas to each of the 18 sheets added.
You can test this button's functionality by deleting the sample formulas you show on the added sheets and then clicking the button to reinsert them.
However, at present, the macro only adds links to the 6 cells your sample supplied. If there are additional cells with formula, please upload a version with 1 sample sheet showing ALL formulas.

Please note that when you click the 'Delete all' button on the FRONT sheet, the linked sheets will display blanks for any cells linking back to row of the FRONT sheet from which data was deleted.
This seems to satisfy your requirement to delete the data in the no colour cells. Let me know if I'm misunderstanding.

OK.

My head is spinning a bit after this so I hope this version has you sorted!

I added a code which allows you to show or hide the Add formulas to sheets button- to show the button, type "show" in cell C1 of the FRONT sheet (no inverted commas, case sensitive). To hide it, type "hide" (same rules).

The 'Delete all button is generally visible at the extreme right of the FRONT worksheet, occasionally a little cut off but I ended up not grasping the nature of how it has to be positioned.
Clicking it deletes any free text from the relevant sheet as well as the specified content on the FRONT sheet.

I misinterpreted this sentence: "as to the delete all button, I could do with that being visible at the end of each row again with the protective pop up." I understood it to require that the button was visible at the end of the rightmost visible cell. Column AE won't always be visible unless you scroll across. In any case, I've changed the code to have the button automatically stay in column AE.

What page / what cells are you trying to change that triggers the error? I changed a few random cells on the FRONT sheet and on one of the added sheets- all went OK here.

Actually- just remembered that I had commented out one of your timer related codes as it was not allowing me to test something. Apologies! I've uncommented it now in the attached workbook.

Would positioning the 'Delete all' button to the left of the screen (for example on the 'Room' cells) be OK? That way, it would always be visible as the user selects any relevant row. Otherwise, there's a trickyish coding needed to keep it visible on the right of the screen (was thinking earlier about it and might have cracked it but unsure).

OK.

The 'Delete all' button could be turned into a smaller shape (like a red circle or square with a white coloured 'X' in it, the well understood concept for 'a delete action). This could be positioned to the right edge of the cell with the patient's name. It would only be visible while the user is on the relevant row and if small enough, wouldn't impeded the entering of a name in that cell. This would avoid the need to edit the delete code (not a huge task but still a task). The button would however not have the 'Delete all' text- just a white coloured 'X' but I think that it would be understood in general that it performs a delete action.

If that doesn't work, could you would upload a workbook with the new column which will hold the 'Delete all' button in whatever place you find works. I can then modify the code as required.

OK- here's hoping!

Does the attached work for you?

Good stuff.

Leave a comment here if you find something that needs sorting.

Have fun with the use of the workbook!

Hi Steve,

Attached should do those extra deletions.

Happy New Year :-)

Are you sure that cell E6 isn't being deleted? I cannot replicate this error using the workbook ver3c uploaded above and the code definitely includes cell E6 in the free-text deletion procedure. It's also very unusual that some sheets are seeing the content deleted while others are not.

Hi deadlyduck
well just when I thought it was good to go, the boss adds a change.
I've been asked to add another row (28) for the delete all option in the sheets - Row 28 C to G

to save me bothering you all the time, could you do me a step by step guide to add/delete to the delete all command?

Langworth PSAG 2024 ver 1.xlsm

Thanks in advance

Hello Steve,

It's actually very easy to update the cells to be deleted on the individual sheets. The relevant code requiring update is in the 'TestDeleteAll' module and the specific macros is called 'Sub DeleteFreeText(TargetSheet As Worksheet)'.
To add additional cells to be deleted, just use the same format as is shown in the With statement- so, for example, you want to delete cells C28:G28 so you'd add a new line as follows just before the 'End with' statement:

    .Range("c28").MergeArea.ClearContents

That's it!

However, I see that the 'Front' sheet (codename is Sheet1) is protected with a password. To allow VBA to do deletions on this sheet, you will need to add the following code to the 'DeleteRowData' macro, just before the line that starts

Set rngToDelete1 =....

The required line to add is

Sheet1.unprotect password:="TypeInThePasswordYouUseToProtectSheet1Here"

Then, at the very end of this same macro, add in the following line of code to reprotect sheet1: Sheet1.protect password:="TypeInThePasswordYouUseToProtectSheet1Here"

Hi deadlyduck
Management has struck again, in the time since we last communicated my office is no more, it's in the midst of a refit so I have not rolled this out yet.
In the mean time, "management" have asked for an additional 2 columns (X and Y) to the front sheet, my question is how to add them to the delete all command for the front sheet?

Langworth PSAG 2024.xlsm