Userform Comment box popup

**R0bert0** · 12-11-2007, 07:39 AM

Hi, apologies if I'm posting an already existing topic but I did a quick search and couldn't find anything similar so....

What I need to do is this...

I have a holiday / visit planner and would like to add some functionality so the user can double click on a particular cell (corresponding to a date) and a userform text box will open up to enable him / her to enter some comments. Once done the user can click an OK button which transfers the text into the cell comments area.

I have the worksheet protected so the users cannot enter comments with the usual 'insert comment' method.

any ideas? thanks!

**royUK** · 12-11-2007, 09:18 AM

Add this code to the worksheet that you want to use it with.then double click any cell to add ooooooooooa comment or add to an existing comment.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Excel.Range, Cancel As Boolean)
    On Error Resume Next
    Dim sCmt As String
    Dim sInput As String

    If Target.Comment Is Nothing Then
        sCmt = InputBox("Enter comment:", "Memo")
        If sCmt = "" Then Exit Sub
        Target.AddComment Text:=sCmt
        Target.Comment.Visible = True
        Target.Comment.Shape.TextFrame.AutoSize = True
    ElseIf Target.Comment.Text <> "" Then
        sInput = InputBox("Enter info:", "Comment Info")
        If sInput = "" Then Exit Sub
        sCmt = Target.Comment.Text & Chr(10) & sInput
    Else
        sCmt = InputBox("Enter info:", "Comment Info")
    End If
    With Target
        .ClearComments
        .AddComment Text:=sCmt
        .Comment.Visible = True
        .Comment.Shape.TextFrame.AutoSize = True    'Remove if you want to size it yourself
    End With

    Cancel = True    'Remove this if you want to enter text in the cell after you add the comment
End Sub

**R0bert0** · 12-11-2007, 09:48 AM

Hi Roy,

Thanks for the reply! Works great except for a couple of little niggles.

I just realised I forgot to mention in my original post that the 'date' cells all contain formulas so they are not blank. This poses a problem when the comments popup is cancelled and the cell is left in edit mode with the user free to edit the formula. Not good where my users are concerned

Also, the comments box and indicator only appear after OK is clicked when the worksheet is unprotected. Any way this can be modified to show up when worksheet protection is on?

Lastly, is it possible to specify the size of the text as it appears in the box once ok'd? The default size is a bit small as the spreadsheet is zoomed out to accommodate as much info as possible.

Thanks for your help I really appreciate it!

Userform Comment box popup

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Userform Comment box popup

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions