Deleting same row on different sheets within a workbook

[Fidget]

Hello All,
I am creating a tracking system for assessment in school. If you can image I have six sheets which all relate to different areas of the curriculum. However, on every sheet I have the same name in the same cells. So, Sheet 1 might be Geography and Sheet 2 Art but each child's name always appear in the same cells. The first child's surname 'Bloggs' appears in cell M15 and his forename 'Joe' appears in N15. Image the third child down leaves. I want to create a button that will automatically delete the same row in each of the worksheets so that the child's data disappears. I have other worksheets in the workbook that are not connected and simply analyse the data so I wouldn't want these affected.
The trouble is that it won't be the same child (or row) every time so I would need to know how to select a new child. I am a bit of a novice so would need really clear instructions on how to do this. I think a could get as far as creating the command button if that's what I need but I would be lost after that. The workbook is protected so I don't know if this causes a problem?
Thank you for any help

[Marcol]

Maybe you don't need a macro for this.

An often overlooked fearture of Excel is the ability to enter/delete/amend data on multiple sheets simultaneously.

Try this

1/. Hold down the ctrl key and select the tabs you need to amend, say Sheet1,Sheet2, and Sheet3

2/. Release the key.

3/. Anything you do on the visible sheet will now be repeated in the other sheets.

4/. Once you select another tab the repeat facility is cancelled.

[Fidget]

Thank you for your answer. To be honest your method would be the easiest and if I were the only one using the workbook then I would just do this. The trouble is that lots of people will use the information and I won't always be there to help. I also intend to hide the sheet tabs - my staff manage to 'kill' anything where they have too much access. I'd like to make it as resilient and 'teacher proof' as possible lol

[Fidget]

Is there anyone who can help me with this. You'd make a huge difference to over 300 children!!

[jaslake]

Hi Fidget

I'd suggest using a UserForm for this. Populate (with Code) a ListBox or ComboBox on the UserForm with the Unique Child Names.

Have the User select a Name from the presented list, push a Button and delete that Child from all appropriate worksheets (with code). Marcol's suggestion could be automated to do this or simply loop through the appropriately Named sheets to do the delete.

If you need help on this please post your workbook.

Protection can/will affect this but there are ways around that.

[Fidget]

Thank you so much.

Please find attached workbook. It was much too big so I have had to delete loads of sheets which may affect functionality (sorry!)Basically there were lots of other sheets nut these would be the only ones where the whole row would need to be deleted. Reception.xlsm. To unprotect press ctrl + b and the password is run (lowercase).

If you look on the sheet named PSED you can see that column N and M contain children's names. These are populated from the 'pupil details' sheet. In effect, therefore, I would want to be able to delete the child's name from the 'Pupil Details' sheet and the same row disappear from the PSED,UW,CL,PH, LIT, EAD and MAT sheet.

Would it be possible to create something like an IF function where you would type the child's name in the box and it deletes the whole row in all of those sheets. Even better would be if you could just give the number of the row to delete and pressed a button and it did it for you?

Any help would be great x

[Fidget]

Reception.xlsmI attached it. Where does it appear - have you got it?? I'll have another go!

[Fidget]

Can anyone help - I know it's tricky but great fun. Don't abandon me jaslake - I have every faith x

[jaslake]

Hi Fidget

Haven't abandoned you...looking at your file and mocking up code as we speak.

[Fidget]

What a star x

[Fidget]

Thought I'd look up Atwood Lake as you suggest. Looks very nice. Do you enjoy all the water pursuits on offer. Very different to where I live - have a look too!

[Fidget]

Where are all you excel wizards??? I have been fascinated before- please do it again

[jaslake]

Hi Fidget

Don't get your shorts caught in your nickers...sit back...relax...have a smoke if you've got 'em.

We all have issues we need to tend to.

[jaslake]

Hi Fidget

Try the code in the attached...let me know of issues.

[Fidget]

That seems to work brilliantly but how do I replicate it in my much bigger file? Could you give me a bit of an idiots guide to how I do it. I can copy codes etc from the one you have sent me if that helps?

[Fidget]

Hi jaslake
Thank you so much for your work. I seem to be getting there . I have had to move the children's names down to row 15 in the Pupil Details sheet. I'm sure this will make a difference to the code. Could you let me know which bit please?

[Fidget]

Hi Again Jaslake,

I have transferred everything (I think!) into my own document. It is deleting the child's information from the Pupil Detail sheet but then in the other sheets it appears to be just dleting the information and leaving a hash ref error?

[jaslake]

Hi Fidget

I'm responding to your question

how do I replicate it in my much bigger file

First look in your "much bigger file and see if you have a UserForm called UserForm2.frm. If you do, let me know. If you don't proceed as follows:


