error in input

[Pierre via OfficeKB.com]

Hi experts,

Mi user can put in a lot of data in my application in the lines 3 -1000 and
columns b,c,e,f,g.
After the input my application will start calculating a lot but if the user
put in a wrong number in a cell my formulas will return an error somwhere in
the sheet.

Question:

I would like to find out if, anywhere in the sheet, there is a formule giving
an error message like
- #NB or
-VALUE or
- etc....

Is this possible to do for the range A3:X1000 ?
If it is, i would be able to then show a message that some input was wrong
and stop the calculation...

Thanks,
Pierre

 

[Norman Jones]

Hi Pierre,

Try:

'==============>>
Public Sub aTester()
Dim rng1 As Range
Dim rng2

Set rng1 = Range("A3:X1000")

On Error Resume Next
Set rng2 = rng1.SpecialCells(xlCellTypeFormulas, xlErrors)
On Error GoTo 0

If Not rng2 Is Nothing Then
'Errors found
MsgBox "Errors in " & rng2.Address(0, 0)
End If

End Sub
'<<==============

 

[Pierre via OfficeKB.com]

Hi Norman,

I tried your code but there seems to generate an error each time i run it.

first:
on the line
If Not rng2 Is Nothing Then
i get the error message "object required"

second: the code itself generates an error but it still stops the code in
stead of handling the error
Any ideas ?
Thanks,
Pierre

Hi Pierre,

Try:

'==============>>
Public Sub aTester()
Dim rng1 As Range
Dim rng2

Set rng1 = Range("A3:X1000")

On Error Resume Next
Set rng2 = rng1.SpecialCells(xlCellTypeFormulas, xlErrors)
On Error GoTo 0

If Not rng2 Is Nothing Then
'Errors found
MsgBox "Errors in " & rng2.Address(0, 0)
End If

End Sub
'<<==============

Hi experts,

[quoted text clipped - 22 lines]

Thanks,
Pierre

 

[Norman Jones]

Hi Pierre,

Change:

to
Dim rng2 As Range

---
Regards,
Norman

Hi Norman,

I tried your code but there seems to generate an error each time i run it.

first:
on the line
If Not rng2 Is Nothing Then
i get the error message "object required"

second: the code itself generates an error but it still stops the code in
stead of handling the error
Any ideas ?
Thanks,
Pierre

Hi Pierre,

Try:

'==============>>
Public Sub aTester()
Dim rng1 As Range
Dim rng2

Set rng1 = Range("A3:X1000")

On Error Resume Next
Set rng2 = rng1.SpecialCells(xlCellTypeFormulas, xlErrors)
On Error GoTo 0

If Not rng2 Is Nothing Then
'Errors found
MsgBox "Errors in " & rng2.Address(0, 0)
End If

End Sub
'<<==============

Hi experts,

[quoted text clipped - 22 lines]

Thanks,
Pierre

 

[Pierre via OfficeKB.com]

Hi Norman,

I found out what the problem was....my sheet was protected and then your code
does not work.

Question:
- Is there a way to have your code work with a protected sheet (other then
unprotect it and protect it again after the code) ?
- is there a way to just give the rownumber back to the user?

Thanks,
Pierre

Hi Pierre,

Try:

'==============>>
Public Sub aTester()
Dim rng1 As Range
Dim rng2

Set rng1 = Range("A3:X1000")

On Error Resume Next
Set rng2 = rng1.SpecialCells(xlCellTypeFormulas, xlErrors)
On Error GoTo 0

If Not rng2 Is Nothing Then
'Errors found
MsgBox "Errors in " & rng2.Address(0, 0)
End If

End Sub
'<<==============

Hi experts,

[quoted text clipped - 22 lines]

Thanks,
Pierre

 

[Norman Jones]

Hi Pierre,

Question:
- Is there a way to have your code work with a protected sheet (other then
unprotect it and protect it again after the code) ?

Setting the Protect method's UserInterfaceOnly parameter to true enables vba
manipulation of the protected sheet.

However, this setting is not persistent and needs to be reset each time the
workbook is opened.

Perhaps, therefore, you could set protection in the Workbook_Open or
Auto_Open procedures, e.g.:

'==========>>
Sub Auto_Open()
With Worksheets("sheet1")
.Protect Password:="drowssap", UserInterfaceOnly:=True
End With
End Sub
'<<==========

