ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   spreadsheet locks after display of msgbox (https://www.excelbanter.com/excel-programming/341284-spreadsheet-locks-after-display-msgbox.html)

Casey[_24_]

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(rngEntryBotto mRow) 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(rngEntryBotto mRow) = 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


------------------------------------------------------------------------
Casey's Profile: http://www.excelforum.com/member.php...fo&userid=4545
View this thread: http://www.excelforum.com/showthread...hreadid=471213


gutch01

spreadsheet locks after display of msgbox
 
You might try
If Application.WorksheetFunction.CountA(rngEntryBotto mRow) 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


Casey[_25_]

spreadsheet locks after display of msgbox
 

gutch01,
Thanks for the try, but I'm still getting the exact same lock up afte
hitting OK or the cancel "X".

Thanks anyway

--
Case

-----------------------------------------------------------------------
Casey's Profile: http://www.excelforum.com/member.php...nfo&userid=454
View this thread: http://www.excelforum.com/showthread.php?threadid=47121



All times are GMT +1. The time now is 06:25 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com