VBA for userform/Comment box type function

[harryco79]

Hello everyone. Seeking help on a function i want to create in a worksheet that mimics the "comment box" function but with a twist.

I would like the user to be able to click a cell (A1) and then a userform with a textbox will appear to the right of it.
The user then inputs a comment , hits save and the form will disappear.

However i would like the text input and saved to be linked to a particular cell in another worksheet (worksheet B) within the workbook.

I Will be using this function for mutilple cells in the workbook. Can anyone helpp with this.

I am using Excel 2007 and inexperienced with VBA.

Thanks so much for all the help in advance. Love the msg board!!!

 

[harryco79]

Pls help Excel gods

 

[mikerickson]

Let me start by saying that I think that it would be a lot more stable if you used Cell B1 rather than an input form.

BUT...this looks like an interesting puzzle.

How do you want to see that "comment" without having to enter another?
Hover over the cell? or Click the cell, read the "comment" as the default of the input form and close without changing it? or ... ?

 

[harryco79]

Thanks for the response. I started off using a cell to capture the txt but because of screensize/spatial restrictions, it was suggested it would be better if a pop up form appeared at the click of a cell that allowed the user input as much text as he/she wanted. Once done the form would be closed. Text is hidden (saved to another worksheet) as it will be viewed not very often.

Does this help?

Let me start by saying that I think that it would be a lot more stable if you used Cell B1 rather than an input form.

BUT...this looks like an interesting puzzle.

How do you want to see that "comment" without having to enter another?
Hover over the cell? or Click the cell, read the "comment" as the default of the input form and close without changing it? or ... ?

 

[harryco79]

I created a graphic to show how it would look like in excel but can figure out how to show it in this thread. The visual may help.

 

[mikerickson]

This solution is UDF based, so that triggering cells and storage cells can be controlled from the worksheet, rather than hardcoded into the VB.

Clicking on a cell cannot be distinguished from tabbing into it, so I used a double click instead.
In the OP, you mentioned Sheet1!$A$1 being the source and the user's entry being stored in Sheet2!$B$2.

The user's experience will be:
Double click on the trigger cell (Sheet1!$A$1).
Type "text" into the dialog box that appears.
Press OK.
Go to Sheet2 and see "Text" in B2.
Go back to Sheet1, hover over A1 and see the cell's comment, "text".

The first UDF is UserEnterComment (ValueToShow, (optional) inputPrompt, (optional) inputTitle)

If user double-clicks on a cell with a formula that uses the function UserEnterComment , a dialog box will appear.
The user's entry will become the text of that cell's comment.

The value returned by UserEnterComment is the value of the first argument.
The inputPrompt and inputTitle arguments are passed to the dialog box.

In the OP example, Sheet1!$A$1 would hold the formula
=SourceOfComment("X", "Question Here")

The three arguments of SourceOfComment are:
ValueToShow - the value that the cell will show
inputPrompt - (optional) the prompt for the dialog box (when double clicked) (default "Enter text.").
inputTitle - (optional) the title for the dialog box (default "Text Input")

Put this in a normal module:

Function UserInputComment(ValueToShow As Variant, _
            Optional inputPrompt As String, Optional inputTitle As String) As Variant
    On Error Resume Next
    With Application.Caller
        .AddComment
        With .Validation
            .Delete
            .Add Type:=xlValidateCustom, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="=true"
            .inputTitle = inputTitle
            .InputMessage = inputPrompt
            .ShowInput = False
            .ShowError = False
            End With
        End With
    On Error GoTo 0
    UserInputComment = ValueToShow
End Function

and this in the ThisWorkbook code module:

Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
    Dim uiComment As String, ioPrompt As String, ioTitle As String
    With Target
    If UCase(.Formula) Like "*USERINPUTCOMMENT(*" Then
        Cancel = True
        ioPrompt = .Validation.InputMessage
        ioTitle = .Validation.inputTitle
        
        'uiComment = Application.InputBox(prompt:=ioPrompt, Default:=.Comment.Text, Title:=ioTitle, Type:=2)
        uiComment = UserForm1.GetInput(strPrompt:=ioPrompt, strDefault:=.Comment.Text, strTitle:=ioTitle)
        
        If uiComment <> "False" Then
            .Comment.Text Text:=uiComment
            .Formula = .Formula
        End If
    End If
    End With
End Sub

Notice that the dialog could be Application.InputBox, but it looks you like you need (and have?) a userform to handle multi-line text entry.
As is, the code invokes UserForm1 (see next post).
The commenting out could be reversed if you prefer an Application.InputBox.

The second UDF is CommentFromCell(sourceRange).

CommentFromCell returns the text of the comment in sourceRange's upper left cell.

For the OP example, Sheet2 B2 would hold the formula
=CommentFromCell(Sheet1!$A$1)

 

[mikerickson]

Application.InputBox is not designed for multi-line text input.
The userform used to test the above had the controls: Label1, TextBox1 and command buttons, butOK and butCancel.
This code, put in the UF's code module, adds the GetInput function to UserForm1.

Option Explicit

Private Sub butCancel_Click()
    Unload Me
End Sub

Private Sub butOK_Click()
    Me.Tag = "OK"
    Me.Hide
End Sub

Public Function GetInput(Optional strPrompt As String = "Enter text", _
                                Optional strTitle As String, _
                                Optional strDefault As String = "", _
                                Optional lngLeft As Long, Optional lngTop As Long) As String

    If strTitle = vbNullString Then strTitle = "Text Input"
    If lngLeft < 0 Then lngLeft = 0
    If lngTop < 0 Then lngTop = 0
    
    With Me
        Rem position form
        If lngLeft * lngTop = 0 Then
            .StartUpPosition = 1: Rem not specified
        Else
            .StartUpPosition = 0
            .Left = lngLeft
            .Top = lngTop
        End If
        Rem set visuals
        .Label1.Caption = strPrompt
        .Caption = strTitle
        With .TextBox1
            .Text = strDefault
            .SelStart = 0
            .SelLength = Len(.Text)
        End With
        .Show
    End With
    
CheckPoint:
    
    With UserForm1
        If .Tag = "OK" Then
            GetInput = .TextBox1.Text
        Else
            GetInput = "False"
        End If
    End With
    GetInput = Replace(Replace(GetInput, vbCrLf, vbCr), vbLf, vbCr)
    Unload UserForm1
End Function

Private Sub UserForm_Initialize()
    Rem properties to be set at design time
    Me.butOK.Default = True
    With Me.TextBox1
        .MultiLine = True
        .WordWrap = True
        .EnterKeyBehavior = True
        .TabKeyBehavior = True
        .AutoWordSelect = False
    End With
    butOK.SetFocus
End Sub

The arguments of UserForm1.GetInput mirror those of InputBox and its used like this

Dim userInput As String

userInput = UserForm1.GetInput("Enter something", "My Title", "default")

If userInput = "False" Then
    MsgBox "cancel pressed"
Else
    MsgBox userInput & " was entered."
End If

The GetInput function can be added to any userform.

 

[harryco79]

Thanks so much for this. I really appreciate it. Working on it now and will post results.

 

[harryco79]

Do i add the last bit of VB below in the UF Module??

Dim userInput As String
userInput = UserForm1.GetInput("Enter something", "My Title", "default")

If userInput = "False" Then
MsgBox "cancel pressed"
Else
MsgBox userInput & " was entered."
End If

 

[mikerickson]

That was a code snippet, showing the way to access a userform that has the .GetInput function. It is not needed for your project.