| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Text Box contents to cell comments
Hello,
I have a macro that allows for budget request rationale to be entered into the comments area of a cell. Is there a bit of code that will take whatever input is typed or pasted into a text box appearing on the spreadsheet page and place it into the active cell's comments? The current macro I've written now brings up an Input Box but the area to enter info is small. I was thinking that an auto resizing text box could be used to enter rationale and the macro transfer the text inputted to the comment box. There are many line items so this process would run each time the user is ready to attach their rationale. Present code is below. Any help to use a text box vs. an Input box would be very much appreciated! Thanks. David Sub Input_Budget_Rationale() ' 'Input_Budget_Rationale Macro ' ' ' ' Dim RationaleBox As String On Error GoTo WrongCell ActiveSheet.Unprotect ActiveCell.Comment.Text Text:="" If ActiveCell.Value = Empty Then GoTo ContinueRationale GoTo OverwriteRationale: ContinueRationale: RationaleBox = InputBox("Please type in (or cut and paste) your Budget Rationale here. Press OK when complete.") If RationaleBox = "" Then GoTo RationaleCancel ActiveCell.Value = RationaleBox ActiveCell.Comment.Text Text:=RationaleBox ActiveCell.Value = "Rationale Entered" GoTo RationaleOK OverwriteRationale: If MsgBox("Rationale has been entered previously. Overwrite it?", vbYesNo) = vbNo Then GoTo RationaleCancel Else RationaleBox = InputBox("Please type in (or cut and paste) your Budget Rationale here. Press OK when complete.") ActiveCell.Value = RationaleBox ActiveCell.Comment.Text Text:=RationaleBox ActiveCell.Value = "Rationale Entered" GoTo RationaleOverwritten WrongCell: Range("Wrong_Cell_Flag") = "Yes" Calculate MsgBox ("You've selected an Invalid cell location for entering Rationale. Make sure you select the cell in the ''Rationale'' Column that is in the same row for the account.") Range("Wrong_Cell_Flag") = "No" Calculate GoTo RationaleEnd RationaleOverwritten: MsgBox ("Rationale has been REPLACED.") Call Resize_Comment_Box_All GoTo RationaleEnd RationaleOK: MsgBox ("Rationale has been SAVED.") Call Resize_Comment_Box_All GoTo RationaleEnd RationaleCancel: MsgBox ("NO CHANGES to this Rationale have been made.") GoTo RationaleEnd RationaleBlank: ActiveCell.Comment.Text Text:="" GoTo RationaleEnd RationaleEnd: ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:=True End Sub |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Text Box contents to cell comments
Have you considered using a UserForm instead of the InputBox() ?
You could create a simple form that has all of the logic of your Input_Budget_Rationale() routine in it, with that routine simply opening the userform itself, as: Sub Input_Budget_Rationale() UserForm1.Show End Sub The form would have a label or two with the instructions/prompts in it, a large text box for data entry (make it whatever size you want during the design), and a couple of command buttons as [Add Rationale to Comment] and [Cancel - No Rationale to Add] or something like that. ActiveCell and its attributes (.Value and .Text) can still be addressed from within the UserForm's code. "CoolBusiness" wrote: Hello, I have a macro that allows for budget request rationale to be entered into the comments area of a cell. Is there a bit of code that will take whatever input is typed or pasted into a text box appearing on the spreadsheet page and place it into the active cell's comments? The current macro I've written now brings up an Input Box but the area to enter info is small. I was thinking that an auto resizing text box could be used to enter rationale and the macro transfer the text inputted to the comment box. There are many line items so this process would run each time the user is ready to attach their rationale. Present code is below. Any help to use a text box vs. an Input box would be very much appreciated! Thanks. David Sub Input_Budget_Rationale() ' 'Input_Budget_Rationale Macro ' ' ' ' Dim RationaleBox As String On Error GoTo WrongCell ActiveSheet.Unprotect ActiveCell.Comment.Text Text:="" If ActiveCell.Value = Empty Then GoTo ContinueRationale GoTo OverwriteRationale: ContinueRationale: RationaleBox = InputBox("Please type in (or cut and paste) your Budget Rationale here. Press OK when complete.") If RationaleBox = "" Then GoTo RationaleCancel ActiveCell.Value = RationaleBox ActiveCell.Comment.Text Text:=RationaleBox ActiveCell.Value = "Rationale Entered" GoTo RationaleOK OverwriteRationale: If MsgBox("Rationale has been entered previously. Overwrite it?", vbYesNo) = vbNo Then GoTo RationaleCancel Else RationaleBox = InputBox("Please type in (or cut and paste) your Budget Rationale here. Press OK when complete.") ActiveCell.Value = RationaleBox ActiveCell.Comment.Text Text:=RationaleBox ActiveCell.Value = "Rationale Entered" GoTo RationaleOverwritten WrongCell: Range("Wrong_Cell_Flag") = "Yes" Calculate MsgBox ("You've selected an Invalid cell location for entering Rationale. Make sure you select the cell in the ''Rationale'' Column that is in the same row for the account.") Range("Wrong_Cell_Flag") = "No" Calculate GoTo RationaleEnd RationaleOverwritten: MsgBox ("Rationale has been REPLACED.") Call Resize_Comment_Box_All GoTo RationaleEnd RationaleOK: MsgBox ("Rationale has been SAVED.") Call Resize_Comment_Box_All GoTo RationaleEnd RationaleCancel: MsgBox ("NO CHANGES to this Rationale have been made.") GoTo RationaleEnd RationaleBlank: ActiveCell.Comment.Text Text:="" GoTo RationaleEnd RationaleEnd: ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:=True End Sub |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Text Box contents to cell comments
I'll play with that and let you know if I get stuck. Thanks for the idea!
"JLatham" wrote: Have you considered using a UserForm instead of the InputBox() ? You could create a simple form that has all of the logic of your Input_Budget_Rationale() routine in it, with that routine simply opening the userform itself, as: Sub Input_Budget_Rationale() UserForm1.Show End Sub The form would have a label or two with the instructions/prompts in it, a large text box for data entry (make it whatever size you want during the design), and a couple of command buttons as [Add Rationale to Comment] and [Cancel - No Rationale to Add] or something like that. ActiveCell and its attributes (.Value and .Text) can still be addressed from within the UserForm's code. "CoolBusiness" wrote: Hello, I have a macro that allows for budget request rationale to be entered into the comments area of a cell. Is there a bit of code that will take whatever input is typed or pasted into a text box appearing on the spreadsheet page and place it into the active cell's comments? The current macro I've written now brings up an Input Box but the area to enter info is small. I was thinking that an auto resizing text box could be used to enter rationale and the macro transfer the text inputted to the comment box. There are many line items so this process would run each time the user is ready to attach their rationale. Present code is below. Any help to use a text box vs. an Input box would be very much appreciated! Thanks. David Sub Input_Budget_Rationale() ' 'Input_Budget_Rationale Macro ' ' ' ' Dim RationaleBox As String On Error GoTo WrongCell ActiveSheet.Unprotect ActiveCell.Comment.Text Text:="" If ActiveCell.Value = Empty Then GoTo ContinueRationale GoTo OverwriteRationale: ContinueRationale: RationaleBox = InputBox("Please type in (or cut and paste) your Budget Rationale here. Press OK when complete.") If RationaleBox = "" Then GoTo RationaleCancel ActiveCell.Value = RationaleBox ActiveCell.Comment.Text Text:=RationaleBox ActiveCell.Value = "Rationale Entered" GoTo RationaleOK OverwriteRationale: If MsgBox("Rationale has been entered previously. Overwrite it?", vbYesNo) = vbNo Then GoTo RationaleCancel Else RationaleBox = InputBox("Please type in (or cut and paste) your Budget Rationale here. Press OK when complete.") ActiveCell.Value = RationaleBox ActiveCell.Comment.Text Text:=RationaleBox ActiveCell.Value = "Rationale Entered" GoTo RationaleOverwritten WrongCell: Range("Wrong_Cell_Flag") = "Yes" Calculate MsgBox ("You've selected an Invalid cell location for entering Rationale. Make sure you select the cell in the ''Rationale'' Column that is in the same row for the account.") Range("Wrong_Cell_Flag") = "No" Calculate GoTo RationaleEnd RationaleOverwritten: MsgBox ("Rationale has been REPLACED.") Call Resize_Comment_Box_All GoTo RationaleEnd RationaleOK: MsgBox ("Rationale has been SAVED.") Call Resize_Comment_Box_All GoTo RationaleEnd RationaleCancel: MsgBox ("NO CHANGES to this Rationale have been made.") GoTo RationaleEnd RationaleBlank: ActiveCell.Comment.Text Text:="" GoTo RationaleEnd RationaleEnd: ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:=True End Sub |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Text Box contents to cell comments
If you have trouble with the form, let me know and I'll try to help. Don't
know how much experience you have working with them, but it's pretty much the same as in your code as far as moving data from/to the worksheet, just reference the name of the text box on either side of the = symbol, as TextBox1.Text = ActiveCell.Comment.Text or to get what they entered back to the comment: ActiveCell.Comment.Text = TextBox1.Text Feel free to email me if you need to at (without the spaces, of course) Help From @ jlatham site.com "CoolBusiness" wrote: I'll play with that and let you know if I get stuck. Thanks for the idea! "JLatham" wrote: Have you considered using a UserForm instead of the InputBox() ? You could create a simple form that has all of the logic of your Input_Budget_Rationale() routine in it, with that routine simply opening the userform itself, as: Sub Input_Budget_Rationale() UserForm1.Show End Sub The form would have a label or two with the instructions/prompts in it, a large text box for data entry (make it whatever size you want during the design), and a couple of command buttons as [Add Rationale to Comment] and [Cancel - No Rationale to Add] or something like that. ActiveCell and its attributes (.Value and .Text) can still be addressed from within the UserForm's code. "CoolBusiness" wrote: Hello, I have a macro that allows for budget request rationale to be entered into the comments area of a cell. Is there a bit of code that will take whatever input is typed or pasted into a text box appearing on the spreadsheet page and place it into the active cell's comments? The current macro I've written now brings up an Input Box but the area to enter info is small. I was thinking that an auto resizing text box could be used to enter rationale and the macro transfer the text inputted to the comment box. There are many line items so this process would run each time the user is ready to attach their rationale. Present code is below. Any help to use a text box vs. an Input box would be very much appreciated! Thanks. David Sub Input_Budget_Rationale() ' 'Input_Budget_Rationale Macro ' ' ' ' Dim RationaleBox As String On Error GoTo WrongCell ActiveSheet.Unprotect ActiveCell.Comment.Text Text:="" If ActiveCell.Value = Empty Then GoTo ContinueRationale GoTo OverwriteRationale: ContinueRationale: RationaleBox = InputBox("Please type in (or cut and paste) your Budget Rationale here. Press OK when complete.") If RationaleBox = "" Then GoTo RationaleCancel ActiveCell.Value = RationaleBox ActiveCell.Comment.Text Text:=RationaleBox ActiveCell.Value = "Rationale Entered" GoTo RationaleOK OverwriteRationale: If MsgBox("Rationale has been entered previously. Overwrite it?", vbYesNo) = vbNo Then GoTo RationaleCancel Else RationaleBox = InputBox("Please type in (or cut and paste) your Budget Rationale here. Press OK when complete.") ActiveCell.Value = RationaleBox ActiveCell.Comment.Text Text:=RationaleBox ActiveCell.Value = "Rationale Entered" GoTo RationaleOverwritten WrongCell: Range("Wrong_Cell_Flag") = "Yes" Calculate MsgBox ("You've selected an Invalid cell location for entering Rationale. Make sure you select the cell in the ''Rationale'' Column that is in the same row for the account.") Range("Wrong_Cell_Flag") = "No" Calculate GoTo RationaleEnd RationaleOverwritten: MsgBox ("Rationale has been REPLACED.") Call Resize_Comment_Box_All GoTo RationaleEnd RationaleOK: MsgBox ("Rationale has been SAVED.") Call Resize_Comment_Box_All GoTo RationaleEnd RationaleCancel: MsgBox ("NO CHANGES to this Rationale have been made.") GoTo RationaleEnd RationaleBlank: ActiveCell.Comment.Text Text:="" GoTo RationaleEnd RationaleEnd: ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:=True End Sub |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Text Box contents to cell comments
With a few change to code, worked out beautifully! Thanks for your help!
"JLatham" wrote: If you have trouble with the form, let me know and I'll try to help. Don't know how much experience you have working with them, but it's pretty much the same as in your code as far as moving data from/to the worksheet, just reference the name of the text box on either side of the = symbol, as TextBox1.Text = ActiveCell.Comment.Text or to get what they entered back to the comment: ActiveCell.Comment.Text = TextBox1.Text Feel free to email me if you need to at (without the spaces, of course) Help From @ jlatham site.com "CoolBusiness" wrote: I'll play with that and let you know if I get stuck. Thanks for the idea! "JLatham" wrote: Have you considered using a UserForm instead of the InputBox() ? You could create a simple form that has all of the logic of your Input_Budget_Rationale() routine in it, with that routine simply opening the userform itself, as: Sub Input_Budget_Rationale() UserForm1.Show End Sub The form would have a label or two with the instructions/prompts in it, a large text box for data entry (make it whatever size you want during the design), and a couple of command buttons as [Add Rationale to Comment] and [Cancel - No Rationale to Add] or something like that. ActiveCell and its attributes (.Value and .Text) can still be addressed from within the UserForm's code. "CoolBusiness" wrote: Hello, I have a macro that allows for budget request rationale to be entered into the comments area of a cell. Is there a bit of code that will take whatever input is typed or pasted into a text box appearing on the spreadsheet page and place it into the active cell's comments? The current macro I've written now brings up an Input Box but the area to enter info is small. I was thinking that an auto resizing text box could be used to enter rationale and the macro transfer the text inputted to the comment box. There are many line items so this process would run each time the user is ready to attach their rationale. Present code is below. Any help to use a text box vs. an Input box would be very much appreciated! Thanks. David Sub Input_Budget_Rationale() ' 'Input_Budget_Rationale Macro ' ' ' ' Dim RationaleBox As String On Error GoTo WrongCell ActiveSheet.Unprotect ActiveCell.Comment.Text Text:="" If ActiveCell.Value = Empty Then GoTo ContinueRationale GoTo OverwriteRationale: ContinueRationale: RationaleBox = InputBox("Please type in (or cut and paste) your Budget Rationale here. Press OK when complete.") If RationaleBox = "" Then GoTo RationaleCancel ActiveCell.Value = RationaleBox ActiveCell.Comment.Text Text:=RationaleBox ActiveCell.Value = "Rationale Entered" GoTo RationaleOK OverwriteRationale: If MsgBox("Rationale has been entered previously. Overwrite it?", vbYesNo) = vbNo Then GoTo RationaleCancel Else RationaleBox = InputBox("Please type in (or cut and paste) your Budget Rationale here. Press OK when complete.") ActiveCell.Value = RationaleBox ActiveCell.Comment.Text Text:=RationaleBox ActiveCell.Value = "Rationale Entered" GoTo RationaleOverwritten WrongCell: Range("Wrong_Cell_Flag") = "Yes" Calculate MsgBox ("You've selected an Invalid cell location for entering Rationale. Make sure you select the cell in the ''Rationale'' Column that is in the same row for the account.") Range("Wrong_Cell_Flag") = "No" Calculate GoTo RationaleEnd RationaleOverwritten: MsgBox ("Rationale has been REPLACED.") Call Resize_Comment_Box_All GoTo RationaleEnd RationaleOK: MsgBox ("Rationale has been SAVED.") Call Resize_Comment_Box_All GoTo RationaleEnd RationaleCancel: MsgBox ("NO CHANGES to this Rationale have been made.") GoTo RationaleEnd RationaleBlank: ActiveCell.Comment.Text Text:="" GoTo RationaleEnd RationaleEnd: ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:=True End Sub |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Text Box contents to cell comments
Good to hear that. Glad I was able to help a little, and your feedback is
very much appreciated - often we don't know if a suggestion helped or not and leaves us wondering if someone asking for help is still sitting frustrated or not. "CoolBusiness" wrote: With a few change to code, worked out beautifully! Thanks for your help! "JLatham" wrote: If you have trouble with the form, let me know and I'll try to help. Don't know how much experience you have working with them, but it's pretty much the same as in your code as far as moving data from/to the worksheet, just reference the name of the text box on either side of the = symbol, as TextBox1.Text = ActiveCell.Comment.Text or to get what they entered back to the comment: ActiveCell.Comment.Text = TextBox1.Text Feel free to email me if you need to at (without the spaces, of course) Help From @ jlatham site.com "CoolBusiness" wrote: I'll play with that and let you know if I get stuck. Thanks for the idea! "JLatham" wrote: Have you considered using a UserForm instead of the InputBox() ? You could create a simple form that has all of the logic of your Input_Budget_Rationale() routine in it, with that routine simply opening the userform itself, as: Sub Input_Budget_Rationale() UserForm1.Show End Sub The form would have a label or two with the instructions/prompts in it, a large text box for data entry (make it whatever size you want during the design), and a couple of command buttons as [Add Rationale to Comment] and [Cancel - No Rationale to Add] or something like that. ActiveCell and its attributes (.Value and .Text) can still be addressed from within the UserForm's code. "CoolBusiness" wrote: Hello, I have a macro that allows for budget request rationale to be entered into the comments area of a cell. Is there a bit of code that will take whatever input is typed or pasted into a text box appearing on the spreadsheet page and place it into the active cell's comments? The current macro I've written now brings up an Input Box but the area to enter info is small. I was thinking that an auto resizing text box could be used to enter rationale and the macro transfer the text inputted to the comment box. There are many line items so this process would run each time the user is ready to attach their rationale. Present code is below. Any help to use a text box vs. an Input box would be very much appreciated! Thanks. David Sub Input_Budget_Rationale() ' 'Input_Budget_Rationale Macro ' ' ' ' Dim RationaleBox As String On Error GoTo WrongCell ActiveSheet.Unprotect ActiveCell.Comment.Text Text:="" If ActiveCell.Value = Empty Then GoTo ContinueRationale GoTo OverwriteRationale: ContinueRationale: RationaleBox = InputBox("Please type in (or cut and paste) your Budget Rationale here. Press OK when complete.") If RationaleBox = "" Then GoTo RationaleCancel ActiveCell.Value = RationaleBox ActiveCell.Comment.Text Text:=RationaleBox ActiveCell.Value = "Rationale Entered" GoTo RationaleOK OverwriteRationale: If MsgBox("Rationale has been entered previously. Overwrite it?", vbYesNo) = vbNo Then GoTo RationaleCancel Else RationaleBox = InputBox("Please type in (or cut and paste) your Budget Rationale here. Press OK when complete.") ActiveCell.Value = RationaleBox ActiveCell.Comment.Text Text:=RationaleBox ActiveCell.Value = "Rationale Entered" GoTo RationaleOverwritten WrongCell: Range("Wrong_Cell_Flag") = "Yes" Calculate MsgBox ("You've selected an Invalid cell location for entering Rationale. Make sure you select the cell in the ''Rationale'' Column that is in the same row for the account.") Range("Wrong_Cell_Flag") = "No" Calculate GoTo RationaleEnd RationaleOverwritten: MsgBox ("Rationale has been REPLACED.") Call Resize_Comment_Box_All GoTo RationaleEnd RationaleOK: MsgBox ("Rationale has been SAVED.") Call Resize_Comment_Box_All GoTo RationaleEnd RationaleCancel: MsgBox ("NO CHANGES to this Rationale have been made.") GoTo RationaleEnd RationaleBlank: ActiveCell.Comment.Text Text:="" GoTo RationaleEnd RationaleEnd: ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:=True End Sub |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| View cell contents as a pop-up window (similar to comments window) | Excel Worksheet Functions | |||
| How to copy comments to cell as normal text? | Excel Discussion (Misc queries) | |||
| print comments using cell contents, not cell# | Excel Discussion (Misc queries) | |||
| Cell only shows link in text, not contents of reference cell | New Users to Excel | |||
| Can Comments be automatically converted to text cell values? | Excel Discussion (Misc queries) | |||
Linear Mode