Download and open the file from Post #14 Reception v7.xlsm
Get into VBA (Alt +F11)
Highlite UserForm2 (don't open it, merely Highlite the Name)
From the top Ribbon select File---->Export
You'll see a screen like this
Export Screen.jpg

Navigate to a location that you will REMEMBER (like your Desktop perhaps...don't care...just remember it).
Click Save.
Close the file Post #14 Reception v7.xlsm
Open your "much bigger file"
Get into VBA (Alt +F11)
From the top Ribbon select File---->Import
Navigate to the location that you REMEMBERED (like your Desktop perhaps...)
You'll see a screen like this
Import Screen.jpg

Highlite UserForm2.frm
Click Open
A UserForm called UserForm2.frm will be loaded into your "much bigger file"

Go back to the workbook--->Pupil Details Worksheet
Get into "Design Mode"...top Ribbon...Developer----->Design Mode---->Insert---->ActiveX Controls---->Command Button
Draw a Button on Pupil Details Worksheet
Right Click on the Button Select View Code
Modify the code to look like this (don't change the Procedure Name...simply add the line of code as indicated)
Code.jpg

Go back to Pupil Details Worksheet...Exit Design Mode.
Save the File.
Things should now work.

Let me know of issues.

[jaslake]

Hi Fidget

Yes, this will affect the Code

Please Login or Register  to view this content.

On which sheets did you make this move?

[jaslake]

Hi Fidget

Don't know

It is deleting the child's information from the Pupil Detail sheet but then in the other sheets it appears to be just dleting the information and leaving a hash ref error?

This does not happen on the Sample File. Post your workbook (with Code)...zip it if you have to.

[Fidget]

I have had to move the children's names down from row 13 to row 15 on the pupil details sheet. All other sheets remain the same ( they were already started on row 15).

[jaslake]

Hi Fidget

I could probably guess and just insert two new rows in Pupil Details Worksheet and it may or may not work. I'd prefer to see the new file structure and so I can test any changes.

Otherwise you can try changing this line of code from

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

and this line of code from

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

[Fidget]

I think it might be sorted!! You are a wonder x

[jaslake]

Hi Fidget

Good...let me know.

[Fidget]

The only problem that remains is that I can only do all of it when the sheet is unprotected. Is there any way to get it to work with the protection on? When I unprotect it people always seem to 'kill it' ha ha x

[jaslake]

Hi Fidget

Yes, we can do this

Is there any way to get it to work with the protection on

Have Family tonight for dinner so it'll be later tonight or tomorrow.

[Marcol]

Hmm?
I'm not entirely clear with what you are trying to do, but it seems to me at least, that you are going about it the hard way.

Okay, why go the easy way when there is a hard way? ...

1/. Never use merged cells on Data sheets, they cause nothing but grief ...

2/. Comand buttons are best avoided, where possible. Use the Worksheet_SelectionChange event instead.

3/. You have formulae and lists buried in your sheets, you'll soon forget where the are, and accidently delete/overwrite them!
Better to put Lookup Lists on a seperate Lookup Sheet.
In this cases the are not required, have a look at the new formulae, to see the differences.

4/. I'm not sure what this type of formula is for, so I've left them in for now.
What are you sub-totalling?

Please Login or Register  to view this content.

Maybe this could used instead?

Please Login or Register  to view this content.

Try this workbook.

Navigate to another sheet by choosing from the drop-down in B2, and return by selecting "Home"

Enter the name to be erased in cells H5:I5, then select "GO". (Try a few misspellings.)

There are a few other things to handle, but we can look at these later, if I'm on an acceptable track.

[jaslake]

Hi Fidget

Add this code to your Workbook.Open Event. This will allow you to delete rows on the protected sheets.

Please Login or Register  to view this content.

[Fidget]

Hi Jaslake,

Thank you but I'm not sure what you mean when you say add it to your Workbook.Open Event - please explain, I'm sorry to be a pain

[Fidget]

Thank you for your help too Marcol x

[Fidget]

Hi jaslake. I know how to get it to run when I open it but there's already lots in there and I'm getting a compile error/ambigious name message??

[jaslake]

Hi Fidget

On a copy of your file replace your Workbook_Open Event with this Code...you can uncomment other lines as required. Let's get it working for you. Replace the code...save the workbook...close it...reopen it...run the code.

Please Login or Register  to view this content.

Let me know of issues.

[Marcol]

To both.

Take a step back and think about it.

Anybody will eventually patch a badly designed workbook, but why?

[Fidget]

Jaslake,
Thank you for helping me with the my workbook. It is far from perfect (and I realise that) but you've ignored that and just helped me resolve the issues. Will try the last bit, thanks again x

[Fidget]

Delete Method of Range Class Failed

Hi Jaslake have completed the last bit and I'm not getting this message when I try to delete a pupil with the protection on. It works fine when the sheet id not protected. Any ideas?

[Fidget]

Marcol, Thank you for your comment. You are really good with excel and I'm not (unfortunately). I am, however, a really good teacher and I'm trying my best with the limited knowledge that I have. It would be lovely to have somebody with your expertise to make these things for me but I don't so I'm just cracking on - please be patient and thank you again

[jaslake]

Hi Fidget

It's been a long day and I'm a bit obtuse at this point but I don't follow...it seems to me these statements are a bit contradictory

I'm not getting this message when I try to delete a pupil with the protection on.

It works fine when the sheet id not protected

What is Sheet ID? I have no sheet called "sheet id" in the workbook I have...you've got me confused.

[Fidget]

Hey Jaslake - I think excel had fallen out with me!! All working fine now and I haven't changed anything. You excel at excel!! Hope you had a lovely meal with your family. Sorry for the confusion- think I'm a bit tired. Thanks againx

[jaslake]

Hi Fidget

Your Great Country gave us this Holiday to celebrate...so from me to you...Happy Thanksgiving...see you after the Holiday.

[processceci]

Hi there,

If i accidentally deleted a student, how to UNDO it?

[Marcol]

Hi processceci
You shouldn't really ask new questions in someone elses' thread, see the forum rules Rule 2.

However this is a good point that you raise.

VBa cancels the "Undo" facility in Excel when code is run, this can be dangerous, and for me at least is a good reason for avoiding VBa wherever possible.

Here are some options to try.

1/. Write your code to store the data as variables before deleting the rows/data. This would allow further code to recover data deleted in error. This will only work until the code stops, or the workbook is closed.

2/. Write the data to a very hidden sheet instead of deleting it. This way data is retained permanently, and can be recovered to the live sheets as required.

3/. Write the Data to an external file during the deleting operation.

4/. Save a backup file via the code as it goes.