inputBox if cell " ?
 

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

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

--
BigDav
-----------------------------------------------------------------------
BigDave's Profile: http://www.excelforum.com/member.php...nfo&userid=774
View this thread: http://www.excelforum.com/showthread.php?threadid=37816

inputBox if cell " ?
 

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

inputBox if cell " ?
 

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.


--
MrShorty
------------------------------------------------------------------------
MrShorty's Profile: http://www.excelforum.com/member.php...o&userid=22181
View this thread: http://www.excelforum.com/showthread...hreadid=378165