Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
error in input
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 -- Message posted via http://www.officekb.com |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
error in input
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 '<<============== -- --- Regards, Norman "Pierre via OfficeKB.com" <u13950@uwe wrote in message news:5790bbd850555@uwe... 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 -- Message posted via http://www.officekb.com |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
error in input
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 Norman Jones wrote: 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 -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200511/1 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
error in input
Hi Pierre,
Change: Dim rng2 to Dim rng2 As Range --- Regards, Norman "Pierre via OfficeKB.com" <u13950@uwe wrote in message news:57a2870fd4e04@uwe... 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 Norman Jones wrote: 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 -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200511/1 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
error in input
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 Norman Jones wrote: 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 -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200511/1 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
error in input
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 "Pierre via OfficeKB.com" <u13950@uwe wrote in message news:57a2cc1ae1be2@uwe... 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
error in input
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 Norman Jones wrote: 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 -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200511/1 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
error in input
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. --- Regards, Norman "Pierre via OfficeKB.com" <u13950@uwe wrote in message news:57a4a88217462@uwe... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
error 13 type mismatch input box | Excel Discussion (Misc queries) | |||
number input error | Excel Discussion (Misc queries) | |||
Code error with input box | Excel Programming | |||
Formula input error help | Excel Discussion (Misc queries) | |||
Input Box error when scrolling | Excel Programming |