Sleeper: Userform pop-up when inserting a comment

[malik641]

Is there a way to have a userform pop-up when the user inserts a comment?

I want to make a userform only to come up when a user inserts a comment. Instead of the standard comment box come up, the userform would display and I will give it certain options and such. But I just want to know if I can interrupt the normal "Insert Comment" command and have my userform pop-up.

Possible???

[JKwan]

Sub AddComment()
    On Error Resume Next
    Dim cmtMsg As String, nwLne As String, LneCnt As Long, shCmt As Long
    If TypeName(Selection) <> "Range" Then Exit Sub
AddLine:
    nwLne = InputBox("Text Line: " & LneCnt + 1 & vbNewLine & vbNewLine & _
                "New Text Lines are added automatically." & vbNewLine & _
                "Leave blank or push ""Cancel"" to exit.", "Please write your comment")
    If LneCnt > 0 Then cmtMsg = cmtMsg & Chr(10) & nwLne Else cmtMsg = nwLne
    LneCnt LneCnt + 1
    If nwLne <> "" Then GoTo AddLine
    cmtMsg = Left(cmtMsg, Len(cmtMsg) - 1)
    Application.ScreenUpdating = False
    With ActiveCell
        .ClearComments
        .AddComment
        With .Comment
            .Visible = True
            .Shape.AutoShapeType = msoShapeRoundedRectangle
            .Shape.Shadow.Visible = msoFalse
            .Shape.Select True
            .Text Text:=cmtMsg
            If cmtMsg <> "" Then .Shape.TextFrame.AutoSize = True
            shCmt = MsgBox("Do you want the comment to remain visible? ", _
                    vbYesNo, "Keep comment visible?")
            If shCmt = vbNo Then .Visible = False
            End With
        .Select
    End With
    Application.ScreenUpdating = True
End Sub

Found this, have a go at it.

[mark007]

To intercept "Insert comment" you need to override the toolbar options in all the places it is found. For example the right-click menu can be overriden with:

Application.CommandBars("Cell").Controls("Insert Comment").OnAction = "addmycomment"

Run that and selecting Insert Comment on the right click menu will run the sub addmycomment(). In this you can get it to display the userform. At this point you can access the activecell's properties to determine the current comment and then save a new one on closing the form.

To completely override it you should make the changes in every menu it appears.

To return the option to normal you would then use:

Application.CommandBars("Cell").Controls("Insert Comment").Reset

I strongly reccommend that if this isn't just for personal use then the overridden actions are retured to normal on closing your workbook/addin containing the code.

[malik641]

That's exactly what I was looking for! Thanks 007! I'll be having fun with that later today. We'll see how that goes.

P.S. how do you COMPLETELY override it?

To intercept "Insert comment" you need to override the toolbar options in all the places it is found. For example the right-click menu can be overriden with:

Application.CommandBars("Cell").Controls("Insert Comment").OnAction = "addmycomment"


Run that and selecting Insert Comment on the right click menu will run the sub addmycomment(). In this you can get it to display the userform. At this point you can access the activecell's properties to determine the current comment and then save a new one on closing the form.

To completely override it you should make the changes in every menu it appears.

To return the option to normal you would then use:

Application.CommandBars("Cell").Controls("Insert Comment").Reset

I strongly reccommend that if this isn't just for personal use then the overridden actions are retured to normal on closing your workbook/addin containing the code.

[malik641]

Also, how can I have this work for an event procedure such as Worksheet_Activate to turn this option on, and Worksheet_Deactivate to turn the option off???

It gives me this error whenever I change the sheet:

Runtime Error: '91'
Object Variable or With Block variable not set

And it highlights the whole code (i.e. "Application....."). When I change the sheet to another that I DON'T want the Comments option changed...It highlights the Worksheet_Deactivate code, and vice versa.

here are my two codes:

Private Sub Worksheet_Activate() 
Application.CommandBars("Cell").Controls("Insert Comment").OnAction _
= "AddComment"
End Sub
 
Private Sub Worksheet_Deactivate()
Application.CommandBars("Cell").Controls("Insert Comment").Reset
End Sub

What's the matter?

[mark007]

You can't completely override it per se - this is the only way of doing it. You could put it in an addin that always loaded though to give the effect of it.

As for the error it's probably that the control is perhaps named slightly differently in your version of Excel.

Try the following in the immediate window:

?Application.CommandBars("Cell").Controls(1).Caption

Increase the 1 by 1 until the result is the control you want to change then use that as the name in your code.

[malik641]

Tried it and I replaced "Insert Comment" with "Insert Co&mment" and it still does the same thing. I noticed something else, though. If I press stop and I go to the sheet with the macro it works fine (go there from another sheet). I'll add a comment, and my temp dialog box (from the code above) comes up and it works fine. I'll go to another sheet and test to see if the command reset itself, and it does. But the moment I go back INTO the sheet I get the error.....and if I press stop and try to go to the other sheet I get the error again.

WHY!!!!???? lol