Function commenttext2(incell As String) As String
If incell " " Then commenttext2 = InputBox("Please enter you
datasheet comment for" & " " & " " & ActiveSheet.Range("c41").Value)
End Function


I have this function set so when the user enters a number into cel
B41, they are prompted with an inputBox. Works great withou
protection. It works with protection, but only when I go to Save o
Exit the spreadsheet.

Ideas

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

If the sheet is protected with the default protection, they won't b
able to change the comments. When you protect the sheet, allow them t
"Edit Objects"

HT

--
tkstoc

-----------------------------------------------------------------------
tkstock's Profile: http://www.excelforum.com/member.php...fo&userid=1444
View this thread: http://www.excelforum.com/showthread.php?threadid=37910

When it works the way I want it to, they enter a number (1-17) and whe
they press Enter, the input box appears asking for a comment. With th
protection (w/ password) and Edit Objects checked, it doesn't work unti
I press save and then it activates the input boxes in order.

The workbook is also protected - if that helps any.

thank

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

What event are you using to trigger the inputboxes? I'm trying t
replicate the problem

--
tkstoc

-----------------------------------------------------------------------
tkstock's Profile: http://www.excelforum.com/member.php...fo&userid=1444
View this thread: http://www.excelforum.com/showthread.php?threadid=37910

I am using the following code, and it works fine for me with the shee
protected:


Code
-------------------
Private Sub Worksheet_Change(ByVal Target As Range)
Target.NoteText InputBox("Enter comment:")
End Su
-------------------


NoteText is old, but it's easy.

HT

--
tkstoc

-----------------------------------------------------------------------
tkstock's Profile: http://www.excelforum.com/member.php...fo&userid=1444
View this thread: http://www.excelforum.com/showthread.php?threadid=37910

tkstock Wrote:
What event are you using to trigger the inputboxes? I'm trying t
replicate the problem.

*=commenttext2(b41)* is the formula I have in B140. So...B14
captures the the comment typed into the input box

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

tkstock Wrote:
I am using the following code, and it works fine for me with the sheet
protected:


Code:
--------------------
Private Sub Worksheet_Change(ByVal Target As Range)
Target.NoteText InputBox("Enter comment:")
End Sub
--------------------


NoteText is old, but it's easy.

HTH

Based on my response above, can I use that to record what is typed into
the inputBox?


--
BigDave
------------------------------------------------------------------------
BigDave's Profile: http://www.excelforum.com/member.php...fo&userid=7741
View this thread: http://www.excelforum.com/showthread...hreadid=379105

My apologies - I thought you were really attempting to use cel
comments.

The function I have will work for you though. The code would b
something like this:


Code
-------------------
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$B$41" then
Range("B140") = InputBox("Enter comment:")
End If
End Su
-------------------


When I set it up the way you had it, it wouldn't prompt me until
entered a value in the cell for a *second* time - I have no clue o
that one! :)

HT

--
tkstoc

-----------------------------------------------------------------------
tkstock's Profile: http://www.excelforum.com/member.php...fo&userid=1444
View this thread: http://www.excelforum.com/showthread.php?threadid=37910

I don't know if this will help but pass C41 as an argument.

As it stands now, your function will always ask for a comment about
cell C41. In general, it is a very, very bad idea to write a user
defined function that works with cells/valures other than those passed
as arguments.

Function CommentText2(aStr as string, RefString as string),
where RefString will contain the value of C41.

This function would now be used as =CommentText2(B41,C41)

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article ,
says...


Function commenttext2(incell As String) As String
If incell " " Then commenttext2 = InputBox("Please enter your
datasheet comment for" & " " & " " & ActiveSheet.Range("c41").Value)
End Function


I have this function set so when the user enters a number into cell
B41, they are prompted with an inputBox. Works great without
protection. It works with protection, but only when I go to Save or
Exit the spreadsheet.

Ideas?


--
BigDave
------------------------------------------------------------------------
BigDave's Profile: http://www.excelforum.com/member.php...fo&userid=7741
View this thread: http://www.excelforum.com/showthread...hreadid=379105

Function commentText2(astr As String, refstring As String)
refstring = c41
astr = b41
If astr " " Then commentText2 = InputBox("Please enter your datasheet
comment for" & " " & " " & refstring)
End Function


Is this the correct way to go about it? (How green am I?)


--
BigDave
------------------------------------------------------------------------
BigDave's Profile: http://www.excelforum.com/member.php...fo&userid=7741
View this thread: http://www.excelforum.com/showthread...hreadid=379105

Okay...code works now. The calculation mode was set to manual b
another proceedure. But, I'm not in the clear yet.

I'm still curious about alternatives to comment prompts were discussin
here, but I'll sort out my new problem in another thread.

Thanks for all of the help

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