- is there a way to just give the rownumber back to the user?

Yes, a string of row numbers can be built and reported. However, given that
your initial post indicated a 24 column range, would reporting the row
number be sufficient? If errors will only occur in a single column or a
subset of the A3:X1000 range, then this can be reflected in the macro's rng
variable assignment. If you really do want row numbers reported (rather than
cell addresses), what do you want to happen if multiple errors occur on a
given row?

 

[Pierre via OfficeKB.com]

Hi Norman,

thanks for the UserInterfaceOnly solution !

In my application the user can fill in only 4 columns (B,C,E,and F)
Only C,E and F can cause possible error (because these are colums where the
user has to input a date, salary and a parttime%. The rest of the columns are
calculations based on the user input.

The code you gave now gives the first error that it encounters.
Is there a code that would give msgbox stating all the rows where errors
occurred?
By the way, i adapted the code to the following:
sub but_test click()
Dim rng1 As Range
Dim rng2
Set rng1 = Range("A3:X1000")
On Error Resume Next
Sheets("deelnemers").Unprotect Password:="ekmef1"
Set rng2 = rng1.SpecialCells(xlCellTypeFormulas, xlErrors)
On Error GoTo 0
If Not rng2 Is Nothing Then
'Errors found
MsgBox "your input cause an error in row " & rng2.Row - 2 & _
Chr(13) & Chr(13) & "Check your input and press the button again please'"
Sheets("deelnemers").Protect Password:="ekmef1"
Exit Sub
End If

Please help me again and thanks,
Pierre

Hi Pierre,

Question:
- Is there a way to have your code work with a protected sheet (other then
unprotect it and protect it again after the code) ?

Setting the Protect method's UserInterfaceOnly parameter to true enables vba
manipulation of the protected sheet.

However, this setting is not persistent and needs to be reset each time the
workbook is opened.

Perhaps, therefore, you could set protection in the Workbook_Open or
Auto_Open procedures, e.g.:

'==========>>
Sub Auto_Open()
With Worksheets("sheet1")
.Protect Password:="drowssap", UserInterfaceOnly:=True
End With
End Sub
'<<==========

- is there a way to just give the rownumber back to the user?

Yes, a string of row numbers can be built and reported. However, given that
your initial post indicated a 24 column range, would reporting the row
number be sufficient? If errors will only occur in a single column or a
subset of the A3:X1000 range, then this can be reflected in the macro's rng
variable assignment. If you really do want row numbers reported (rather than
cell addresses), what do you want to happen if multiple errors occur on a
given row?

---
Regards,
Norman

Hi Norman,

[quoted text clipped - 9 lines]

Thanks,
Pierre

 

[Norman Jones]

Hi Pierre,

Try:
'================>>
Sub But_test_click()
Dim rng As Range
Dim RngA As Range, RngB As Range
Dim RngBig As Range
Dim rCell As Range
Dim rw As Range
Dim WB As Workbook
Dim SH As Worksheet
Dim msg As String

Set WB = ActiveWorkbook
Set SH = WB.Sheets("Deelnemers")
Set rng = Columns("A:X")

On Error Resume Next
Set RngA = rng.SpecialCells(xlCellTypeConstants, xlErrors)
Set RngB = rng.SpecialCells(xlCellTypeFormulas, xlErrors)
On Error GoTo 0

If Not RngA Is Nothing Then Set RngBig = RngA

If Not RngB Is Nothing Then
If Not RngBig Is Nothing Then
Set RngBig = Union(RngB, RngBig)
Else
Set RngBig = RngB
End If
End If

If Not RngBig Is Nothing Then
For Each rCell In RngBig.Rows
Set RngBig = Union(RngBig, rCell.EntireRow)
Next rCell
msg = "Errors found in rows:"
For Each rw In RngBig.Rows
msg = msg & vbNewLine & rw.Row
Next rw
msg = msg & vbNewLine & _
"Check your input and press the button again please'"
Else
msg = "No errors found"
End If
MsgBox Prompt:=msg, _
Buttons:=vbInformation, _
Title:="ErrorReport"

End Sub
'<<================


Assuming that you have protected the sheet using the UserInterfaceOnly
parameter, it is not necessary to unprotect / reprotect the sheet.