Run Commands Before Allowing Delete 1

[bdmangum]

As per the title, I need to run several commands before the user can delete the selected cell. Let me explain the setup. I have built an excel workbook which acts like a database. My desire would be to use only forms, however the customer wants to be able to modify entries on an excel spreadsheet. As I know some of you are thinking, big problems.

The user is able to attach one file to each new entry, which is accomplished through use of forms(both creating a new entry and uploading the file). The file is stored in a network folder. A hyperlink is then created in a column on the main data storage spreadsheet which loads the file upon being selected.

Here's my problem. Since the user can see and modify items in spreadhseet format, I need to be able to proccess certain commands before the user deletes anything on the sheet. There doesn't appear to be any worksheet event which runs commands before something is deleted.

To clarify, the user can modify individual entries which begin on the fifth row. The creates the potential for the user to delete a row of data, which in turn deletes the hyperlink to the uploaded file. There is no problem with the user deleting the row, the problem is that I need to trace which attached file is with the deleted entry so it can also be deleted.

If this isn't very clear let me know.

TIA,
BD

 

[xlbo]

You can hook into the worksheet_CHANGE event to check to see if something has been deleted. You then process what you need to in there before allowing the sub to finish and the change to be executed

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[bdmangum]

Not sure I understand what you mean. The Change event is triggered after the data is deleted. Thus all the commands in the change event run after the data is gone.

What I have done is use the change event followed by an Application.Undo when certain criteria are met. It works, but I doubt it's the best method.

 

[xlbo]

only other thing you could do would be to use the before_right_click event which would potentially interrupt ONE of the delete options.

Realistically though, you can only capture a change after it has happened - excel doesn't yet have the premonition function as standard ;-)

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[WalkerEvans]

Have you tried using the OnKey Method? This should allow you to interupt the delete action until your other stuff is done.

----------------------------------------------------------------------------------
"A committee is a life form with six or more legs and no brain." -- L. Long

 

[bdmangum]

Thanks for the tip Walker! That works perfectly when combined with the Selection_Change event

Here's the completed code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim Crasher As Boolean

If Target.Count > 1 Then
    For Each Item In Target.Cells
        If Item.Column = 5 Then
            Crasher = True
        End If
    Next
End If


If Target.Column = 5 Or Crasher = True Then
    Application.OnKey "{DELETE}", "MsgCall"
Else
    Application.OnKey "{DELETE}"
End If

End Sub



Sub MsgCall()
    MsgBox "Delete key selected.  Cannot delete data in column E."
End Sub