IF function message too big for cell - automate reply?

[Guest]

I have an IF function that if TRUE returns user information text too large
for the nominated cell B34. How can I have a user information message box
appear with this text in it if the IF function returns TRUE?
Or is there another way of generating this user message when the IF function
returns TRUE?
I do not think I can use Data Validation as the nominated cell is not
selected by the user and holds the IF function based on data in other cells.

 

[Guest]

Colin,

Set your if statement true value to "TRUE" (ie. B34 is =if('your condition',
TRUE, 'false value'). Then add the following to the sheet code:

Private Sub Worksheet_Calculate()
If Range("B34").Value Then MsgBox ("Put Your Message Here")
End Sub

To add code to the sheet, right click the sheet tab and click "view code"
Paste the code above into the VBA window that opens.

Mike

 

[Guest]

Hi,

This would work okay if the user only had one cell to select from, but the
IF statement condition in B34 is based on 10 cells, and this message would
appear as soon as one cell is changed.
This is the IF statement.
=IF(COUNTA(Scoring_Operational)<10,"",SUM(Scoring_Operational)*2). The NAME
Scoring_Operational refers to 10 cells.
I require the message to appear only if all ten cells are changed.
Apologies for not supplying enough detail.

 

[Guest]

Colin,

OK, I can work with that too...need more info though.

When you say 'only if all ten cells are changed' do you mean changed from
their initial value upon opening the document? Does the order of change
matter (can I change cells in any order or only in a specific order)? Are
the 10 cells changed by the user, by a function, or by VBA code? What are
the ten cells? Does the range change or is it always the same 10 cells?

Mike

 

[Guest]

Hi,

I've found a way round this by having an information message box shown when
the user opens the worksheet and also when they exit. Thank you for your
help. The code you supplied I have used elsewhere in the same workbook.