PDA

View Full Version : Solved: Alert before delete a record



sujittalukde
06-13-2007, 09:58 PM
I have a macro which deletes the selected record But it doesnot alert any message before delete I want that the when the code will be run, a alert msg should pop up asking "Do you really want to delete the record?" if the user clicks "Yes" then the record should be deleted and if the user clicks "No" , no deletion should take place and the msg box should go off the screen.


Sub DeleteRecord()
Dim intActiveRow As Integer

Application.ScreenUpdating = False

intActiveRow = ActiveCell.Row

ActiveSheet.Unprotect password:="abcd"
With Range("A" & intActiveRow)
.EntireRow.Delete
End With
ActiveSheet.Protect password:="abcd"

Application.ScreenUpdating = True
End Sub

lucas
06-13-2007, 10:10 PM
See if this works for you:
Option Explicit
Public Sub CustomMessageBoxes()
Dim iResponse As Integer
Dim intActiveRow As Integer
iResponse = MsgBox(Prompt:="Click Yes or No.", _
Buttons:=vbYesNo + vbCritical)
Select Case iResponse
Case vbYes
Application.ScreenUpdating = False
intActiveRow = ActiveCell.Row
ActiveSheet.Unprotect Password:="abcd"
With Range("A" & intActiveRow)
.EntireRow.Delete
End With
ActiveSheet.Protect Password:="abcd"
Application.ScreenUpdating = True
Case vbNo
' MsgBox Prompt:="You clicked No."
Exit Sub
End Select
End Sub

lucas
06-13-2007, 10:11 PM
or you can call it like this and call the macro you already have:
Public Sub CustomMessageBoxes()
Dim iResponse As Integer
Dim intActiveRow As Integer
iResponse = MsgBox(Prompt:="Click Yes or No.", _
Buttons:=vbYesNo + vbCritical)
Select Case iResponse
Case vbYes
DeleteRecord
Case vbNo
' MsgBox Prompt:="You clicked No."
Exit Sub
End Select
End Sub

anandbohra
06-13-2007, 10:14 PM
try this simplest way

Sub DeleteRecord()
Dim a
Dim intActiveRow As Integer
Application.ScreenUpdating = False
intActiveRow = ActiveCell.Row
ActiveSheet.Unprotect Password:="abcd"
a = MsgBox("do you really want to delete record", vbYesNo + vbCritical, "Your Name")
If a = vbYes Then
With Range("A" & intActiveRow)
.EntireRow.Delete
End With
End If

ActiveSheet.Protect Password:="abcd"

Application.ScreenUpdating = True
End Sub

sujittalukde
06-13-2007, 10:40 PM
Thank you lucas, thanks anand.
Codes are perfect.