Results 1 to 13 of 13
  1. #1
    Lounger
    Join Date
    Sep 2001
    Location
    Birmingham, Alabama, USA
    Posts
    40
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Excel Validation (Excel 2000)

    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

  2. #2
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts

    Re: Excel Validation (Excel 2000)

    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

  3. #3
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts

    Re: Excel Validation (Excel 2000)

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


    zeddy

  4. #4
    Lounger
    Join Date
    Sep 2001
    Location
    Birmingham, Alabama, USA
    Posts
    40
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Validation (Excel 2000)

    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

  5. #5
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Validation (Excel 2000)

    Maybe the attached file comes in handy here.

    Source: MSO Forum.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Excel Validation (Excel 2000)

    You could add this to the thisworkbook object:

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


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

    Steve

  7. #7
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts

    Re: Excel Validation (Excel 2000)

    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

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Excel Validation (Excel 2000)

    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

  9. #9
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Validation (Excel 2000)

    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.
    Legare Coleman

  10. #10
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Validation (Excel 2000)

    And control-Insert.....
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  11. #11
    Lounger
    Join Date
    Sep 2001
    Location
    Birmingham, Alabama, USA
    Posts
    40
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Validation (Excel 2000)

    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

  12. #12
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Validation (Excel 2000)

    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.

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

    Legare Coleman

  13. #13
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Maidstone, Kent, England
    Posts
    398
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Validation (Excel 2000)

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

    Peter

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •