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?:help

[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, :rofl: :rofl:

[joanna_gr]

EDIT: Looks like we were typing at the same time, :rofl: :rofl:[/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! :doh: 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! :banghead: :( 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. :banghead:

[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 :2p:

[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... :yes

[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. :yes

[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.

[joanna_gr]

Well after deleting all data the book remains at 1500kb zipped. The sheets have not the same structure as not all of them have the same columns. Other are in greek other in English, some use only columns with phone numbers other only addresses so to export labels. It's a bit complicated. Just send me an example of how to create a userform to find a record and delete it in the master file and then delete all #ref in other sheets. I'll try to make it work. Otherwise I'll give up as I don't think it worths to keep on trying on this. After all the hard work is already done.

[Zack Barresse]

Here are some links which you would want to study ...

http://www.vbaexpress.com/kb/getarticle.php?kb_id=171

http://www.vbaexpress.com/kb/getarticle.php?kb_id=19 - for animation

http://www.vbaexpress.com/kb/getarticle.php?kb_id=132 - unique items

http://www.vbaexpress.com/kb/getarticle.php?kb_id=129 - print manager

http://www.vbaexpress.com/kb/getarticle.php?kb_id=86 - group toggle buttons

http://www.vbaexpress.com/kb/getarticle.php?kb_id=85 - group command buttons

http://www.contextures.com/xlUserForm01.html - Step by step

http://nucleardownload.com/s=userform - various

http://www.excel-vba.com/v-forms-controls.htm - various

http://support.microsoft.com/?kbid=158848 - a little bit of MS


Some tips on using the Find method ...

http://www.vbaexpress.com/forum/articles.php?action=viewarticle&artid=5

[joanna_gr]

ok. I got your point. I managed to have a userform and delete #ref from all sheets. But still don't know how to delete a record from the master sheet using a userform. I want your help on this and that's all! :)

Wow! Great links and very usefull also! After this I'll have to build my project again in a more professional way! :doh: You were right. Not msg box are needed.

Well, ehmmm, will you help me by giving me a hint of how to locate a record in a list and delete it??? : pray2:

[lucas]

Joanna,
I noticed you posted this code which works:


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

I was wondering why you don't just run your macro to fix your #refs..something like this:


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = Target.EntireRow.Address Then
Macro1
End If
End Sub

where Macro1 is your macro to fix xrefs.
I understand from trying it that it runs when you copy and paste rows as well as when you delete them but...not when you just insert (excel2000).
This way it fires when they delete a row, without a message

[geekgirlau]

If you have already created a macro to fix the #REF errors, why don't you trigger the macro in the Worksheet Before Close event?



Private Sub Workbook_BeforeClose(Cancel As Boolean)
FixRefMacro
End Sub


Errors would not be fixed until just prior to closing the workbook, however it means that Excel is not launching this macro every time you make any change.

Hands up all those who would love to have some more specific workbook events!!! : pray2:

[Killian]

Welll I think Carlos was on the right track...
If you decalre a global variable in a separate module, set it to the row count for the range on workbook_open and test the row count against it on worksheet_change, you have a result.
Or at least I do in the attached example. (in the perfect world inside my head, the data starts in A1 and there aren't any blank rows)

[joanna_gr]

oh my god! What I first though to be a silly question turned to be a round table discussion!!! :doh: Well, that's it I give up! I need vacation! :bug:

just tell me on last thing! Am I the only one in the whole world who ever needed more worksheet events? :dunno

[Paleo]

Hi Joanna,

it worked on my computer and I have submitted it at the knowledge base, while its not approved if you can send me your e-mail, I can send it to you.

[joanna_gr]

Paleco > ok. pls check your private msgs. I've sent you my email

[Anne Troy]

Am I the only one in the whole world who ever needed more worksheet events?

ROFL!!

[joanna_gr]

lucas > for some reason the macro does not run under this code. Though It looks clever solution and it should solve my problem.

[Paleo]

Paleco > ok. pls check your private msgs. I've sent you my email

Hi Joanna,

I am sending it to you...

[joanna_gr]

paleo > it works with your workbook but when I transfer it to a new file i got an error: varialbe not defined and goes to line: If Range("Last").Row < gintRow Then

[joanna_gr]

Well I did it at last. Luca's idea seems to be the most suitable for my project.


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = Target.EntireRow.Address Then
Macro1
End If
End Sub
It automatically removes all errors from all sheets without even let user to understand that something is wrong.

Thank you all people. I really appreciate very much your help on this case. :beerchug:
I got so tired with this! :( . Thank you all once again.

[Paleo]

paleo > it works with your workbook but when I transfer it to a new file i got an error: varialbe not defined and goes to line: If Range("Last").Row < gintRow Then


Hi Joanna,

you need to declare gintRow as Public in a Module first.
Anyway I am glad you got it working!:thumb

[joanna_gr]

yes you are right. sorry i missed all other information. Thank you very much:)

[Paleo]

You are very welcome!:thumb

[ndendrinos]

Hello, not sure if this will help by I would do it by double clicking any cell in the row I want to delete (place this is the sheet code) :

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

With ActiveCell.EntireRow.Select
Dim res As VbMsgBoxResult
res = MsgBox("Choose yes to delete row and remember to do this and that", vbQuestion + vbYesNo, _
"Delete confirmation")
If res = vbYes Then
Selection.Delete Shift:=xlUp
Else
' here goes the code to be run if the user doesnt confirm
End If
End With
End Sub

[ndendrinos]

Not sure what happened here ... first this is a very old posting,, second when I read it only page one showed ... well maybe someone could use my suggestion after all.