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