Consulting

Results 1 to 6 of 6

Thread: Solved: drop down list forced selection

  1. #1
    VBAX Regular
    Joined
    Jan 2007
    Posts
    42
    Location

    Solved: drop down list forced selection

    Hi,

    I have an interesting question, I want to force a user to select from a drop down list based on a calculated field... Im doing a cash reconcilation, so If column B has a discrepancy the user will have to select from a drop down list.

    I have a drop down list in column c that gives a user a range of reasons? Column b is a calculated field which is a result of other colums.. put simply it?s a sum of ?

    Now my question is ? if there is ANY value in column b? the user MUST Choose a reason from the Drop down list in column c? if they don?t then excel Must prompt them to do so?. What is the best way to solve this problem?? i tried data validation.. but it doesnt work... I would appreciates anyones professional expertise.

  2. #2
    Site Admin
    Jedi Master
    VBAX Guru Jacob Hilderbrand's Avatar
    Joined
    Jun 2004
    Location
    Roseville, CA
    Posts
    3,712
    Location
    Try this code. Put it in the code section for the sheet you want it to run on (right click on the sheet tab and select View Code, paste in the window that opens up).

    [vba]
    Option Explicit

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    Dim i As Long
    Dim LastRow As Long
    Dim Prompt As String
    Dim Title As String

    If Target.Column <> 3 Then
    LastRow = Range("B" & Rows.Count).End(xlUp).Row
    For i = 2 To LastRow
    If Range("B" & i).Text <> "" And Range("C" & i).Text = "" Then
    Prompt = "You must enter a value in " & Range("C" & i).Address(False, False) & "."
    Title = "Additional Information Required"
    MsgBox Prompt, vbCritical, Title
    Exit For
    End If
    Next i
    End If

    End Sub
    [/vba]

    This will basically check to see if there is a value in B and not in C, and if so prompt the user. This will run whenever the selection range is changed.

  3. #3
    VBAX Regular
    Joined
    Jan 2007
    Posts
    42
    Location
    Just to make it a bit more complex... what if I had another drop down list in another column On the same worksheet... say column G that need to be entered if there is a value in column F... what would the code be: (ill have a go but could you pllease correct it mate)



    [VBA]Option Explicit
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim i As Long
    Dim LastRow As Long
    Dim Prompt As String
    Dim Title As String
    If Target.Column <> 3 Then
    LastRow = Range("B" & Rows.Count).End(xlUp).Row
    For i = 2 To LastRow
    If Range("B" & i).Text <> "" And Range("C" & i).Text = "" Then
    Prompt = "You must enter a value in " & Range("C" & i).Address(False, False) & "."
    Title = "Additional Information Required"
    MsgBox Prompt, vbCritical, Title
    Exit For
    End If
    Next i
    End If

    Option Explicit
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim i As Long
    Dim LastRow As Long
    Dim Prompt As String
    Dim Title As String
    If Target.Column <> 7 Then
    LastRow = Range("F" & Rows.Count).End(xlUp).Row
    For i = 2 To LastRow
    If Range("f" & i).Text <> "" And Range("C" & i).Text = "" Then
    Prompt = "You must enter a value in " & Range("f" & i).Address(False, False) & "."
    Title = "Additional Information Required"
    MsgBox Prompt, vbCritical, Title
    Exit For
    End If
    Next i
    End If
    End Sub


    End Sub [/VBA]






    how did I go?? could you correct me please... thankyou soooooo much

  4. #4
    Site Admin
    Jedi Master
    VBAX Guru Jacob Hilderbrand's Avatar
    Joined
    Jun 2004
    Location
    Roseville, CA
    Posts
    3,712
    Location
    You can't have to separate event macros (for the same event), but you can combine them.

    [vba]
    Option Explicit

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    Dim i As Long
    Dim LastRow As Long
    Dim Prompt As String
    Dim Title As String

    If Target.Column <> 3 And Target.Column <> 6 Then
    LastRow = Range("B" & Rows.Count).End(xlUp).Row
    For i = 2 To LastRow
    If Range("B" & i).Text <> "" And Range("C" & i).Text = "" Then
    Prompt = "You must enter a value in " & Range("C" & i).Address(False, False) & "."
    Title = "Additional Information Required"
    MsgBox Prompt, vbCritical, Title
    GoTo ExitSub:
    End If
    Next i

    LastRow = Range("F" & Rows.Count).End(xlUp).Row
    For i = 2 To LastRow
    If Range("F" & i).Text <> "" And Range("G" & i).Text = "" Then
    Prompt = "You must enter a value in " & Range("G" & i).Address(False, False) & "."
    Title = "Additional Information Required"
    MsgBox Prompt, vbCritical, Title
    GoTo ExitSub:
    End If
    Next i
    End If

    ExitSub:

    End Sub
    [/vba]

  5. #5
    VBAX Regular
    Joined
    Jan 2007
    Posts
    42
    Location
    Thankyou soooo much for your expertise and time... your a genious

  6. #6
    Site Admin
    Jedi Master
    VBAX Guru Jacob Hilderbrand's Avatar
    Joined
    Jun 2004
    Location
    Roseville, CA
    Posts
    3,712
    Location
    You're Welcome

    Take Care

Posting Permissions

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