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

 

[erik.van.geit]

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.

perhaps force the user to print only by means of a button, so you can customize the printaction

workbookmodule

Private Sub Workbook_BeforePrint(Cancel As Boolean)
If button_used = False Then
MsgBox "only print with button", 48, "PRINTING CANCELED"
Cancel = True
End If
End Sub

"normal" module

Public button_used As Boolean

Sub print_with_button()
button_used = 1
'printcode
button_used = 0
End Sub

if you have sheetchangecode to check for a number of lines, it will popup at least on each new entry-line (depending how your code is setup)

instead you could use a formula like =COUNTA("A3:A65536") to check for the number of items
combined with conditional formatting in a cell on the first row + freezing panes so the first row stays on screen looks ideal to me

step-by-step
select row2
menu window/freeze panes
A1:
formula: =IF(COUNTA(A3:A65536)>B1,"WARNING","")
conditional format =COUNTA(A3:A65536)>B1 / make cell red if condition is met
B1: value is 52
(you only need to put the value in a cell on each sheet, if the maximum is different per sheet)

create button(s) to print and assign the macro "print_with_button"
you could check cell A1 within your code or use

If Application.CountA(Range("A3:A" & Rows.Count)) > Range("B1") Then

add sheetreferences as needed
example ...

Set Sh = Sheets(1)
If Application.CountA(Sh.Range("A3:A" & Rows.Count)) > Sh.Range("B1") Then

so still some work to do
think about maintenance and userfriendly approach before proceeding

best regards,
Erik

 

[mikeburg]

It's really not just a print thing. The user should edit the sheet until the maximum number of lines are not exceeded.

I wish I knew why the message box works OK with the Union VBA but not the other.

Thanks so very much, mikeburg

 

[erik.van.geit]

Hi,

it seems to me you missed my reply, because it was on the second page of the thread
this little post, just to be sure

best regards,
Erik