Stop indifinite Msgbox loop in Worksheet_Change event.

[mikeburg]

Need help to program an error message for the Worksheet_Change event. Tried the following but when the sheet exceeds 49 lines, I get an indifinite loop showing the message box until I control break to stop the loop:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim intNumberOfLines As Integer
intNumberOfLines = Cells.Find("*", After:=Range("IV65536"), SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row - 2 'Finds last data in any column
If intNumberOfLines > 49 then MsgBox("The maximum of 49 lines have been exceeded" & VbLf & vbLf & "reduce the number of lines to 49 or less!")
End sub

Also tried:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim intNumberOfLines As Integer
intNumberOfLines = Cells.Find("*", After:=Range("IV65536"), SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row - 2 'Finds last data in any column
If intNumberOfLines > 49 Then
strAnswer = MsgBox("The maximum of 49 lines have been exceeded" _
& VbLf & vbLf & "reduce the number of lines to 49 or less!")
MsgBox strAnswer
If strAnswer = 1 Then Exit Sub
End If
End Sub

Please provide the VBA code for any suggestions. Thanks very much.
mikeburg

 

[Norie]

Mike

I can't see any code there that would cause any type of loop.

Am I missing something?

 

[erik.van.geit]

Hi,

Do you have some other code which changes the sheet ?
Perhaps you didn't display all the code in this sheetchangeevent ?
If this problem is occuring when you change your sheet manually, it is a great mistery: seems quite impossible to me. (and to be sure for 100%, I did test)

kind regards,
Erik

 

[mikeburg]

Oh yes, you guys are great! I also have the following code in the worksheet_change event:

If intNumberOfLines >= 0 Then
Range("A1") = intNumberOfLines
Else
Range("A1") = 0
End If

This is to keep me informed how many lines are used in the sheet. When I remove it, I no longer get the loop. I really need the # of lines displayed in cell A1. Any ideas how to make all this code work together?

Thanks mikeburg

 

[Peter_SSs]

mikeburg

For your last piece of code, try changing to this:

Application.EnableEvents = False
If intNumberOfLines >= 0 Then
Range("A1") = intNumberOfLines
Else
Range("A1") = 0
End If
Application.EnableEvents = True

 

[erik.van.geit]

some ideas
1. you might also be intrested to use a formula for the last row
http://www.mrexcel.com/board2/viewtopic.php?t=142787&start=6
2. why let the user do something which he shouldn't do ??

The maximum of 49 lines have been exceeded
reduce the number of lines to 49 or less!

instead of curing the problem, we could find a way to prevent the user from adding too much rows

best regards,
Erik

 

[mikeburg]

Thanks for all your suggestions but this is the only VBA code I could get to work!

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Union(Range("$A50:$IV65534"), Target).Address = Range("$A50:$IV65534").Address Then
MsgBox ("The maximum of 49 lines have been exceeded" & vbLf & vbLf _
& "reduce the number of lines to 49 or less!")
End If
End Sub

To allow trial & error editing, I want to allow more than 49. I just want to warn the user they have exceeded 49.

You guys are greatly helping me learn. Thanks a million. If you have any other ideas, please post them here. mikeburg

PS: I just don't understand why I can't click ok & end the message box loop using the VBA I 1st posted above.

 

[erik.van.geit]

for me this last post is somehow confusing

perhaps we should have asked earlier:
please tell us in plain english: what is the purpose ?

example of a clear statement:
disable any possibility for the user to write anything in row 52 and further

can you write something like that, so we know for sure what you mean ?
the help we can provide will be more adequate

best regards,
Erik

 

[mikeburg]

All I am trying to do is to warn the user if he adds too many procedures for that particular sheet to be able to print on the form that will be loaded in the laser printer (for example, 52 procedures).

The user may continue adding more procedures but since he has been warned, he will know to go back to previous lines of procedures & remove &/or combine procedures until they do not exceed the desired limit for that form (52, for example).

Thanks, mikeburg

 

[Norie]

Mike

If this is a printing thing then why not use the BeforePrint event instead of a worksheet change event.

You could use that to check the no of lines/whatever and then cancel printing if required.