PDA

View Full Version : Sleeper: Userform pop-up when inserting a comment



malik641
07-27-2005, 10:33 AM
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
07-27-2005, 10:59 AM
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
07-27-2005, 06:28 PM
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
07-28-2005, 06:55 AM
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
07-28-2005, 08:17 AM
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
07-28-2005, 08:42 AM
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
07-28-2005, 08:56 AM
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