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
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