PDA

View Full Version : Solved: Command Buttons to SAVE, SAVE and EXIT and to EXIT without SAVING



DonnaDiablo
10-21-2009, 05:55 AM
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
10-21-2009, 06:39 AM
Greetings Donna,

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

In the worksheet module:

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


In ThisWorkbook Module, add:

Public Sub SaveMe(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Call Workbook_BeforeSave(SaveAsUI, Cancel)
End Sub


At the top of a Standard Module, add:

Public bolBail As Boolean


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
10-21-2009, 06:41 AM
ACK!

At the top of BeforeClose, add:

If bolBail Then Exit Sub


Mark

DonnaDiablo
10-22-2009, 04:41 AM
Thanx Mark! :kiss 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
10-22-2009, 04:59 AM
Thanx Mark! :kiss 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
10-23-2009, 06:12 AM
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)