Stop indifinite Msgbox loop in Worksheet_Change event.

mikeburg

Board Regular
Joined
Jun 23, 2005
Messages
165
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
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,303
Office Version
  1. 365
Platform
  1. Windows
Mike

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

Am I missing something?:eek:
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
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

Board Regular
Joined
Jun 23, 2005
Messages
165
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
50,531
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
 

mikeburg

Board Regular
Joined
Jun 23, 2005
Messages
165

ADVERTISEMENT

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

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
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

Board Regular
Joined
Jun 23, 2005
Messages
165
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

Well-known Member
Joined
Apr 28, 2004
Messages
76,303
Office Version
  1. 365
Platform
  1. Windows
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.
 

Forum statistics

Threads
1,141,095
Messages
5,704,310
Members
421,338
Latest member
Pepess

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top