none
vba: insert comment box upon value in cell and write in other cell RRS feed

  • Question

  • Dear experts,

    I have been looking for a solution for hours with regard to the following problem, but unfortunately without luck.

    If in cell A1 a user selects "KO criterium" out of a validation list, I'd like a msgbox to appear to request additional text input from the user (e.g. "Dear user, please motivate your answer in the textbox below"). Once the user has entered some text and presses an OK button, I'd like this input to be copied to another cell, say B1.

    Any help is much appreciated!

    Kind regards,

    Thomas.
    Wednesday, January 4, 2012 4:54 PM

Answers

  • Here's one way. The code belongs in the worksheet module, right click the sheet tab and click "View Code"

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim sPrompt As String
    Dim sTitle As String
            If Target.Address = "$A$1" Then
                 If LCase$(Target.Value) = "ko criterium" Then
                         sTitle = "Motivational response request"
                         sPrompt = "Dear user, please motivate your answer in the textbox
    below"
                         v = Application.InputBox(sPrompt, sTitle)
                         If VarType(v) = vbBoolean Then
                                 Target.Offset(0, 1) = "you cancelled"
                         ElseIf Len(v) = 0 Then
                                 Target.Offset(0, 1) = "you pressed OK but didn't answer"
                         Else
                                 Target.Offset(0, 1) = v
                         End If
                 End If
         End If
    End Sub

    Peter Thornton

    • Marked as answer by Tomatski Wednesday, January 4, 2012 7:23 PM
    Wednesday, January 4, 2012 5:43 PM
    Moderator

All replies

  • Here's one way. The code belongs in the worksheet module, right click the sheet tab and click "View Code"

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim sPrompt As String
    Dim sTitle As String
            If Target.Address = "$A$1" Then
                 If LCase$(Target.Value) = "ko criterium" Then
                         sTitle = "Motivational response request"
                         sPrompt = "Dear user, please motivate your answer in the textbox
    below"
                         v = Application.InputBox(sPrompt, sTitle)
                         If VarType(v) = vbBoolean Then
                                 Target.Offset(0, 1) = "you cancelled"
                         ElseIf Len(v) = 0 Then
                                 Target.Offset(0, 1) = "you pressed OK but didn't answer"
                         Else
                                 Target.Offset(0, 1) = v
                         End If
                 End If
         End If
    End Sub

    Peter Thornton

    • Marked as answer by Tomatski Wednesday, January 4, 2012 7:23 PM
    Wednesday, January 4, 2012 5:43 PM
    Moderator
  • Peter,

    Many thanks for your reply. Exactly what I was looking for!

    An alternative apparently would bethe following:

    Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$A$1" And Range("A1") = "KO criterium" Then   
        Range("B1") = InputBox("Dear user, please motivate your answer in the textbox below")
    End If
    
    End Sub

    Kind regards,

    Thomas

    Wednesday, January 4, 2012 7:25 PM