Macro or?

Posted by RoB on August 13, 2001 4:03 PM

Im looking for a way to do 2 things (totally separate).

First, I was wondering if there is a way (probably a macro) to:
when the close workbook, or close excel button is clicked, ask the user a question, and give them a "yes" or "no" option. If they click yes, I would like the close to proceed, and ask if they would like to save their changes. If they click no, I would like the macro to take them to the first sheet of the workbook.

Also, I was wondering (probably a macro again) to:
Give a shape a conditional color.
ie: If a certain cell is empty, I would like the shape I place to NOT filled, with NO lines (invisible). But if the certain cell has data, then the line to be black (still no fill).

I appreciate any help on these questions.

Thanks again :)
Rob

Posted by Damon Ostrander on August 13, 2001 4:28 PM

Hi Rob,

Yes to both questions. The first can be accomplished via the Applications Workbook_Close event. This event has a Cancel argument, which, if you set it to True will enable you to cancel the close. So, you can add a MsgBox to ask the user whether to close and if not activate worksheet 1. (Worksheets(1).Activate)

To use the Workbook_Close event (not to be confused with the ThisWorkbook object's Close event), see "Application Object Events" in the VBA helps, and follow the directions carefully--it is a bit tricky as you have to create a Class module. I believe there is even an example in the helps of this exact problem (except without activating worksheet 1).

Your second item can be accomplished using the worksheet object's Worksheet_Change event. Use this event's Target argument to test whether the cell you are interested in is the cell that has changed. If it is, look at its value and set the fill color, visible property, etc., of the Shape object accordingly. I could email you a simple example of this if this description is not sufficient.

Happy computing.

Damon

Posted by RoB on August 13, 2001 5:14 PM

Yes...

Any further assistance would really be appreciated. I'm still very new to VB, and programming in general. I'll take a look at the help files, but any examples would be great :)

Thanks!
Rob