Solved: Command Buttons to SAVE, SAVE and EXIT and to EXIT without SAVING

[DonnaDiablo]

Hello...

I have a Workbook with 2 worksheets on it. The User HAS to enable Macros or they get a Worksheet telling them to reopen and Enable Macros and the Main worksheet is hidden. The code for that is here:



When they ENABLE Macros they get in to my main Worksheet which has code for Command Buttons and Special Functions, as follows:

Private Sub CommandButton1_Click() ' Save ONLY
ThisWorkbook.Save
End Sub

Private Sub CommandButton2_Click() 'Exit Without Saving
ThisWorkbook.Close
End Sub
Private Sub CommandButton3_Click() 'Save then Exit
ThisWorkbook.Save
ThisWorkbook.Close
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 4 Then
Target.Offset(0, 1) = Environ("username")
End If
End Sub

The troubles is, i can't get the Command Buttons to work at all.

SAVE does nothing. SAVE and EXIT quite happily exits the workbook, but when i go in it again it hasn't saved. EXIT without SAVING works unless i make any changes to the spreadsheet, then it seems to flick into the workbook 'CustomSave' subroutine.

How can i get these Command Buttons to work... it's my first go at them and i'm sure i need to declare a global or public variable - but where?

Any help anyone can give will be gratefully received.

Donna :o)

[GTO]

Greetings Donna,

Not tested, but in a throwaway copy of your workbook try:

In the worksheet module:
[vba]
Option Explicit

Private Sub CommandButton1_Click() ' Save ONLY
Call ThisWorkbook.SaveMe(False, False)
End Sub

Private Sub CommandButton2_Click() 'Exit Without Saving
bolBail = True
ThisWorkbook.Close False
End Sub
Private Sub CommandButton3_Click() 'Save then Exit

Call ThisWorkbook.SaveMe(False, False)
bolBail = True
ThisWorkbook.Close False
End Sub
[/vba]

In ThisWorkbook Module, add:
[vba]
Public Sub SaveMe(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Call Workbook_BeforeSave(SaveAsUI, Cancel)
End Sub
[/vba]

At the top of a Standard Module, add:
[vba]
Public bolBail As Boolean
[/vba]

Again, not tested (its way late here), but in short, I've experienced that the created 'AfterSave' has this glitch, in that if BeforeSave is called due to a ThisWorkbook.Save, the .Save in the the BeforeSave doesn't get acted upon.

Hope that helps,

Mark

[GTO]

ACK!

At the top of BeforeClose, add:
[vba]
If bolBail Then Exit Sub
[/vba]

Mark

[DonnaDiablo]

Thanx Mark! That works wonderfully when accessing the spreadsheet directly

Now to sort out why it doesn't work when accessing via a link in Sharepoint... I may well post later for assistance.

Cheers!

Donna :o)

[Bob Phillips]

Thanx Mark! That works wonderfully when accessing the spreadsheet directly

Now to sort out why it doesn't work when accessing via a link in Sharepoint... I may well post later for assistance.

Does VBA work in Sharepoint?

[DonnaDiablo]

I'm not sure if VBA works in Sharepoint... I'm only really new to this VB Scripting stuff. I working on the basis that if I can get everything working by accessing the file directly, then i know my scripting is coming along nicely.

Will keep you posted.

Donna :o)