Excel

[Belinda]

Can anybody please advise, how would I show a popup warning box in an Excel
2000 spreadsheet, if the totals cell reaches a particular figure, this box
would also show a message.



I would appreciate any advice.



Regards,



Belinda

 

[JE McGimpsey]

One way:

Assume the total cell is A21, and your "particular figure" is 100.
Select your input cells to the total cell (e.g., A1:A20). Choose
Data/Validation, select Custom from the dropdown, and enter

=$A$21<=100

Select the Error Message tab and enter your message.

 

[Guest]

Belinda,

The only thing I can think of is a macro. You've got to open the macro editor which you can do by hitting Atl-F11 or use the tools, macro, visual basic editor option.

Once there, you should see a panel on your left that lists the names of the tabs in your file. If not, hit view, project explorer.

You should be able to spot the worksheet that you totals are on. Double click and you should see a blank window on the right which is where you're going to put the following macro.

Paste the following into that window.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim mResult As Integer
msg = "total has reached " & Cells(7, 1)
If Cells(7, 1) > 10 Then
mResult = MsgBox(msg, vbOKOnly)
End If
End Sub

This assumes that the total cell is A7 -- that's referenced in the above macro by Cells(7,1) -- the 7 is for row 7 and the 1 is for column 1 (A). Obviously you can change this whatever you need.

Good luck.

Art

 

[Jay]

Can anybody please advise, how would I show a popup warning box in an

Excel 2000 spreadsheet, if the totals cell reaches a particular
figure, this box would also show a message.

Suppose the "Totals" cell is D20. Find an otherwise unused cell, and put in
it:
=IF(D20>100, "*** ERROR: Sum too large ***", "")

The cell's text can be bright red and bold to make it stand out.

This isn't exactly what you asked for, but it's super easy to do and not
error-prone. I sometimes have several of these checks scattered around a
worksheet.