Quick Links ▼

Bookmark page

Printable version

Customer: Sign in
Delegate: Sign in
Trainer: Log in

Forum home » Delegate support and help forum » Microsoft Excel Training and help » Boxes

	98.1 % recommend us
	More live stats...

The UK's most regular
instructor-led training courses.

Training information:

Boxes

Resolved · Low Priority · Version 2010

Reply Thu 7 Jul 2011, 16:03Delegate Bradley said...

Bradley has attended:
Excel Intermediate course

Boxes

Please canyou remind me how to create the message box that should appear just before I close my worksheet?

Thanks

For upcoming training course dates see: Pricing & availability

Reply Fri 8 Jul 2011, 11:21Trainer Stephen said...

RE: Boxes

Hi Bradley

Thanks for your question

Can you clarify your question for me. Why do you want the message box to appear before closing your worksheet? What options do you want it to present

Thanks

Stephen

Reply Fri 8 Jul 2011, 11:23Delegate Bradley said...

RE: Boxes

Hi Stephen,

yesterday at the end of our training session, you showed us how to create a message box in VBS.

I think the message box came up just before we went to close the Excel workbook but I cant remember exactly.

We are able to customise the message box to say anything we wanted it to say.

Thanks.

Bradley.

Reply Mon 11 Jul 2011, 16:19Trainer Simon said...

RE: Boxes

Hi Bradley,

Thank you for your question.

Please follow these instructions:

1. Open the workbook first.

2. Then Alt + F11 on the keyboard.

3. In the Project Explorer window on left of screen, find the VBAProject with your workbook name and expand it if necessary using the + symbol.

4. From the list of Objects double click on workbook.

5. A new window appears on the right hand side.

6. From the first drop down box at the top choose Workbook.

7. From the second combo box to the right, choose BeforeClose.

8. The following should then appear:

Private Sub Workbook_BeforeClose(Cancel As Boolean)

msgbox "Enjoy your evening"

End Sub

9. Between Private Sub and End sub type the following as shown above.

10. Close the VBA window using the red X and everytime you close the workbook the message appears.

I hope this answers your question. Let me know.

Regards

Simon

Reply Mon 11 Jul 2011, 16:19Trainer Simon said...

Will be marked as resolved in 5 days

Notice: This is an automated message. Due to inactivity, this forum post will be marked as 'resolved' if there are no further responses in the next 5 days.

Mon 18 Jul 2011: Automatically marked as resolved.

Excel articles

· So what's new in Excel 2007?
· How To Use The Subtotal Function In Excel
· Microsoft Excel Training - Numerical excellence for all
· Tools From Advanced Excel Courses: Goal Seek and Solver
· How To Create A Custom List In Excel

Please browse our web site to find out more about
microsoft excel training in london and other Microsoft training courses.

Excel tip:

Quickly copy a formula across sheets

Suppose you have a formula in cell Sheet1!B2, say =A1*5%, that you wish to copy to cell B2 on Sheet2, Sheet3 and Sheet4. Instead of using copy and paste, try this: (1) Select Sheet1!B2. (2) Group Sheet1 with the worksheets Sheet2, Sheet3 and Sheet4 by holding down Ctrl and clicking on the tabs of the sheets to group them. (3) Press the F2 key, then immediately press Enter to copy the formula in Sheet1!B2 across the grouped sheets.

Remember to ungroup the sheets afterwards! Right-click on any tab and choose Ungroup Sheets to do that.

View all Excel hints and tips