Validation with List drop down?

Hi

I want to add some simple validation to my Excel sheet but can not for the life of me remember how to do it! Basically i have a drop down list as in the image below and want so that when 'No' is selected, the user is forced to enter the reason in the next cell

http://i102.photobucket.com/albums/m82/sc0ttb_2006/excel_list.jpg

I can remember doing this before and used some VBA code but can not remember how i did it :(

Please put me out of my misery!

Thanks...

Hi

Below is from the macro recorder. I picked data form the menu then validation then selected list, and typed my two options separated by a comma

Range("K8").Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="Yes,No"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With

Sorry misunderstood question. I have a start of an answer. You need to add some variables at a module level

Public LastRange As Range
Public Warned As Boolean

The add the below code to your worksheet, then save and close. I think it should work when you re open the worksheet.

Private Sub Worksheet_Activate()
Set LastRange = ActiveCell
Warned = False
End Sub

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Not Intersect(Range("C:C"), Target) Is Nothing Then
If Target.Value = "No" Then
Range("D" & Target.Row).Select
End If
End If

End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Dim rsp

If Warned = True Then
Warned = False
Else
If LastRange.Column = 4 Then
If Range("C" & LastRange.Row).Value = "No" Then
If Len(LastRange.Value) = 0 Then
Warned = True
Range("D" & LastRange.Row).Select
rsp = MsgBox("Please enter reason", vbOKOnly)

End If
Else
Set LastRange = Target
End If
Else
Set LastRange = Target
End If
End If

End Sub

I hope the above puts you on the right path

Hi

Tried using above on one of my projects and the Worksheet_SelectionChange code needs amending to :

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Dim rsp

If Warned = True Then
Warned = False
Else
If LastRange.Column = 4 Then
If Range("C" & LastRange.Row).Value = "No" Then
If Len(LastRange.Value) = 0 Then
Warned = True
Range("D" & LastRange.Row).Select
rsp = MsgBox("Please enter reason", vbOKOnly)
Else
Set LastRange = Target
Warned = False
End If
Else
Set LastRange = Target
Warned = False
End If
Else
Set LastRange = Target
Warned = False
End If
End If

End Sub

Hi,

Thanks so much for the info. I've had a look at it but to be honest i haven't a clue where to start! I'll have another look after Xmas and see if i can get it working... Thanks again

Guys why don't you just use an input box instead of a msgbox

Range("B" & Target.Row).Value = InputBox(Prompt, Titlebar)

Or in your case

Range("D" & Target.Row.Value = InputBox("Enter a Reason", "Reason")

You can try to lock them down by popping up the text box again if they leave it blank like this

Reason = InputBox("Enter a Reason", "Reason")
Do While (Reason = "")
Reason = InputBox("Enter a Reason", "Reason")
Loop
Range("D" & Target.Row).Value = Reason

But it's an event driven language so it's impossible to force them to enter something. They could still hit alt F-4 or close the app or put any letter for a reason.