Macro Command to Exit

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Zainuddin Zakaria - 02 Jan 2007 03:07 GMT

Hi again,

This is related to the question below.
Dave Peterson has kindly responded to that and it works.
Thanks, Dave.

My next question is how to exit the workbook (file) since there is no MENU
bar again. I would like to have Macro command to do that (Exit) with a
warning message to save first.

Please help ... Thank you.

Zainuddin Z

"Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
news:<45991DBA.107C9AC5@verizonXSPAM.net>...

> One way is to just show the File|Save as dialog.

> Option Explicit

> Sub testme()

> Application.Dialogs(xlDialogSaveAs).Show

> End Sub

"Zainuddin Zakaria" <salza@tm.net.my> wrote in message
news:45991483$1_1@news.tm.net.my...

> Hi all,
>
> I have hidden the MENU BAR using a macro command on purpose.
>
> For the user to 'save/save as' his work, I plan to have a button on that
> worksheet. The user can open the FILE menu, using that button when
> pressed. Can someone suggest a macro command for that button to work the
> way I plan it to be if it is possible.
>
> Thank you so much.
>
> Zainuddin Z

Reply to this Message

Chip Pearson - 02 Jan 2007 03:19 GMT

If by "exit the workbook" you mean close the file, use something like

Workbooks("Book1.xls").Close SaveChanges:=True

No need to prompt the user to be sure to save the file. The code above will
save the file before closing, taking the decision whether to save out of the
hands of the user.

Signature

Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)

> Hi again,
>
[quoted text clipped - 32 lines]
>>
>> Zainuddin Z

Reply to this Message

Zainuddin Zakaria - 02 Jan 2007 04:50 GMT

Thanks Chip.

Got that macro command and insert it in a module. I created a button and
assign macro to it using the macro command you gave me.

But when I clik on the macro button, I got this message "Run time error 9,
subscript out of range"

Do I have to change anything to the macro command u gave me?

Workbooks("Book1.xls").Close SaveChanges:=True

Thanks

Zainuddin Z

> Hi again,
>
[quoted text clipped - 32 lines]
>>
>> Zainuddin Z

Reply to this Message

Bob Phillips - 02 Jan 2007 10:05 GMT

That is probably because you do not have a workbook called Book1.

If the code is in the same workbook as is being saved, use

ThisWorkbook.Close SaveChanges:=True

Signature

---
HTH

Bob

(change the xxxx to gmail if mailing direct)

> Thanks Chip.
>
[quoted text clipped - 49 lines]
>>>
>>> Zainuddin Z

Reply to this Message

Chip Pearson - 02 Jan 2007 12:40 GMT

The code I posted was just an example. It assumed that there would be an
open workbook named "Book1.xls". You can change "Book1.xls" to the actual
name of the workbook, or use

ActiveWorkbook.Close SaveChanges:=True

to close the workbook that is active, or

ThisWorkbook.Close SaveChanges:=True

to close the workbook that contains the code.

Signature

Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)

> Thanks Chip.
>
[quoted text clipped - 49 lines]
>>>
>>> Zainuddin Z

Reply to this Message

Zainuddin Zakaria - 02 Jan 2007 16:05 GMT

Dearest Bob Phillips and Chip Pearson,

Thank you so much. The code both of you suggested work perfectly like I
want.
I really appreciate it.

Thanks again.

Warmest regards,

Zainuddin Z

> Hi again,
>
[quoted text clipped - 32 lines]
>>
>> Zainuddin Z

Reply to this Message