Excel Validation (Excel 2000)

Topic

New Reply

I have a large spreadsheet that I send out to customers where they fill in data that is then uploaded to a data base. One of the things I need to control is the length of the fields. To do this I use Data Validation and set the field to allow Text Length Less then or Equal to the max number of characters I want entered in the field. The sheet and workbook are password protected when sent out. All rows and columns that are not to be filled in are hidden.

My problem is that when the sheet is returned I have fields with significantly more characters in it then the Validation should allow. The validation rules are not changed on the cell. For example a cell that has validation set for a length less then or equal to 30 may have 55 characters in it. There are no fields on the sheet that should allow for more then 30 characters.

I know that you can past into a cell with Validation and the validation rules do not apply. The only way I can come up with that someone could get around the edit and cause the problem I am having is to open a second workbook and enter the data and then copy and past it into the validated cell in the first workbook.

Two questions, can anyone think of any other way around the validation and how can I keep people from doing this?

Thanks

Reply | Quote

Replies

You can use Tools->Auditing->Show Auditing Toolbar and then click the button ‘circle invalid values’ to show cells which don’t comply with the validation rules set.

Perhaps you could set your template excel spreadsheet up with ‘disable drag-anddrop’.?

zeddy

Reply | Quote

..you would also have to disable the Ctrl-V paste function either completely or only when in a specifed range.

zeddy

Reply | Quote

Zeddy,

Thanks for the tips. Using audit to circle the error would show the user where the error is, I am just not sure that would do much good. Turing off the Ctrl-V paste function sounds interesting, trouble is I can

Reply | Quote

Maybe the attached file comes in handy here.

Source: MSO Forum.

Reply | Quote

You could add this to the thisworkbook object:

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Excel.Range)
    Application.CutCopyMode = False
End Sub

It clears the clipboard whenever the selection has been changed. Nothing in clipboard nothing to paste.

Steve

Reply | Quote

Steve
..would this prevent you from dropping a Ctrl-V paste from say, Word, into the Excel cell?

I was hoping to see one of Legare’s interesting If Not Intersect combinations with a disabling
Application.OnKey “^{v}”, “”

zeddy

Reply | Quote

No I do NOT think so.
Just disabling Ctrl-v isn’t enough either. You must ALSO remove the paste button from the toolbar, the paste and paste special menu items from the toolbars, any other “paste” buttons the user added (I have excel’s pastespecialvalues button on mine) AND also prevent them from adding them so “customize” must be disabled.

Your best bet is to remove ALL the toolbars and the menubar, add your own and then when you go to a different window in excel restore them (DO NOT RESET them – this makes them the excel default – restore them to what they were) for more complete control.

Steve

Reply | Quote

And control-Insert…..

Reply | Quote

Instead of using data validation, you could use the worksheet change event routine to check the length of data that was entered into the cell that is being changed. Depending on how your data is arranged, it could be a little messy because the routine would have to figure out what length to check based on the cell address. If you can upload an example workbook, I would see what I could do.

Reply | Quote

Lagare

In its simplest form my problem comes down to a single column, all the other columns are hidden. There are a limited number of rows available and the remaining rows are hidden. All the cells have validation turned on to allow 40 or fewer text characters in the cell. The attached example is really simplified. It has one column and 10 rows. Validation is set at 10 or less characters. I did not protect the example. You will notice that the first 5 cells have 11 characters in the cell. I copied them from another sheet.

In the real world I have a work book that includes 30 worksheets. Each sheet builds on previous sheets by using data entered on one sheet on one or more of the following sheets. There are multiple columns on some of the sheets each having a different validation rule. The workbook is approaching 3.5 Meg when populated.

You mention that

Reply | Quote

In your example all of the cells on the sheet are being checked for a length of <= 10. The code below in the worksheet change event (Right click on the sheet tab and select "View code") should do what your are asking. I left the too long cells so you could test copy and paste.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim oCell As Range, oBadRange As Range
Dim strAddr As String
    If Intersect(Target, ActiveSheet.Range("A1:A10")) Is Nothing Then Exit Sub
    Application.EnableEvents = False
    strAddr = ""
    For Each oCell In Target
        If Len(oCell.Value) > 10 Then
            If oBadRange Is Nothing Then
                Set oBadRange = oCell
            Else
                Set oBadRange = Union(oBadRange, oCell)
            End If
        End If
    Next oCell
    If Not oBadRange Is Nothing Then
        MsgBox "The cell(s) " & oBadRange.Address(False, False) & " contain invalid data."
        oBadRange.Select
    End If
    Application.EnableEvents = True
End Sub

Reply | Quote

Whilst it would not stop them doing it, conditional formating could be used to make it rather obvious when they have!

Peter

Reply | Quote