Validation with List drop down?

Validation with List drop down?

• Ying06

  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.

  
  

• Atul Kulkarni

  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

  
  

• Baji Prasad

  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

  
  

• Yeshia

  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
  

• mccpres

  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

  
  

• clint 2

  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

  
  

• Anand Raman - MSFT

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

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

  
  

• Ramanakumar

  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
  
  
  
  
  
• Validation with List drop down?