Results 1 to 10 of 10
  1. #1
    4 Star Lounger
    Join Date
    Jul 2006
    Posts
    531
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Mandatory field if value selected in dropdown (2003)

    Is there any way in Excel to make a field mandatory if a value is selected in a dropdown, see attachment for details.
    Attached Images Attached Images

  2. #2
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Mandatory field if value selected in dropdown

    You could use conditional formatting

    Assume the dropdown box is in A1, highlight B1 and then in conditional formatting type =A1<>""

    Like the image below
    Jerry

  3. #3
    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: Mandatory field if value selected in dropdown (2003)

    How about something like this in the worksheet code (I presumed the change was going to be made to Column G, change as desired)

    Steve

    <pre>Option Explicit
    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim sComment As String
    Dim rInt As Range
    Dim rCell As Range
    Set rInt = Intersect(Target, Range("G:G"))
    If Not rInt Is Nothing Then
    For Each rCell In rInt
    If Trim(rCell) <> "" Then
    If Trim(rCell.Offset(0, 1)) = "" Then
    sComment = ""
    Do While sComment = ""
    sComment = Application.InputBox _
    (prompt:="Enter Mandatory Comment: ", _
    Title:="Comment is Mandatory", _
    Type:=2)
    Loop
    rCell.Offset(0, 1) = sComment
    End If
    End If
    Next
    End If
    Set rInt = Nothing
    Set rCell = Nothing
    End Sub</pre>


  4. #4
    4 Star Lounger
    Join Date
    Jul 2006
    Posts
    531
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Mandatory field if value selected in dropdown

    Can you send me this is a test spreadsheet so l can see exactly how this works

  5. #5
    4 Star Lounger
    Join Date
    Jul 2006
    Posts
    531
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Mandatory field if value selected in dropdown (2003)

    Can you send me this coding in a spreadsheet, so l can see how it works and also how to implement it correcty.

  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: Mandatory field if value selected in dropdown (2003)

    I think you will learn more by trying to do it yourself and adpating it for your own spreadsheet. If you have problems, post back and we can walk you through it....

    Steve

  7. #7
    4 Star Lounger
    Join Date
    Jul 2006
    Posts
    531
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Mandatory field if value selected in dropdown

    I tried this an nothing happened? I would like the comments field to be made mandatory if this option is selected in dropdown.
    Attached Images Attached Images

  8. #8
    4 Star Lounger
    Join Date
    Jul 2006
    Posts
    531
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Mandatory field if value selected in dropdown (2003)

    Where does this coding go exactly and what comes up on the screen if they choose this option in drowndown?

  9. #9
    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: Mandatory field if value selected in dropdown

    The conditional formatting needs to be in the column with the comment, referring to the column ot the left. Also a format needs to be set to highlight the mandatory cell. The cond formatting will not make the cell be filled in, it will only highlight (with formating) cells that need to be filled in.

    Steve

  10. #10
    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: Mandatory field if value selected in dropdown (2003)

    It goes in the worksheet object for the sheet you want the cell in. [you can right-click on the sheet tab and select view code. The macro goes in this pane of VB.]

    If the value in a cell in G is changed, the code runs an a input box comes asking for the comment, which is put into the cell to the right.

    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
  •