Help please on conditional message box display

Hi there,

I’m desperately looking for some help with some VB coding for a worksheet. I’m not bad when it comes to formulas and data validation, but I’ve never used VB code before. I’ve tried looking for samples through google searches and even these forums, but I can’t seem to get it working exactly how I need it.

To explain what I need, firstly I’ve attached a jpeg file which shows a basic worksheet layout as an example.

All cells on the worksheet, with the exception of Columns B, D, G & H, will be locked and the user will not be able to enter data, or even select the cells.

Columns B & D only allow the user to enter a “*” on condition that G & H are equal. This is done through Data Validation.

Columns G & H only allow the user to enter a whole number from 0 to 20 through data validation.

Column I has formulas only in some selected cells. Depending on the data input in B, D, G or H in the corresponding row, the return value for this formula will always be 0 or 1. The formulas may differ slightly, but the return value will always be 0 or 1.

The three cells that have data in Column K are not related to their corresponding rows, and these cells will be located elsewhere in the sheet. I have just put them in Column K for easy reference.

So this is what I want to happen:

When the user enters data in either B3, D3, G3 or H3, then if I3 changes to 1, I want a message box to appear as follows:

Message Title: “MESSAGE FROM TERRY”
Main Message Body: “I can’t believe that Carol has beaten “ K3 “ at last!!”. (Whereas it will actually read “I can’t believe that Carol has beaten Twinkle Toes at last!!”)
Message Buttons: I just want one “OK” button, when pressed will clear the box and allow the user to carry on.
Icon: I would like the Information “i” graphic shown. However, is it possible to substitute this icon with a graphic of my own?
Miscellaneous:
Can I change the background colour and font style/colour of the Message Title bar?
Can I do the same for the main message body?
Is it possible to have the message box appear in a specified position on the worksheet, rather than in the middle of the screen?

The above message box would only be relevant for a change to I3.

So if I5 changes to 1, then I would have a slightly different message box – same title bar, but with a message such as:
“What a shame! We were all hoping that Little Sarah would beat “ K2 “ at this stage!!” (whereas it will actually read “What a shame! We were all hoping that Little Sarah would beat the Big Man at this stage!!”

And so on, with a different message for each time.

I hope that I’ve explained myself here, and I would be extremely grateful if someone can help me with this.

Oh, by the way, I am using Excel 2007 but saving the file as Excel 97-2003. This is because some of the users that I send it out to will only have the older version of excel.

Many thanks in advance.

Cheers
Terry

This is how to call a message.

If Worksheets("sheet").Cells(3, 9)= "1" then
msg = "I can’t believe that Carol has beaten " & Worksheets("sheet").Cells(3, 11) & " at last!!"
Style = vbOKOnly
Title = "MESSAGE FROM TERRY"
response = MsgBox(msg, Style, Title)
End if

Just change the red higlighted to your sheet name.
and the just copy and paste and change the res for the other cells.

For the other 'stuf' someone else will need to help you.

Sadly, you can't change the title colour or background colour of the msgbox object. If you want to be able to do this, you will have to create a UserForm and use that to act as your message (although you can only change the bancground colour - not title bar colour), or you will have to go down the road of custom activex controls, which is probably more complicated than what you really need i.e. is it that important?

Hi Vivsang, many thanks for that. I've tried it in my worksheet, and although it works initially there is a problem. As soon as I select any other cell, the message box keeps appearing.

I need the message box to appear only when the specific cell (i.e. I3) changes to a 1. What I think is happening is that after it has changed, it is always 1, and so the macro is being called all the time with every action elsewhere in the worksheet.

Any ideas?

Sadly, you can't change the title colour or background colour of the msgbox object. If you want to be able to do this, you will have to create a UserForm and use that to act as your message (although you can only change the bancground colour - not title bar colour), or you will have to go down the road of custom activex controls, which is probably more complicated than what you really need i.e. is it that important?
Hi there,

Thanks for the info. It's not really important to change the colours and fonts, so I'll forget about that part!

Still need to get the message box working properly, though. :)

Cheers
Terry

what is the last cell(s) that the user will change before colom i changes