Text Box contents to cell comments

C

CoolBusiness

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
 
J

JLatham

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.
 
C

CoolBusiness

I'll play with that and let you know if I get stuck. Thanks for the idea!

JLatham said:
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 said:
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
Click to expand...
Click to expand...
 
J

JLatham

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 said:
I'll play with that and let you know if I get stuck. Thanks for the idea!

JLatham said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
C

CoolBusiness

With a few change to code, worked out beautifully! Thanks for your help!

JLatham said:
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 said:
I'll play with that and let you know if I get stuck. Thanks for the idea!

JLatham said:
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.

:

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
Click to expand...
Click to expand...
Click to expand...
 
J

JLatham

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 said:
With a few change to code, worked out beautifully! Thanks for your help!

JLatham said:
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 said:
I'll play with that and let you know if I get stuck. Thanks for the idea!

:

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.

:

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
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Display text box contents in cell 1
Replacing Input Box in Macro with Info Picked Up from Active Cell? 3
Duplicate the contents of a row when text is entered in a cell 10
Add Comments with Text From Cells 1
lock text box to cell 4
Store cell & workbook references, to retrieve later in Macro 3
Display cell contents in a text box (shape) and linking 2
Text box value lost 2

Top