Results 1 to 5 of 5

Cancel a message box pop up action

Threaded View

  1. #1
    Valued Forum Contributor
    Join Date
    04-11-2006
    MS-Off Ver
    2007
    Posts
    438

    Cancel a message box pop up action

    Hello all,

    I have this awesome code below created by mrice (thanks again mrice!) from an earlier post. Currently, after the states I choose are entered in, the options that the message box brings up are "OK" and "Cancel".

    An issue that I hadn't foreseen at the time is the following...

    If I press either the "OK" or "Cancel" button without entering any states in (because I changed my mind and want ALL states in there, or I pressed either button by accident), the macro eliminates ALL the rows because it needs criteria.

    Can this code be modified so that the message box does the following...

    1. If I press the "OK" button but I haven't entered in any criteria in the message box, bring up another message box saying "you have not entered in any states in" which brings up an "OK" box and brings you back to the original message box.

    2. If I hit the "Cancel" button, bring up a message box that says "No states will be removed" with an "OK" button, essentially cancelling the "State" macro portion of the sub, but continuing with the rest of the code in the sub if there is any.

    I would like to have this message box work this way even if states were entered in the input box but the user pressed the "Cancel" button anyway.

    3. Create a NEW button named "Keep ALL States" which essentially does the same thing as the "Cancel" button. (I'm trying to "idiot proof" this ).

    Thanks much!




    Sub DeleteSpecial()
    Dim DeleteRow As Boolean
    StateColumn = Rows(1).Find("State", , xlValues, xlWhole).Column
    StateList = Application.InputBox("Please enter the state/states you would like to use, separate each state with a comma", , , , , , , 2)
    MyArray = Split(StateList, ",")
    For N = 0 To UBound(MyArray)
        MyArray(N) = Trim(MyArray(N))
    Next N
    For M = Cells(Rows.Count, StateColumn).End(xlUp).Row To 2 Step -1
        DeleteRow = True
        For N = 0 To UBound(MyArray)
            If LCase(MyArray(N)) = LCase(Cells(M, StateColumn)) Then
                DeleteRow = False
                Exit For
            End If
        Next N
        If DeleteRow = True Then Rows(M).Delete
    Next M
    End Sub
    Last edited by duugg; 08-14-2009 at 06:49 PM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1