Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Edit Comment from UserForm
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. -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200809/1 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Edit Comment from UserForm
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" <u45578@uwe wrote in message news:8a3d0eefa903b@uwe... 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. -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200809/1 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Edit Comment from UserForm
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? Peter T wrote: 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. -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200809/1 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Edit Comment from UserForm
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 "Joe_Hunt via OfficeKB.com" <u45578@uwe wrote in message news:8a48758a7579e@uwe... 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? Peter T wrote: 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. -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200809/1 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Comment Edit | Excel Worksheet Functions | |||
VBA to edit a comment | Excel Discussion (Misc queries) | |||
How can I edit a comment w/o first having to select Show Comment | Excel Discussion (Misc queries) | |||
cannot edit comment | Excel Discussion (Misc queries) | |||
Edit Comment Box | Excel Programming |