Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to limit number of Message Boxes on Validation Error?
Hi
I have the following code which highlights validation errors when a user pastes into a range. The problem is that when a large amount of data is pasted (with a suitably large n number of validation errors) the msgbox has to be clicked n times before the corrections can be made. ***Start Code*** Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) Cells.ClearComments Dim TempCell As Range Dim rc As Interger For Each TempCell In ActiveSheet.UsedRange If Not TempCell.Validation.Value Then rc = MsgBox("Please ensure the circled data is entered correctly (including formatting) and paste as values only", 16, "Data Validation Error") ActiveSheet.CircleInvalid End If Next End Sub **End Code*** Is it possible to have the message box appear once only but for all erroneous cells to be circled? I also have a problem with users pasting formatted cells into the range but that's another problem :) Thanks in advance. (I'm a novice VBA user so please excuse my simplisitic approach) |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to limit number of Message Boxes on Validation Error?
Hi
You can use a boolean variable and test if it has been set True: Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) Cells.ClearComments Dim TempCell As Range Dim rc As Interger Dim NoShow As Boolean For Each TempCell In ActiveSheet.UsedRange If Not TempCell.Validation.Value Then If NoShow = False Then rc = MsgBox("Please ensure the circled data is entered correctly" & vbLf & _ "(including formatting) and paste as values only", 16, "Data Validation Error ") NoShow = True ActiveSheet.CircleInvalid End If Next End Sub Regards, Per On 26 Apr., 18:11, Richard R wrote: Hi I have the following code which highlights validation errors when a user pastes into a range. The problem is that when a large amount of data is pasted (with a suitably large n number of validation errors) the msgbox has to be clicked n times before the corrections can be made. ***Start Code*** Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) Cells.ClearComments Dim TempCell As Range Dim rc As Interger For Each TempCell In ActiveSheet.UsedRange If Not TempCell.Validation.Value Then rc = MsgBox("Please ensure the circled data is entered correctly (including formatting) and paste as values only", 16, "Data Validation Error") ActiveSheet.CircleInvalid End If Next End Sub **End *Code*** Is it possible to have the message box appear once only but for all erroneous cells to be circled? I also have a problem with users pasting formatted cells into the range but that's another problem :) Thanks in advance. (I'm a novice VBA user so please excuse my simplisitic approach) |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to limit number of Message Boxes on Validation Error?
Many thanks Jessen
It seems to work with a small amount of tweaking. This is what I've finished with: Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) Cells.ClearComments Dim TempCell As Range Dim rc As Integer Dim NoShow As Boolean For Each TempCell In ActiveSheet.UsedRange If Not TempCell.Validation.Value Then If NoShow = False Then rc = MsgBox("Please ensure the circled data is entered correctly " & vbLf & "(including formatting) and paste as values only", 16, "Data Validation Error ") End If NoShow = True ActiveSheet.CircleInvalid End If Next End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Error message is Character Limit is over 1024 characters | Excel Discussion (Misc queries) | |||
Data validation error message | Excel Discussion (Misc queries) | |||
Data validation boxes, outputting a final number from the boxes | Excel Discussion (Misc queries) | |||
Formatting calculated number appearing in the text in message boxes | Excel Programming | |||
Error message boxes.... | Excel Discussion (Misc queries) |