Edit Comment from UserForm

[Joe_Hunt via OfficeKB.com]

Hello all, and thank you for all the help you've given me, even though most
it has been just looking through the archives! I'm still working on
shortening my learning curve. I haven't been able to find a clear-cut answer
on how I might be able to edit a comment from a UserForm, and I was hoping
someone could help me with that. I have a workbook that contains data on over
100 items that need to be adjusted occasionally after the preliminary work is
done (that's why they call it preliminary I guess). Because there are so many
worksheets I've created a UserForm that I can use to scroll through each page
and look at only the relavent data as I go. In addition, there is a macro I
run that loops through each sheet pulling a couple of points of data and
inserting into a comment box on a specified cell each month. Occasionally I
need to edit that comment and it would be nice if I could do so without
closing the UserForm, either by clicking a command button that would bring up
what was already there then I could modify it and save it back to the
worksheet, or through a textbox. Is this possible? I would really appreciate
any help you can give me.

 

[Peter T]

Try the following for ideas.
With the curser in the textbox press F12 to 'get' next comment text and
Ctrl-F12 to 'set' comment text.

Private Sub TextBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, _
ByVal Shift As Integer)
Dim s As String, sUN As String
Static n As Long

' press F12 to write next comment to textbox
' press Ctrl-F12 to write textbox text to comment

If KeyCode = vbKeyF12 Then
If Shift = 2 Then
If Not mCom Is Nothing Then
sUN = Application.UserName & ":"
s = Replace(TextBox1.Text, vbCrLf, vbLf)
If Left(s, Len(sUN)) <> sUN Then
s = sUN & vbLf & s
End If

mCom.Text Text:=s

mCom.Shape.TextFrame.Characters.Font.Bold = False
pos = InStr(2, s, vbLf)
If pos Then
mCom.Shape.TextFrame.Characters(1, pos - 1).Font.Bold =
True
End If
End If

Else
cnt = ActiveSheet.Comments.Count
If cnt Then
n = n + 1
If n > cnt Then n = 1
Set mCom = ActiveSheet.Comments(n)
TextBox1.Text = mCom.Text
Me.Caption = "Comment in cell " & mCom.Parent.Address(0, 0)
End If
End If
End If
End Sub

Up to you if you want to include the stuff about Username and first line
bold.

Regards,
Peter T

 

[Joe_Hunt via OfficeKB.com]

Thank you Peter. I ran into a problem though. When I tried to run this code I
got a syntax error on this line:

mCom.Shape.TextFrame.Characters(1, pos - 1).Font.Bold = True

Any thoughts?

Try the following for ideas.
With the curser in the textbox press F12 to 'get' next comment text and
Ctrl-F12 to 'set' comment text.

Private Sub TextBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, _
ByVal Shift As Integer)
Dim s As String, sUN As String
Static n As Long

' press F12 to write next comment to textbox
' press Ctrl-F12 to write textbox text to comment

If KeyCode = vbKeyF12 Then
If Shift = 2 Then
If Not mCom Is Nothing Then
sUN = Application.UserName & ":"
s = Replace(TextBox1.Text, vbCrLf, vbLf)
If Left(s, Len(sUN)) <> sUN Then
s = sUN & vbLf & s
End If

mCom.Text Text:=s

mCom.Shape.TextFrame.Characters.Font.Bold = False
pos = InStr(2, s, vbLf)
If pos Then
mCom.Shape.TextFrame.Characters(1, pos - 1).Font.Bold =
True
End If
End If

Else
cnt = ActiveSheet.Comments.Count
If cnt Then
n = n + 1
If n > cnt Then n = 1
Set mCom = ActiveSheet.Comments(n)
TextBox1.Text = mCom.Text
Me.Caption = "Comment in cell " & mCom.Parent.Address(0, 0)
End If
End If
End If
End Sub

Up to you if you want to include the stuff about Username and first line
bold.

Regards,
Peter T

Hello all, and thank you for all the help you've given me, even though
most

[quoted text clipped - 22 lines]

appreciate
any help you can give me.

 

[Peter T]

Not sure. If you comment that line does the rest work OK.

In theory that code should format the first comment line bold.

Does it error in all cases, try applying with different text and lines
(don't forget need to press Ctrl-Enter to force a new line in the textbox

Replace that line with the following

On error resume next
mCom.Shape.TextFrame.Characters(1, pos - 1).Font.Bold = True

If err.number then
msgbox err.description,,pos
err.clear
end if
on error goto 0

What's the error description, also what's the text and 'pos'

Which Excel version ?

Regards,
Peter T

Thank you Peter. I ran into a problem though. When I tried to run this
code I
got a syntax error on this line:

mCom.Shape.TextFrame.Characters(1, pos - 1).Font.Bold = True

Any thoughts?

Try the following for ideas.
With the curser in the textbox press F12 to 'get' next comment text and
Ctrl-F12 to 'set' comment text.

Private Sub TextBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, _
ByVal Shift As Integer)
Dim s As String, sUN As String
Static n As Long

' press F12 to write next comment to textbox
' press Ctrl-F12 to write textbox text to comment

If KeyCode = vbKeyF12 Then
If Shift = 2 Then
If Not mCom Is Nothing Then
sUN = Application.UserName & ":"
s = Replace(TextBox1.Text, vbCrLf, vbLf)
If Left(s, Len(sUN)) <> sUN Then
s = sUN & vbLf & s
End If

mCom.Text Text:=s

mCom.Shape.TextFrame.Characters.Font.Bold = False
pos = InStr(2, s, vbLf)
If pos Then
mCom.Shape.TextFrame.Characters(1, pos - 1).Font.Bold
=
True
End If
End If

Else
cnt = ActiveSheet.Comments.Count
If cnt Then
n = n + 1
If n > cnt Then n = 1
Set mCom = ActiveSheet.Comments(n)
TextBox1.Text = mCom.Text
Me.Caption = "Comment in cell " & mCom.Parent.Address(0,
0)
End If
End If
End If
End Sub

Up to you if you want to include the stuff about Username and first line
bold.

Regards,
Peter T

Hello all, and thank you for all the help you've given me, even though
most

[quoted text clipped - 22 lines]

appreciate
any help you can give me.