Results 1 to 7 of 7
  1. #1
    Lounger
    Join Date
    Feb 2002
    Location
    Surrey, England
    Posts
    38
    Thanks
    0
    Thanked 0 Times in 0 Posts

    List Data Validation (Excel 2000 SR-1)

    Is there a way to control what is entered in a cell, allowing only entries from a list but only displaying a warning if it is a particular part of that list? For example, a list with parts numbered 1 to 100 but a warning dialog to be displayed only if a number above 50 is entered. Over to the experts ... <img src=/S/dizzy.gif border=0 alt=dizzy width=15 height=15>

  2. #2
    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: List Data Validation (Excel 2000 SR-1)

    You could use datavalidation to ensure that the cell contents are between 1 and 100 (using its error and messages).
    In additon to display a comment that it is > 50 add this to the worksheet object in VB (I used range B1:B100 as the range to check, change as appropriate in both places) change the message as desired

    Steve

    <pre>Option Explicit
    Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    Dim rCell As Range
    Dim bMoreThan50 As Boolean
    Dim sMsg As String

    If Not Intersect(Target, Range("B1:B100")) Is Nothing Then
    bMoreThan50 = False
    sMsg = "The following cells are > 50:"
    For Each rCell In Intersect(Target, Range("B1:B100"))
    If rCell.Value > 50 Then
    bMoreThan50 = True
    sMsg = sMsg & vbCrLf & rCell.Address
    End If
    Next
    If bMoreThan50 Then MsgBox sMsg
    End If
    End Sub</pre>


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

    Re: List Data Validation (Excel 2000 SR-1)

    You might also consider using a conditional format that colours the cell red when it gets an entry above 50.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  4. #4
    Lounger
    Join Date
    Feb 2002
    Location
    Surrey, England
    Posts
    38
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: List Data Validation (Excel 2000 SR-1)

    Thanks for the quick response but I haven't got a clue about VB and am not entirely sure what to do with this code. How do I actually go about using it? <img src=/S/blush.gif border=0 alt=blush width=15 height=15>

  5. #5
    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: List Data Validation (Excel 2000 SR-1)

    Then you might want to use Jan's suggestion of conditional formatting. No VB easier to maintain.

    If you want a "popup message:
    Question 1:
    What cells do you plan on having datavalidation on? (I need to know this to help you change the code)

    Steve

  6. #6
    Lounger
    Join Date
    Feb 2002
    Location
    Surrey, England
    Posts
    38
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: List Data Validation (Excel 2000 SR-1)

    Do you mean the actual cells in my worksheet? If so, it's A6:A13 at the moment. Some of our products are subject to a surcharge so I wanted whoever is filling the form out to be able to enter only a valid part number but get a warning if a surcharge is applicable on that particular number as well. Maybe I'll have a fiddle with the conditional formatting too. Thanks again.

  7. #7
    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: List Data Validation (Excel 2000 SR-1)

    Open VB (alt-F11)
    In the explorer pane dbl-click the worksheet NAME for the worksheet that has these cells
    Copy this code:

    Option Explicit
    Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    Dim rCell As Range
    Dim bMoreThan50 As Boolean
    Dim sMsg As String
    If Not Intersect(Target, Range("A6:A13")) Is Nothing Then
    bMoreThan50 = False
    sMsg = "The Items in the following cells will have a surcharge::"
    For Each rCell In Intersect(Target, Range("A6:A13"))
    If rCell.Value > 50 Then
    bMoreThan50 = True
    sMsg = sMsg & vbCrLf & rCell.Address
    End If
    Next
    If bMoreThan50 Then MsgBox sMsg
    End If
    End Sub

    And Paste it into the "pane" in the upper right
    Close VB (Alt-q)

    If you want a different message let me know and I can walk you through changing it.

    Also you could put in a cell NEXT to the items a formula like (eg in cell B6):
    <pre>=if(A6>50, "There is a surcharge on this item","")</pre>


    copy this from B6 to B7:B13. Now when the value is >50 a message will pop up in another cell (no VB code). You could also use cond formatting to color the cell in col A as well as this cell.

    Steve

Posting Permissions

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