Data Validation - Create input box if certain value is selected

[duboost]

Hi,

I am using data validation to limit the text strings that may be entered into a cell. However one of the options in the data validation list is "OTHER" for things that don't fall under any of the other options. Basically, if the user selects "OTHER" I want an input box to pop up to allow the user to type in a description and it will enter in the cell, "OTHER - user's description".

Here is code that I am using so far which I found online that is allowing more than one entry to be selected in a single cell. I'm not sure how or where in this code to add the code for the input box if "OTHER" is selected. sorry vba n00b. Thanks for any help

Option Explicit
' Developed by Contextures Inc.
' www.contextures.com
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngDV As Range
Dim oldVal As String
Dim newVal As String
If Target.Count > 1 Then GoTo exitHandler

On Error Resume Next
Set rngDV = Cells.SpecialCells(xlCellTypeAllValidation)
On Error GoTo exitHandler

If rngDV Is Nothing Then GoTo exitHandler

If Intersect(Target, rngDV) Is Nothing Then
'do nothing
Else
Application.EnableEvents = False
newVal = Target.Value
Application.Undo
oldVal = Target.Value
Target.Value = newVal
If Target.Column = 3 Then
If oldVal = "" Then
'do nothing
Else
If newVal = "" Then
'do nothing
Else
Target.Value = oldVal _
& Chr(10) & newVal
End If
End If
End If
End If

exitHandler:
Application.EnableEvents = True
End Sub

 

[duboost]

 

[Ruddles]

Reaplce your code with this:-

[FONT=Courier New]Option Explicit[/FONT]
[FONT=Courier New][/FONT] 
[FONT=Courier New]' Developed by Contextures Inc.
' [/FONT][URL="http://www.contextures.com"][FONT=Courier New]www.contextures.com[/FONT][/URL]
[FONT=Courier New][/FONT] 
[FONT=Courier New]Private Sub Worksheet_Change(ByVal Target As Range)[/FONT]
[FONT=Courier New][/FONT] 
[FONT=Courier New]  Dim rngDV As Range
  Dim oldVal As String
  Dim newVal As String
  
  If Target.Count > 1 Then GoTo exitHandler
  
  On Error Resume Next
  Set rngDV = Cells.SpecialCells(xlCellTypeAllValidation)
  On Error GoTo exitHandler
  
  If rngDV Is Nothing Then GoTo exitHandler
  
  If Intersect(Target, rngDV) Is Nothing Then
    'do nothing
  Else
[/FONT][FONT=Courier New][COLOR=red]    If Target.Value = "OTHER" Then
      newVal = "OTHER - " & InputBox("Enter a description:")
    Else
      newVal = Target.Value
    End If
    Application.EnableEvents = False
    Application.Undo
[/COLOR]    oldVal = Target.Value
    Target.Value = newVal
    If Target.Column = 3 Then
      If oldVal = "" Then
        'do nothing
      Else
        If newVal = "" Then
          'do nothing
        Else
          Target.Value = oldVal & Chr(10) & newVal
        End If
      End If
    End If
  End If
  
exitHandler:
  Application.EnableEvents = True[/FONT]
[FONT=Courier New][/FONT] 
[FONT=Courier New]End Sub[/FONT]

The bit in red is where I made the changes. Please test on a copy of your workbook and make sure you retain a copy of your existing code before you make the changes.

Can I also suggest that when posting code, you enclose it within CODE tags - the # icon in the advanced editor toolbar - as this preserves indenting and uses a fixed-width font, both of which make code easier for us to read and encourages more responses.

 

[duboost]

thank you for your help and apologies for not putting the code within the code tags. this is probably a really stupid question, but when I paste the code into a module and try to run it, it asks me for the macro's name. But doesn't "Private Sub Worksheet_Change (ByVal Tartget As Range)" already specify the name of the macro as "Worksheet_Change"?

Sorry for the stupid question. What exactly am I doing wrong? Thanks

 

[Smitty]

The code needs to go in the Worksheet specific module, not a general module. It runs based on the specified change on the sheet, so you can't call it.

 

[duboost]

Thanks for the reply although I'm not sure I follow. I THINK I've added the code to a worksheet specific module. I right clicked ok the worksheet tab and clicked view code. The drop down menu on the upper left of the module shows "Worksheet" rather than "General". However when trying to run it I am still being prompted for the macro name. Apologies for my noobness. Thanks again

 

[Smitty]

You put it in the right place, but you don't run it. Instead it runs automatically based on whatever worksheet change you told it to monitor. In your case, when a data validation range changes.

 

[duboost]

Well my face is red lol. All is working well now. Thanks so much for both of your help and patience.