• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Delete on a different sheet

Eric Carolus

New Member
Hi Folks
I have two sheets in the attached workbook.
These are called Teachers and Allocation.
I have a dynamic range called Educators on the sheet called Teachers.
This dynamic range is also the LIST for the sheet Allocation.
Click on the columns E9:E38, G9:G38, I9:E38, etc, etc.
On the sheet Allocation, the first three educators had been selected.

Now the problem, whenever a teacher is DELETED on the sheet called Teachers,
I wish for the educator name to be removed on the sheet Allocation (E9:AP38)
(Of course the educators name will be removed from the drop down list in each cell on the sheet Allocation)

I have no idea where to start.
Can someone help please?

Thanks in advance.
Eric
 

Attachments

  • Forum2.xlsm
    82.7 KB · Views: 7
When you talk about a teacher being deleted from the Teachers worksheet, I suppose you mean that someone will have deleted the entire row, right?

I've had to handle things like this before, and I've never yet found an event that can spot a deletion and tell me what used to be there. Off-hand I can think of two ways to deal with this. There are probably more—there always are :):

1) You can save a list of the teachers in some other location, and compare it whenever there's a change in Teachers. The backup list could be in a separate file, but it's probably easiest to have it in a hidden worksheet. Of course, if someone deletes the row in Teachers, you'll want to delete it not only in Allocation but also in the backup list. And this method leads you to wonder whether you want also to see if someone adds a new row to Teachers, and if so add it to the list in the hidden worksheet and in Allocation.

2) I think this method is simpler: Instead of letting a user just delete a row in Teachers, declare that the official method for deleting a teacher is to put some command string ('D', perhaps) in some other column of that row. If the user puts 'D' in A4, for example, your Worksheet_Change method could detect it, delete row 4 in Teachers and find and delete the corresponding row in Allocation as well.

If you're afraid that some user will absent-mindedly delete a teacher row instead of putting 'D' in column A, it's a sensible fear. (Even if you're the only user of the workbook, the day will come when you'll do that without thinking about it.) You can use worksheet protection against that possibility; I think worksheet protection can be set up to allow deleting values but not ranges.
 
Hi Folks

I had given it another shot.

Based on many searches I came up with the code that I
placed in a module, but a solution still eludes me.

I include the changed workbook.

Thanks in advance.

Eric
 

Attachments

  • Forum2.xlsm
    85.8 KB · Views: 4
Hi BobBridges

The Educators list is dynamic. Whoever handles the Teacher's list must have a free hand in adding or deleting teachers
to the worksheet called Teachers.
1). The list of teachers, namely "Educators" is dynamic. Adding it to a hidden list would not help.
2). The teacher should not ONLY be deleted in THE CORRESPONDING ROW. I must be deleted/erased ALL OVER the range F9:OP38 in the sheet Allocation.

I have posted a new workbook where I posted the macro in a module, to no avail.
I had searched/Googled and found a starting point for a solution in a video at:
.

But I cannot make it work. See the module in my previous post.

Thanks in advance.

Eric
 
I may not have been clear. I don't think you understood me—or maybe I've misunderstood you.

1) You say the list in the Teachers worksheet is dynamic, which I take to mean that it will change from time to time. I get that; that's why I said that if you use my first (and more complicated) solution, your code must update that hidden, backup list of teachers whenever the user changes the list in Teachers. When a teacher is added to Teachers, your code must detect that and add it to the backup list; when a teacher is deleted, your code must compare the list in Teachers to the backup list, figure out which teacher was deleted and update the backup list also. That's why this solution is more complicated than my second one; it can work, but it's harder to code. When you say that adding a new teacher to the hidden list wouldn't help, I think you didn't really understand what I was suggesting.

2) My second solution is simpler to program. I said there that the deleted teacher has to be deleted from "the corresponding row" in Allocation, and you said it has to be deleted throughout the range in Allocation; unless you misunderstood me again, I'm thinking you must mean that the teacher may appear in more than one Allocation row. But that's a mere detail; if you have to find (and delete) the teacher in more than one row, fine, but the problem you're asking about is not how to delete a row from allocation but how to figure out what teacher was deleted...right? That's the problem I'm trying to address.

