Prompt Box

[joanna_gr]

Hi again! I hope you can help me on this also. I want to create a warning box to prompt after a user deletes a row and remind him to do something. Any ideas please?

[Paleo]

Hi Joanna,

to popup a prompt box do this:

Sub Delete()
    Dim res As VbMsgBoxResult
    res = MsgBox("Are you sure you want to delete this row?", vbQuestion + vbYesNo, _
"Delete confirmation")
    If res = vbYes Then
        '  here goes the code if the user confirms
    Else
        '  here goes the code to be run if the user doesnt confirm
    End If
End Sub

[joanna_gr]

well, I think I was not very clear. I want the user to delete a row and AFTER deleting a msg box appears and reminds him to do something.


p.s. I don't need a macro. I think that must be written in the worksheet code or something and prompt each time someone deletes a row. I hope I explain better this time.

[Paleo]

Oh ok, will the user be deleting the row by using a button created by you?

[joanna_gr]

not necessarily. For example : In a list of names there is a duplicate. The user goes there and deletes the whole line. Then a msg box appers and ask him to do something... like press a button. That's all


To help you more this is the code i've found

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = Target.EntireRow.Address Then
MsgBox "REMINDER! after DELETING row press button 1"
End If
End Sub

But it promts a msgbox even if I insert a line or copy paste and I don't need that because it's so confusing.

[Paleo]

You may put this code in the worksheet module:

Private Sub CommandButton1_Click()
Dim Linha As Long, z As VbMsgBoxResult
Linha = ActiveCell.Row
Selection.Delete Shift:=xlUp
z = MsgBox("You have just deleted row " & Linha, vbInformation + vbOKOnly, _
"Deletion occured")
End Sub

Is this what you wanted?



EDIT: Looks like we were typing at the same time,

[joanna_gr]

EDIT: Looks like we were typing at the same time, [/QUOTE]


Yes we are typing at the same time... LOL

Well I don't want to be a code of a command button or something. As said before a user deletes a row manually and then the message appears. It's just a reminder not to forget to do something important.

[Paleo]

I think you could use:

Private Sub Worksheet_Change(ByVal Target As Range)
   If Selection.Delete Then
      MsgBox "Remind to ..."
   End If
End Sub

Have to test it still, but might work.

[joanna_gr]

nope! That caused excel to blinks for long time and at the end a msg box shows up which I pressed about 25 times and is still on my screen! I'm afraid it's more difficult that I first thought to be.

[Paleo]

Gee, I just tested it and I am affraid you were very right. Had to push Ctrl + Alt + Del and finish excel, .

Ok, try this:

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False
    If Selection.Delete Then
        MsgBox "Remind to ..."
    End If
    Application.EnableEvents = True
End Sub

Sorry for the goofed, my event was firing itself.

[joanna_gr]

This is close but as the code I posted the msg box promts in every event. Now prompts even if I add a word. But I only need to promt if someone deletes a row! I'm gonna cry!

[Paleo]

Hi Joanna,

ok, now I believe this will work for you:

Place the code below into the worksheet module

Private Sub Workbook_Open()
gintRow = Range("A65536").End(xlUp).Row
End Sub

Place the code below into the worksheet module

Private Sub Worksheet_Calculate()
If Range("A65536").End(xlUp).Row < gintRow Then
MsgBox "The row is deleted!"
End If
gintRow = Range("A65536").End(xlUp).Row
End Sub

[joanna_gr]

Did you try this out? Is it working for you? Because I tried and nothing changed. If you make it work could you please let me know exactly your steps to put the code Maybe is my mistake.

[Zack Barresse]

You wouldn't need to use Ctrl + Alt + Delete to get out of running the code. While the message box is up on your screen, hit Ctrl + Pause|Break (usualy just referred to as Break, and is usually somewhere above the Page Up key and to the right of your F12 key). This will give you an error message that says something like your code has been inturrupted, and you can End or go into Debug mode to allow you to see what line of code you are on or erroring out on.

I believe what Carlos is trying to get at is setting a Public variable. For me, the easiest way to do this is to use an extra (usually hidden) worksheet to hold these values. This is common practice with add-ins. Although in the end, I don't think it is going to help what you are after. There is no specific event tied to deleting or inserting of rows or columns. Besides that, it's very bad practice to use these techniques. They should ONLY be employed in the building of spreadsheets, and not in the daily operation(s) of one.

The safest way for you to handle this is to create a UserForm in which is as simple as possible for your users to use for the deletion/insertion of rows/columns. If you can control the flow of their actions, then you can test what they are wanting to do and customize it any way you'd like (such as giving them a custom message box prior to or after such actions). I would then protect the worksheet structure NOT allowing any deleting or inserting of rows/columns.

My

[joanna_gr]

well, I thought that this would be very simple and it turned to be impossible to be done.
I don't want a form or anything else than I asked and let me explain why. I have a sheet1 and in sheet2,3,4 I have pasted in links various data.
When I delete a row from sheet1 in all other sheets a #ref is shown up. I have a macro to run after is deletion to clear all #ref. The only thing I want is to remind a user to run this macro so not errors are shown on other sheets. I thought that this would be the simpliest thing I've ever asked but....

[Zack Barresse]

Sorry Joanna, this is far from a simple procedure. It sounds like we need to get to the root of this problem. Why are you deleting rows to start with? This is a very bad habit to get into and the best way to counter act this is to develop spreadsheet structures that do not need deleting or inerting of rows/columns.

[joanna_gr]

ok. let's start from the beginning. I have a worksheet (sheet1) that holds a list o contacts. It happens many times that we stop doing business with a contact so we need to delete it. Or (rarely happens of course) someone dies... delete again! I don't want to explain to users that they must run a macro to delete a #ref that is shown on other sheets. I only want to remind them to do so.

This code:

Private Sub Worksheet_Change(ByVal Target As Range) 
If Target.Address = Target.EntireRow.Address Then 
MsgBox "Rows " & Target.Row & " to " & _
Target.Row + Target.Rows.Count - 1 & " deleted" 
End If 
End Sub

does what I want but triggers even if I insert rows. I wish there was a way to work only in deletion of rows... If it's impossible... maybe I'll ask microsoft to correct this in a next version...

[Zack Barresse]

I would still sugest a UserForm for this action. This will give you the flexibility of customizing the way you handle data across sheets and even take out all message boxes (or the need for them). They do not take long to create and are a good way to go for what you are wanting to do.

Are you able to upload the workbook? (If need be, strip all personal data from the file, but at least try to provide the necessary structure.) If so, I will take a look at it and post an example later this week (am very busy this afternoon until Wednesday), unless somebody posts a similar solution before me.

I believe we can really make this a more trouble free and professional spreadsheet structure for you.

[joanna_gr]

well I wish i could upload this workbook but it's huge. I have so many sheets and userforms etc. I'm not sure what to delete and what to keep to help you. It would be helpful though to explain in what way I could use a userform to delete a record in sheet one. I don't know how to do it. And then how I could del all #ref from the other sheets?

[Zack Barresse]

Well, it would be a matter of doing a Find method (of what I have invisioned in my head) for each sheet, then performing the delete on these. If there was any kind of "unified structure" in your sheets, this would be very easy and done in about 4 lines of code.

And as long as you can zip your file and get it to about 250 kb, then upload it. If not, you can always email it to me.