Defining Active Cell Location

[theteerex]

In the snippet of code below, what would I have to do to change the way I reference the active cell?

Private Sub Worksheet_Change(ByVal Target As Range) '_Change  creates the variable as a target, transferred as a value to "ProjectName"
    Dim ans As Integer


[B][COLOR=#ff0000]    If Target.Column = 2 And Target.Row > 1 And Target.Value = "" Then [/COLOR][/B]

How can I change the red to this?

[COLOR=#008000]    If Worksheets("Projects").Range("Records[OPPORTUNITY]") And Worksheets("Projects").Target.Row > 1 And Target.Value = "" Then[/COLOR]

 

[My Aswer Is This]

Maybe you would like to tell us what your trying to do.
This is a sheet change event script.

 

[theteerex]

Maybe you would like to tell us what your trying to do.
This is a sheet change event script.

In my table, when I delete anything from column B, the code asks the user if they are sure, if yes, it deletes the entire row.
The problem occurs when I am pasting values into multiple cells on that row that are not in column B!
I get an error that should not even be occurring due to the fact that I was not even deleting anything from the table.
Curiously, I can paste single values, for example, I can paste in C35, D35, E35 and F35 individually with no error. But if I do it all at once, I get an error.
Full Code:

Private Sub Worksheet_Change(ByVal Target As Range) '_Change  creates the variable as a target, transferred as a value to "ProjectName"
    Dim ans As Integer
    


    Debug.Print Target.Column, Target.Row, Target.Value
[COLOR=#ff0000][B]    If Target.Column = 2 And Target.Row > 1 And Target.Value = "" Then[/B][/COLOR]
    
        ans = MsgBox("Are you sure you want to Delete......This cannot Be Undone !!!", vbYesNo)
            If ans = vbYes Then
        
                With Application
                    .EnableEvents = False ' freeze WorkSheetChange event since you're about to UNDO a change
                    .Undo ' undo the change
                    ProjectName = Target.Value ' assign the deleted-undone value in to YYY
                    Rows(ActiveCell.Row).EntireRow.Delete ' delete that row
                    .EnableEvents = True ' re-enable all events
                End With
                MsgBox ProjectName & " has been deleted."  ' now you have that value in ProjectName
                        
            End If
        
    End If
DeleteRows (ProjectName) 'Gives sub-routine "DeleteRows" the company name
End Sub

 

[My Aswer Is This]

Your telling me what is going wrong. But I want to know why do you have this sheet change event script.

What do you want to happen when something else happens.

This script runs any time you make a change in column B. No matter if you delete cells or change cells.

 

[theteerex]

Your telling me what is going wrong. But I want to know why do you have this sheet change event script.

What do you want to happen when something else happens.

This script runs any time you make a change in column B. No matter if you delete cells or change cells.

I want the script to delete a set of records on another worksheet.
I want this deletion to happen anytime a user deletes information from the active row in column B.
This column contains all project names that correspond to specific records on the other sheet, and so, if it is deleted from this sheet, it must also be deleted from the other sheet.

My script, as you noticed, runs on anything that happens on the worksheet, when it should be running only when any of the cells in the table column b are deleted.

 

[My Aswer Is This]

I know of no way you can have a script run when you delete a row.
Or a column or a cell.

You said:

when it should be running only when any of the cells in the table column b are deleted.

But maybe someone else here at Mr. Excel will know a way to do this.
I will continue to monitor this thread and see what I can learn.


​

 

[My Aswer Is This]

Are you always wanting to delete a entire column or row. Or just a cell in the row.

We could have you double click on Range("C1") and the script would delete column "C" on Sheet (1)
And delete Column "C" on sheet(2)

Would that work?

I would need the two sheet names.

 

[My Aswer Is This]

Or we could write a script so if you double click on any cell.
That row will be deleted on both sheets.

So if you double click on Range("B45") then Row(45) will be deleted on both sheets.

 

[Peter_SSs]

The problem occurs when I am pasting values into multiple cells on that row that are not in column B!
I get an error that should not even be occurring due to the fact that I was not even deleting anything from the table.
Curiously, I can paste single values, for example, I can paste in C35, D35, E35 and F35 individually with no error. But if I do it all at once, I get an error.

I can clarify why you are getting that error.
Suppose that you are pasting "red", 2, "", 5 into C35:F35 at once.
Clearly that will trigger your code.
Ignoring the Debug.Print line (I think you only have that to try to find what is going wrong) the first line encountered is

If Target.Column = 2 And Target.Row > 1 And Target.Value = "" Then

Now, how can the code evaluate that red bit? We are trying to evaluate if the 'Target' has a value of "", but the target is four cells, all with different values. In summary, Target.Value is meaningless if there is more than one cell, hence the code throws an error.

Some other comments about your code:
- The DeleteRows (ProjectName) line is too far down. It will run every time the Worksheet_Change code is triggered, whether or not you have deleted anything in column B.
- In the code, you ask the user if they really want to delete. If they answer 'No', your code doesn't delete the row but it does leave the column B value deleted. Is that what you want to happen or do you want the deleted value restored?
- This isn't an error, but the line Rows(ActiveCell.Row).EntireRow.Delete does not require the 'EntireRow' part as 'Rows(ActiveCell.row)' already is an entire row.

Here is a modified version of your code that addresses at least some of the issues above.
At this stage I have made 2 assumptions:
i) That you already have the code to deal with deletion in the other sheet via the DeleteRows (ProjectName) call.
ii) That you will be deleting at most one value at a time from column B

Anyway, see if this nudges you in the right direction.

Private Sub Worksheet_Change(ByVal Target As Range) '_Change  creates the variable as a target, transferred as a value to "ProjectName"
  Dim ans As Integer
  Dim ProjectName As String
  Dim Changed As Range
  
  Set Changed = Intersect(Target, Columns(2), Rows("2:" & Rows.Count))
  If Not Changed Is Nothing Then
    If Changed.Cells.Count = 1 Then
    
      If Changed.Value = "" Then
      
        ans = MsgBox("Are you sure you want to Delete......This cannot Be Undone !!!", vbYesNo)
        If ans = vbYes Then
        
          With Application
            .EnableEvents = False ' freeze WorkSheetChange event since you're about to UNDO a change
            .Undo ' undo the change
            ProjectName = Changed.Value ' assign the deleted-undone value in to YYY
            Rows(Changed.Row).Delete ' delete that row
            .EnableEvents = True ' re-enable all events
          End With
          
          MsgBox ProjectName & " has been deleted."  ' now you have that value in ProjectName
          DeleteRows (ProjectName) 'Gives sub-routine "DeleteRows" the company name
          
        End If
      End If
    End If
  End If
End Sub

 

[theteerex]

Thank you for your help so far, I appreciate the opportunity to learn.

A few questions about your code:

• Do I still need the

  ByVal Target As Range

  ?

If there is no Target, isn't this useless?

• Is there some way this sub could have done the same thing without the

  _Change

  ?

I didn't really even know what it was, I just found out what it was later and thought it was part of the name of my worksheet

Otherwise, your code works very well and the error is gone. I can even paste to multiple cells.
I am going to add a backup sub as well since I was told to never delete without backing up!