Msg Box Order

[RandyD123]

I would like to alter the code below to do the following if possible

When a user tries to delete a row or column they get an "Warning or Error" first saying something like "Deleting Rows or Column Is Not Allowed without a password" Do you wish to continue? (if they select YES a password dialog box shows and if they say NO the row or column they tried to delete is restored and a msg box will show saying "no changes have been made"

If they enter the wrong password they get to "RETRY" or "Cancel" If they cancel a msg box will say "No Changes Have Been Made" If they enter the correct password the row or column gets deleted and a msg box confirms that a row/column has been deleted.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Dim Msg As Variant
    Dim pWord As String
    pWord = "deleterow"

    If Target.Address = Target.EntireRow.Address Or _
       Target.Address = Target.EntireColumn.Address Then
passwrd:
        response = InputBox("Enter Password To Continue")
        If response = "" Then
        
        GoTo undochange
        ElseIf response <> pWord Then

            Msg = MsgBox("Please Enter Correct Password To Continue", 5, "Incorrect Password")
            If Msg = 4 Then GoTo passwrd
            Msg = "Deleting Rows/Columns Not Permitted"
            Msg = MsgBox(Msg, 16, "WARNING")
undochange:
            With Application
                .EnableEvents = False
                .Undo
                .EnableEvents = True
            End With
        Else
            Exit Sub
        End If
    End If
End Sub

 

[dmt32]

Hi,
give this a try & see if does what you want:

Place following in ThisworkBook Code Page

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Dim Msg As Integer
    Dim pWord As String
    pWord = "deleterow"


    If Target.Address = Target.EntireRow.Address Or _
       Target.Address = Target.EntireColumn.Address Then
       
    Msg = MsgBox("Deleting Rows or Column Is Not Allowed without a password" & Chr(10) & _
                    "Do you wish to continue?", 292, "Password Required")


        If Msg = 6 Then If IsValidPassword(Password:=pWord) Then Exit Sub
        
            With Application
                .EnableEvents = False
                .Undo
                .EnableEvents = True
            End With
            
             MsgBox "No Changes Have Been Made.", 48, "Action Cancelled"
    End If
End Sub

Place Function in standard module.

Function IsValidPassword(ByVal Password As String) As Boolean
    Dim Entry As Variant
    Dim Prompt As String, Title As String
    Dim try As Integer
    
    try = 1
    Title = "Enter Password"
    Do
        Prompt = "Please Enter Password To Continue"
        Prompt = Prompt & Chr(10) & Chr(10) & _
                  "attempt " & try & " of 3"


        Entry = InputBox(Prompt, Title)
        If StrPtr(Entry) = 0 Then Exit Do
        
        If Entry <> Password And try = 3 Then
                MsgBox "Three Attempts Only Allowed", 16, "Three Attempts Only"
                Exit Do
        ElseIf Entry <> Password Then
                MsgBox "Password invalid - Please try again", 48, "Invalid Password"
                try = try + 1
            
        Else
                MsgBox "Password Correct - Click OK To Continue.", 48, "Password Correct"
            
            Exit Do
        End If
    Loop
    IsValidPassword = Entry = Password
End Function

Dave

 

[RandyD123]

Ha! Works Perfectly...Thank You

 

[dmt32]

Ha! Works Perfectly...Thank You

Making the password part a separate function you can use it in other applications if needed.

Glad solution helped - many thanks for feedback

Dave

 

[Macropod]

Cross-posted at: http://www.excelforum.com/excel-programming-vba-macros/1115360-message-box-order.html
Please read our policy on Cross-Posting in rule #13: http://www.mrexcel.com/forum/board-announcements/99490-forum-rules.html

 

[RandyD123]

Am I to assume that if I used your first sentence, that I would be OK.....?

 

[Macropod]

Yes, that's all that is expected.

 

[RandyD123]

Thank you. I really appreciate all the help here.

 

[philb99]

Dave this works great - but how do you set up the Password part

thanks

 

[dmt32]

Dave this works great - but how do you set up the Password part

thanks

You just pass the Password to the Function

If IsValidPassword(Password:=pWord) Then

In current code your variable pWord is intialized with Password "deleterow" but this can be changed by you if required.

Dave