Need help with some VBA - Probably very simple

[KopThat99]

Hi all, im new to this forum and really need some help.

I have created an excel workbook that has 3 sheets, 2 sheets on data/information that users will see and 1 sheet as the main menu.
I have created buttons on all sheets allowing users to navigate to the different sheets/menu and when this is pressed all other sheets are hidden (so only 1 sheet is visable)

On the main menu, i would like to creat an 'exit' button that will close the workbook without saving (not whole application) and would also like to disable the close X so user have to close using my button.

Is this possible??

I now it may be easier if i used user forms etc in my project, but im pretty basic at VBA and have no idea how that works.

Anyway, thank you in advance for reading this and hope you can help.

[austenr]

On the main menu, i would like to creat an 'exit' button that will close the workbook without saving (not whole application) and would also like to disable the close X so user have to close using my button.

Is this possible??

A little confused, are you talking about a close button on a sheet or on the excel menu?

Might help us to see the workbook.

[KopThat99]

I would like to create a button on a worksheet - and when clicked have it close my workbook.

is that ok?

[austenr]

From the Mr Excel board:


There's an icon on the VB toolbar that looks like a hammer crossed with a crescent wrench. Click that to open the "toolbox".
OK. In the toolbox you can "hover" over an element to get the tooltip on what it is. There's one that looks like a timny button. Hover your mouse pointer over it and it should say "Command Button". OK. Click that and then draw the button where you want it on the sheet: you can make it any size you like.
OK. Now rightclick over the newly drawn button. Pick Properties from the Pop-up menu. There are two properties I would change. The Name property I change to cmdSaveNClose (but you don't have to do this; still it's a good habit to get into). The second property I change is the Caption property. I change it to "Save and Close" (without the quotation marks). You will see, if you look around in the properties that you can change the font of the caption, the color of the caption, and a few other things if you like. OK.
Close the Properties Window and rightclick over the newly captioned button and this time pick ViewCode from the pop-up menu. The Visual Basic Editor opens and there is already the "stub" of an event handler. It says

Private Sub cmdSaveNClose_Click()
End Sub

Alright. All we're going to do is to write two lines of code into this 'stub'. (If you really get into this stuff, you'll want to write some extra lines of code to handle any errors that may ever occur, but this is a simple event handler and the chances are you can do without it, for your own use). Here's the two lines we add Between the two lines that are already there:

ThisWorkbook.Save
ThisWorkbook.Close

So the whole thing now looks like this:

Private Sub cmdSaveNClose_Click()
ThisWorkbook.Save
ThisWorkbook.Close
End Sub

(It's a good idea to indent your code like this to make it easier to understand.)
OK. Still in the Visual Basic Editor choose File/Close and Return to Microsoft Excel.
You'll want to close the toolbox (close button in upper right of toolbox). On your Visual Basic toolbar you'll see that there's an icon that looks like a ruler and a triangle, maybe a T-square. It's still depressed (meaning we're still in Design mode. OK. Click it once and we're back to our sheet in normal mode and the new button is on the sheet. Now, if you want, oyu can hide the Visual Basic toolbar again by View/Toolbars/ and unselecting it. Try the button. Write something new on the sheet and then hit the button. It should save and close the workbook. Then open it up and see if the last thing you wrote was saved.

[KopThat99]

Thanks,
I have used that code and the button works (i have not included the 'Save' Line as i want people to have the choice)

Is there now anyway to prevent the workbook being closed using 'X' in the top right corner of Excel?? So the only way a user can close the workbook is by pressing the exit button that i have now set up.

Thanks again

[austenr]

Put th is in the before workbook close event:

[vba]If CloseMode = 0 Then Cancel = True
MsgBox "Please use the button to close this file"
[/vba]

or what ever you want to prompt in the message box

[KopThat99]

Put th is in the before workbook close event:

[vba]If CloseMode = 0 Then Cancel = True
MsgBox "Please use the button to close this file"
[/vba]

or what ever you want to prompt in the message box

Hi,

I have put this code in, it works in so far as it stops people closing my workbook using the 'X' in the right hand corner, however it also stops people from closing the workbook by clicking on the button i created.

Is there anyway of allowing the button to work?