Closing message

[fubb]

Hi there,
does anybody know how I can add a closing prompt on an xls with a message of my own making, so when I close the xls it brings up the message.

 

[Ivan F Moala]

On 2002-09-18 02:59, fubb wrote:
Hi there,
does anybody know how I can add a closing prompt on an xls with a message of my own making, so when I close the xls it brings up the message.

Hi Fubb
Welcome to the Board
You will need to use an Event code for this;<META content=Excel.Sheet name=ProgId><META content="Microsoft Excel 9" name=Generator><LINK href="./sht_files/filelist.xml" rel=File-List><STYLE id=AAA_Instructions_VBAEnter_10568_Styles></STYLE>  <DIV id=AAA_Instructions_VBAEnter_10568 align=center xublishsource="Excel"><TABLE style="TABLE-LAYOUT: fixed; WIDTH: 456pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=606 border=0 x:str><COLGROUP><COL style="WIDTH: 23pt; mso-width-source: userset; mso-width-alt: 1097" width=30><COL style="WIDTH: 53pt; mso-width-source: userset; mso-width-alt: 2560" width=70><COL style="WIDTH: 338pt; mso-width-source: userset; mso-width-alt: 16493" width=451><COL style="WIDTH: 23pt; mso-width-source: userset; mso-width-alt: 1097" width=30><COL style="WIDTH: 19pt; mso-width-source: userset; mso-width-alt: 914" width=25><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl2310568 style="WIDTH: 23pt; HEIGHT: 12.75pt" width=30 height=17> </TD><TD class=xl2310568 style="WIDTH: 53pt" width=70> </TD><TD class=xl2310568 style="WIDTH: 338pt" width=451> </TD><TD class=xl2310568 style="WIDTH: 23pt" width=30> </TD><TD class=xl2310568 style="WIDTH: 19pt" width=25> </TD></TR><TR style="HEIGHT: 23.25pt" height=31><TD class=xl2310568 style="HEIGHT: 23.25pt" height=31> </TD><TD class=xl2510568>STEP</TD><TD class=xl3210568><SPAN style="mso-spacerun: yes"> </SPAN>'<FONT class=font1010568></FONT><FONT class=font710568>HOW<SPAN style="mso-spacerun: yes"> </SPAN></FONT><FONT class=font1110568>J</FONT></TD><TD class=xl2210568> </TD><TD class=xl2310568> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl2310568 style="HEIGHT: 12.75pt" height=17> </TD><TD class=xl3010568>1)</TD><TD class=xl2710568 style="WIDTH: 338pt" width=451><SPAN style="mso-spacerun: yes"> </SPAN>Press<FONT class=font610568>Alt-F11</FONT><FONT class=font510568>to display the Visual Basic editor (VBE).</FONT></TD><TD class=xl2910568 style="BORDER-LEFT: medium none">þ</TD><TD class=xl2310568> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl2310568 style="HEIGHT: 12.75pt" height=17> </TD><TD class=xl2410568><U style="VISIBILITY: hidden; mso-ignore: visibility"> </U></TD><TD class=xl2410568><U style="VISIBILITY: hidden; mso-ignore: visibility"> </U></TD><TD class=xl2610568> </TD><TD class=xl2310568> </TD></TR><TR style="HEIGHT: 25.5pt" height=34><TD class=xl2310568 style="HEIGHT: 25.5pt" height=34> </TD><TD class=xl3010568>2)</TD><TD class=xl2710568 style="WIDTH: 338pt" width=451><SPAN style="mso-spacerun: yes"> </SPAN>In the<FONT class=font610568>Project Explorer window</FONT><FONT class=font510568>, find the</FONT><FONT class=font610568>ThisWorkbook</FONT><FONT class=font510568> object. If you don't see the</FONT><FONT class=font610568>Project Explorer window,</FONT><FONT class=font510568> press</FONT><FONT class=font610568> Ctrl-R.</FONT></TD><TD class=xl2910568 style="BORDER-LEFT: medium none">þ</TD><TD class=xl2310568> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl2310568 style="HEIGHT: 12.75pt" height=17> </TD><TD class=xl2410568><U style="VISIBILITY: hidden; mso-ignore: visibility"> </U></TD><TD class=xl2410568><U style="VISIBILITY: hidden; mso-ignore: visibility"> </U></TD><TD class=xl2610568> </TD><TD class=xl2310568> </TD></TR><TR style="HEIGHT: 25.5pt" height=34><TD class=xl2310568 style="HEIGHT: 25.5pt" height=34> </TD><TD class=xl3010568>3)</TD><TD class=xl2710568 style="WIDTH: 338pt" width=451><SPAN style="mso-spacerun: yes"> </SPAN><FONT class=font610568>Double-click</FONT><FONT class=font510568> the</FONT><FONT class=font610568>Thisworkbook</FONT><FONT class=font510568> object in the</FONT><FONT class=font610568>Project Explorer window</FONT><FONT class=font510568>.</FONT></TD><TD class=xl2910568 style="BORDER-LEFT: medium none">þ</TD><TD class=xl2310568> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl2310568 style="HEIGHT: 12.75pt" height=17> </TD><TD class=xl2410568><U style="VISIBILITY: hidden; mso-ignore: visibility"> </U></TD><TD class=xl2410568><U style="VISIBILITY: hidden; mso-ignore: visibility"> </U></TD><TD class=xl2610568> </TD><TD class=xl2310568> </TD></TR><TR style="HEIGHT: 25.5pt" height=34><TD class=xl2310568 style="HEIGHT: 25.5pt" height=34> </TD><TD class=xl3010568>4)</TD><TD class=xl2710568 style="WIDTH: 338pt" width=451><SPAN style="mso-spacerun: yes"> </SPAN>Above the code window, you'll find<FONT class=font610568>two drop-down lists</FONT><FONT class=font510568>. In the list on the left, choose</FONT><FONT class=font610568>Workbook</FONT><FONT class=font510568>. In the list on the right, choose</FONT><FONT class=font610568> BeforeClose</FONT><FONT class=font510568>.</FONT></TD><TD class=xl2910568 style="BORDER-LEFT: medium none">þ</TD><TD class=xl2310568> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl2310568 style="HEIGHT: 12.75pt" height=17> </TD><TD class=xl2410568><U style="VISIBILITY: hidden; mso-ignore: visibility"> </U></TD><TD class=xl2410568><U style="VISIBILITY: hidden; mso-ignore: visibility"> </U></TD><TD class=xl2610568> </TD><TD class=xl2310568> </TD></TR><TR style="HEIGHT: 38.25pt" height=51><TD class=xl2310568 style="HEIGHT: 38.25pt" height=51> </TD><TD class=xl3010568>5)</TD><TD class=xl2710568 style="WIDTH: 338pt" width=451><SPAN style="mso-spacerun: yes"> </SPAN>A procedure template for<FONT class=font610568> Workbook_BeforeClose</FONT><FONT class=font510568> now appears in the code window. Between the</FONT><FONT class=font610568> Private Sub</FONT><FONT class=font510568>line and the</FONT><FONT class=font610568> End Sub</FONT><FONT class=font510568> line, type in your procedure.</FONT></TD><TD class=xl2910568 style="BORDER-LEFT: medium none">þ</TD><TD class=xl2310568> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl2310568 style="HEIGHT: 12.75pt" height=17> </TD><TD class=xl2410568><U style="VISIBILITY: hidden; mso-ignore: visibility"> </U></TD><TD class=xl2410568><U style="VISIBILITY: hidden; mso-ignore: visibility"> </U></TD><TD class=xl2610568> </TD><TD class=xl2310568> </TD></TR><TR style="HEIGHT: 25.5pt" height=34><TD class=xl2310568 style="HEIGHT: 25.5pt" height=34> </TD><TD class=xl3110568>eg.</TD><TD class=xl2710568 style="WIDTH: 338pt" width=451>For example, your Message code<FONT class=font610568>, for</FONT><FONT class=font510568>your</FONT><FONT class=font610568>Workbook_BeforeClose</FONT><FONT class=font510568> procedure would look like this:</FONT></TD><TD class=xl2910568 style="BORDER-LEFT: medium none">þ</TD><TD class=xl2310568> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl2310568 style="HEIGHT: 12.75pt" height=17> </TD><TD class=xl2410568><U style="VISIBILITY: hidden; mso-ignore: visibility"> </U></TD><TD class=xl2410568><U style="VISIBILITY: hidden; mso-ignore: visibility"> </U></TD><TD class=xl2610568> </TD><TD class=xl2310568> </TD></TR><TR style="HEIGHT: 51pt" height=68><TD class=xl2310568 style="HEIGHT: 51pt" height=68> </TD><TD class=xl3110568>Code:</TD><TD class=xl2810568 style="WIDTH: 338pt" width=451>Private Sub<FONT class=font910568> Workbook_BeforeClose</FONT><FONT class=font610568>(</FONT><FONT class=font910568>Cancel</FONT><FONT class=font610568></FONT><FONT class=font810568>As Boolean</FONT><FONT class=font610568>)<SPAN style="mso-spacerun: yes">       </SPAN></FONT><FONT class=font910568>MsgBox</FONT><FONT class=font610568> "Your message goes here!"</FONT><FONT class=font810568>End Sub</FONT><FONT class=font610568></FONT></TD><TD class=xl2910568 style="BORDER-LEFT: medium none">þ</TD><TD class=xl2310568> </TD></TR></TBODY></TABLE></DIV>

_________________
Kind Regards,<font size=+2><font color="red"> I<font color="blue">van<font color="red"> F M</font color="blue">oala</font><font size=1> From the City of Sails

This message was edited by Ivan F Moala on 2002-09-18 03:19

 

[sen_edp]

Hello Ivan

Very nice reply , nice work !

Keep it up

take care

 

[fubb]

Hello,
thanks to Ivan my problem is solved, just to save any body else answering.

Thanks again Ivan.

Simon.