+ Reply to Thread
Results 1 to 3 of 3

spreadsheet locks after display of msgbox

  1. #1
    Forum Contributor
    Join Date
    01-06-2004
    Location
    Carbondale CO
    Posts
    245

    spreadsheet locks after display of msgbox

    Hi,
    I have code in a sheet module, activated by a button from the contol toolbox. The Code executes fine unless the msgbox is needed, at which point it displays properly, but then locks up Excel (nothing responds to keyboard or mouse) except the close button on Excel. When I put a watch (break when value changes)on the "Response" the code never seems to get there as the value stays <out of context>. The other funny thing is this code seems to have worked in the past. I'm stumped. I tried inserting two different error handlers one:
    On Error GoTo ws_exit ' right after the dim statements
    ws_exit ' right before End Sub

    and two:
    On Error Resume Next ' right after the dim statements

    but neither solved the problem. Thanks in advance for any help you can give here is my Code

    Option Explicit

    Private Sub DeleteRowFAT_Click()
    Dim Response As Integer
    Dim rngEntryBottomRow As Range

    On Error Resume Next
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    ActiveSheet.Unprotect ("geekk")
    Set rngEntryBottomRow = Range("Below_Entry_Bottom_RowFAT").Offset(-1)


    If Application.WorksheetFunction.CountA(rngEntryBottomRow) > 10 Then
    MsgBox "You are attempting to Delete a Row that contains User Input." & _
    " Delete Row Failed", vbOKOnly + vbCritical, "Can Not Delete" & _
    " Row with Information"
    If Response = 0 Then
    Exit Sub
    End If
    End If

    If Application.WorksheetFunction.CountA(rngEntryBottomRow) = 10 Then
    With rngEntryBottomRow 'rngI
    .EntireRow.Delete
    End With
    End If
    'if last detail row is blank, delete one detail row and If not blank
    ' then msg box to explain error and exit sub.

    ActiveSheet.Protect ("geekk"), DrawingObjects:=True, Contents:=True, Scenarios:=True
    Application.ScreenUpdating = True
    Application.EnableEvents = True
    End Sub
    Casey

  2. #2
    gutch01
    Guest

    Re: spreadsheet locks after display of msgbox

    You might try
    If Application.WorksheetFunction.CountA(rngEntryBottomRow) > 10
    Then
    Response = MsgBox "You are attempting to Delete a Row that contains
    User
    Input." & _
    " Delete Row Failed", vbOKOnly + vbCritical, "Can Not Delete" &
    _
    " Row with Information"
    If Response <> 6 Then
    Exit Sub
    End If
    End If


  3. #3
    Forum Contributor
    Join Date
    01-06-2004
    Location
    Carbondale CO
    Posts
    245
    gutch01,
    Thanks for the try, but I'm still getting the exact same lock up after hitting OK or the cancel "X".

    Thanks anyway.

+ Reply to Thread

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