inputBox if cell >" ?

[BigDave]

In my worbook have a column of 20 cells. Any one of the cells, up to all 20, can have text in them. If there is text in those cells, I want/need an inputBox to appear so that a comment can be typed in. The result of the input box will go to a cell out of sight on the same page of the workbook in a relative location to the 20 cells.

I've thought about having commandbutton next to each cell so that the user could select which one she/he wants. That much vba I know and can do. The other thought is to press a single button and programmitically search out the text filled cells and prompt the user for a comment (I like this idea a bit better), but only if the comment can be placed relative to cell that prompted the action.

[MrShorty]

Here's the beginnings of a solution. It's not very refined, but it gives you a starting point

Function commenttext(incell as string) as string
If incell>"" then commenttext=inputbox("enter comment for " & incell)
end function

Then in the cell where you want to put the comment text enter the function =commenttext(A1)

Not very refined, but it gives you a start.

Or someone will come along with a completely different approach, and if you like that one better, go for it.

[Jason Morin]

Let's assume the cells are A1:A20 and comments would go into A101:A121. Try:

Sub InsertComments()

Dim cell As Range
Dim rTextCells As Range
Dim rCommCells As Range
Dim strComment As String
Dim strMsgBoxAns As Long
Dim strInputMsg As String
Dim strMsgBoxMsg As String

Set rTextCells = ActiveSheet.[A1:A20]
Set rCommCells = ActiveSheet.[A101:A121]

strInputMsg = "Enter a comment for cell "
strMsgBoxMsg = "You hit cancel or didn't " & _
"enter a comment. Move on?"


For Each cell In rTextCells
With cell
If Not IsEmpty(.Value) Then
GoBack:
strComment = InputBox(strInputMsg & _
.Address(False, False) & ":")
If strComment <> "" Then
rCommCells(.Row).Value = strComment
Else
strMsgBoxAns = MsgBox(strMsgBoxMsg, vbYesNo)
If strMsgBoxAns = vbNo Then
GoTo GoBack
End If
End If
End If
End With
Next

End Sub

---
HTH
Jason
Atlanta, GA

"BigDave" wrote:

>
> In my worbook have a column of 20 cells. Any one of the cells, up to
> all 20, can have text in them. If there is text in those cells, I
> want/need an inputBox to appear so that a comment can be typed in. The
> result of the input box will go to a cell out of sight on the same page
> of the workbook in a relative location to the 20 cells.
>
> I've thought about having commandbutton next to each cell so that the
> user could select which one she/he wants. That much vba I know and can
> do. The other thought is to press a single button and programmitically
> search out the text filled cells and prompt the user for a comment (I
> like this idea a bit better), but only if the comment can be placed
> relative to cell that prompted the action.
>
>
> --
> BigDave
> ------------------------------------------------------------------------
> BigDave's Profile: http://www.excelforum.com/member.php...fo&userid=7741
> View this thread: http://www.excelforum.com/showthread...hreadid=378165
>
>