If you use the second method (which, as I said, is simpler), the users can still delete any teacher; they just have to be trained to delete it not by deleting the row but by putting a 'D' (or whatever you choose) in a column on that row in the Teachers workbook. That allows your code to see which teacher is to be deleted, without having to maintain a backup list.

If this is still unclear, maybe we need to back up and define your question more carefully; I may be missing something.
 
Hi BobBridges

Thanks for your advice.

My experience in coding is not not that good at all.
But let me try explain again.
An I am thinking in terms of your proposed solution #2.

The list Called 'Educators' is dynamic. It feeds the drop down in the cells on the sheet called 'Allocation'.
I think I phrased my problem incorrectly.

On the sheet called 'Teachers' the teachers/educators (labelled, i.e. FG, JK, etc) will be DELETED from the DYNAMIC list.

On the sheet 'Allocation' the CELL CONTENTS must be CLEARED.
E.g. the teacher PN in the dynamic list called 'Educators' on the sheet 'Teachers' will be/must be DELETED.

On the sheet 'Allocation' ALL CELLS (in the range F9:OP38) with content PN must be CLEARED.
Rows must not be deleted.

I hope this a bit clearer.
Thanks again for your patience.

Eric
 
You did mention a drop-down list in your first post, but I didn't notice at the time that the drop-down list would be on the Allocation worksheet. I think that's part of where I went wrong.

Let's start over, then, and define a few terms. I see that you have two worksheets named Teachers and Allocation.

You also mention "a list called Educators". I take this to be the list of teachers (or rather of teachers' initials) on the Teachers worksheet.

You speak of a "DYNAMIC list". I suppose that term, too, refers to the list of teacher on the Teachers worksheet, and I take you to mean, by "dynamic", that the list may change from time to time. I shrug indifferently at this point; sure, it changes, that's what we're talking about. But since you place such emphasis on "dynamic" I may be dismissing that point too casually; correct me if I've overlooked something.

And yes, I now understand that when a user deletes a teacher from the Teachers worksheet—teacher XYZ, let's say—you want the workbook to automatically find XYZ anywhere in the range Allocation!F9:OP80 and clear the contents of (not delete) those cells.

Am I doing better now?

So if you can get your users not to delete rows in Teachers, but to mark the to-be-deleted row with some code, then you can write a program for the Teachers worksheet to spot the delete code and behave accordingly. That behavior:
1) Delete that row (XYZ) in Teachers
2) Search through Allocation looking for XYZ.
3) Wherever XYZ is found in Allocation, clear cells on that row.

If I'm closer, then on to the next step: I'm guessing you already know how to write the program that spots the delete code (whatever delete code you decide upon) and deletes the row in Teachers. What you don't know how to do is search for it in Allocation. Am I right?
 
Hi BobBrides

In reply:

You did mention a drop-down list in your first post, but I didn't notice at the time that the drop-down list would be on the Allocation worksheet. I think that's part of where I went wrong.

Understand
, no problem

I see that you have two worksheets named Teachers and Allocation.
Yes

You also mention "a list called Educators". I take this to be the list of teachers (or rather of teachers' initials) on the Teachers worksheet.
Yes, basically a named, dynamic range.

You speak of a "DYNAMIC list". I suppose that term, too, refers to the list of teacher on the Teachers worksheet, and I take you to mean, by "dynamic", that the list may change from time to time.

I shrug indifferently at this point; sure, it changes, that's what we're talking about. But since you place such emphasis on "dynamic" I may be dismissing that point too casually; correct me if I've overlooked something.

The list (named, dynamic list called Educators on the worksheet Teachers

And yes, I now understand that when a user deletes a teacher from the Teachers worksheet—teacher XYZ, let's say—you want the workbook to automatically find XYZ anywhere in the range Allocation!F9:OP80 and clear the contents of (not delete) those cells.
Yes

Am I doing better now?
Absolutely

So if you can get your users not to delete rows in Teachers, but to mark the to-be-deleted row with some code, then you can write a program for the Teachers worksheet to spot the delete code and behave accordingly.

1) Delete that row (XYZ) in Teachers - Yes
2) Search through Allocation looking for XYZ. -Yes
3) Wherever XYZ is found in Allocation, clear cells on that row. Yes

