Input box triggered when specific value selected from data validation list.

[MeaclH]

G'day all,
Not sure if this is possible at all. I am currently working on a project that uses lots of data validation drop down cells.
What I would like to happen, is when a specific value is selected from the drop down box, it triggers an input box to appear and the user is required to enter a message which will then be applied as a comment to the cell.

Can this be done? or wishful thinking...

Cheers,
Hayden

 

[Peter_SSs]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Try this Worksheet_Change code with a test worksheet. For this sample code I have assumed that the DV drop-downs are in C2:C10 and the "trigger value" for the drop-down is "b".
To implement ..
1. Right click the sheet name tab and choose "View Code".
2. Copy and Paste the code below into the main right hand pane that opens at step 1.
3. Close the Visual Basic window & test.

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim Resp As String
  
  If Target.CountLarge = 1 And Not Intersect(Target, Range("C2:C10")) Is Nothing Then
    If Target.Value = "b" Then
      Resp = Application.InputBox("Enter your comment", , , , , , , 2)
      If Len(Resp) > 0 And Resp <> "False" Then Target.AddComment Text:=Resp
    End If
  End If
End Sub

Should anything happen if the user has added a comment and then later changes the value from "b" to something else, including clearing the cell?

 

[MeaclH]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Try this Worksheet_Change code with a test worksheet. For this sample code I have assumed that the DV drop-downs are in C2:C10 and the "trigger value" for the drop-down is "b".
To implement ..
1. Right click the sheet name tab and choose "View Code".
2. Copy and Paste the code below into the main right hand pane that opens at step 1.
3. Close the Visual Basic window & test.

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim Resp As String
 
  If Target.CountLarge = 1 And Not Intersect(Target, Range("C2:C10")) Is Nothing Then
    If Target.Value = "b" Then
      Resp = Application.InputBox("Enter your comment", , , , , , , 2)
      If Len(Resp) > 0 And Resp <> "False" Then Target.AddComment Text:=Resp
    End If
  End If
End Sub

Should anything happen if the user has added a comment and then later changes the value from "b" to something else, including clearing the cell?H

Hi Peter,
Will do!
That works amazingly. Thanks so much. I wasn't aware of worksheet code changes. Thanks for the step by step.
Code works great. If I want to add multiple target values into the one code, how shall I change this line? the OR function?
If Target.Value = "b" Then

I don't think anything needs to happen if the value changes.
One question about the comments though. If there is a comment already on the cell, there is a runtime error. Any ideas on altering the code so that it allows a comment to be added in addition?

Cheers,
Hayden

 

[Peter_SSs]

Try this then. You will need to remove the previous code.

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim Resp As String
  
  If Target.CountLarge = 1 And Not Intersect(Target, Range("C2:C10")) Is Nothing Then
    With Target
      Select Case .Value
        Case "b", "d", "f" '<- Add more trigger values here if required
          Resp = Application.InputBox("Enter your comment", , , , , , , 2)
          If Len(Resp) > 0 And Resp <> "False" Then
            If Not .Comment Is Nothing Then
              .Comment.Text .Comment.Text & vbLf & Resp
            Else
              .AddComment Text:=Resp
            End If
          End If
      End Select
    End With
  End If
End Sub

 

[MeaclH]

Try this then. You will need to remove the previous code.

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim Resp As String
 
  If Target.CountLarge = 1 And Not Intersect(Target, Range("C2:C10")) Is Nothing Then
    With Target
      Select Case .Value
        Case "b", "d", "f" '<- Add more trigger values here if required
          Resp = Application.InputBox("Enter your comment", , , , , , , 2)
          If Len(Resp) > 0 And Resp <> "False" Then
            If Not .Comment Is Nothing Then
              .Comment.Text .Comment.Text & vbLf & Resp
            Else
              .AddComment Text:=Resp
            End If
          End If
      End Select
    End With
  End If
End Sub

Mate that works so, so, so well. I really appreciate you!

Cheers,
Hayden

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.