Solved: drop down list forced selection

[cbs81]

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.

[Jacob Hilderbrand]

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).


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


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.

[cbs81]

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)




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






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

[Jacob Hilderbrand]

You can't have to separate event macros (for the same event), but you can combine them.


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

[cbs81]

Thankyou soooo much for your expertise and time... your a genious

[Jacob Hilderbrand]

You're Welcome :beerchug:

Take Care