If I'm closer, then on to the next step: I'm guessing you already know how to write the program that spots the delete code (whatever delete code you decide upon) and deletes the row in Teachers. No. I thought I was getting somewhere but now I am not so sure. Look at the code in the module of the second workbook that I attached.

What you don't know how to do is search for it in Allocation. Am I right? Yes

Thanks again for all your trouble.

Eric
 
I'd recommend some process like below.

1. On worksheet open, have some container to hold list of current names (masterList)
2. On worksheet change, check the named range values and compare against masterList.
3. If named range count > masterList then update masterList. If named range count < masterList, generate list of names to be deleted.
4. Use Range.Replace to replace names to be deleted with blank (LookAt argument must be xlWhole).
 
Here's sample code for above process.

In Standard Module (ex: Module1):
Code:
Public dic As Object
Sub LoadDic()
Dim cel As Range
Set dic = CreateObject("Scripting.Dictionary")
For Each cel In Range("Educators")
    dic(cel.Value) = 1
Next
End Sub

Sub CleanTeacher()
With Sheets("Allocation")
    With .Range("E9:AP" & .Cells(Rows.Count, "D").End(xlUp).Row)
        For Each Key In dic.Keys
            .Replace Key, vbNullString, xlWhole, , True
        Next
    End With
End With
End Sub

In ThisWorkbook module:
Code:
Private Sub Workbook_Open()
    LoadDic
End Sub

In Worksheet module (i.e. Teachers):
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim cel As Range
If Range("Educators").Rows.Count >= dic.Count Then
    LoadDic
Else
    For Each cel In Range("Educators")
        dic.Remove cel.Value
    Next
    CleanTeacher
    MsgBox "Following teachers removed from Allocation:" & vbNewLine & Join(dic.Keys, ", ")
    LoadDic
End If
End Sub

NOTE: I didn't test extensively and didn't put any error handling. You should test it on a copy of your workbook first before applying it in the production workbook. Also, this code is Windows specific. If you are using Mac, or code should run on Mac. You'll need to replace "Scripting.Dictionary" with drop-in replacement found in link below.
https://github.com/VBA-tools/VBA-Dictionary
 
I'm looking at the Sub you named DeleteInitials. The main problem I see—and I gather you already know it— is that you're checking the rows in Teacher for a value that's been deleted, and if you find one your program goes to Allocation and deletes all the teachers. You want to clear out just the teacher that was deleted. And to do that, your program has to know what that teacher's code was. And by the time your program runs, that teacher's code is gone, so your program can't tell.

One way to fix that was the backup list I mentioned. But that's pretty complicated; I wouldn't go that way, and it sounds like you're planning to ignore it too. Another way is to teach the user to run DeleteInitials, but first putting the cursor on the row of the teacher he wants to delete. Your program can see the initials of the teacher that is to be deleted, like this:
Code:
Sub DeleteInitials()
  Set ows = ThisWorkbook.Worksheets("Teachers")

  ' Which teacher is the cursor pointing at?
  rd = Selection.Row
  TDel = ows.Cells(rd, 4).Value
  If TDel = "" then 'abend with a message; you can't delete a teacher that isn't there.

  ' Now you have the teacher's initials; you can got to Allocation and clear out just that teacher's assignments.
That would work, if you can get your users to learn it. The way I suggested is slightly more direct: When the user puts a 'D' in column 2 (for example), Excel sees it and responds automatically:
Code:
Private Sub Worksheet_Change(ByVal oc As Excel.Range)
  If UCase(oc.Value) <> "D" Then Exit Sub 'ignore all but the delete code
  If oc.Column <> 2 Then Exit Sub 'ignore if not column 2
  Set ows = oc.Worksheet
  rd = oc.Row
  TDel = ows.Cells(rd, 4).Value 'check the teacher's initials
  If TDel = "" then 'abend with a message; there's no teacher to delete on this row
  ' Now you have the teacher's initials; you can go to Allocation and clear out just that teacher's assignments.

This program runs automatically when the user changes anything on that worksheet, so the user doesn't have to do anything extra.

Is this clearer?
 
Back
Top