Function doesn't work correctly with protection on

[BigDave]

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

 

[tkstock]

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

 

[BigDave]

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

 

[tkstock]

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

 

[tkstock]

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

 

[BigDave]

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

 

[BigDave]

I am using the following code, and it works fine for me with the sheet
protected:
Code:
--------------------
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?

 

[tkstock]

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

 

[Tushar Mehta]

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

 

[BigDave]

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]

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