Scott Boyd


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




Re: Validation with List drop down?

ADG


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






Re: Validation with List drop down?

Scott Boyd

I'm not sure if you get what i'm trying to do... I want it so that if 'No' is selected from the list the user is forced to input text into the column 'D'. Does this macro do this




Re: Validation with List drop down?

Andy Pope

Hi,

Not sure how you can force enter but this make the cell for that row active.
In what way did your other workbook force entry

Private Sub Worksheet_Change(ByVal Target As 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







Re: Validation with List drop down?

ADG

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





Re: Validation with List drop down?

ADG

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





Re: Validation with List drop down?

Scott Boyd

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





Re: Validation with List drop down?

Handyman_994

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

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





Re: Validation with List drop down?

Handyman_994

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.