VBA Code Conflict(Not Resolved Yet)

[James]

My Code:

Private Sub Workbook_Open()
ThisWorkbook.IsAddin = False
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
This Workbook.IsAddIn = True
End Sub

With the above code, if you choose to disable the macros the file will not open.

Private Sub Workbook_BeforeSave(BYVal SaveAsUI As Boolean, Cancel As Boolean)
If ThisWorkbook.Saved = False Then
MsgBox"changes not saved", vbOKOnly
Cancel = True
End If
End Sub

With the above code, it cancels "saving" the Workbook.

My headache is that i can't combine them and get them both to work in unison.

Regards,
James




_________________


[ This Message was edited by: James on 2002-05-17 18:29 ]

[ This Message was edited by: James on 2002-05-17 20:26 ]

[NateO]

James, just to clarify, are you trying to not allow users to save the file until close, then allow them to save? I got this impression when you set the file at close. With a little more clarity on the concept, we can get this conflict resolved.

Also, in your post you have an inadvertant space in "thisworkbook" in your beforeclose procedure.

_________________
Cheers, NateO

[ This Message was edited by: NateO on 2002-05-17 19:29 ]

[James]

Hi Nate,

Edited my original, hopefully i clarified what i am trying to achive.

Regards,
James

[ This Message was edited by: James on 2002-05-17 18:33 ]

[dsnbld]

Hi James,

Set the workbook to run as AddIn:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
ThisWorkbook.IsAddin = True
End Sub

Save & Close (and save again at prompt)

Re-open workbook and enable macros at prompt. Book window is hidden, but you can still open VBA, delete the above (hit save in VBA after deleting) and enter the following:


Private Sub Workbook_Open()
ThisWorkbook.IsAddin = False
End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If ThisWorkbook.Saved = False Then
Msg = "Saving Changes Is Disabled In Evaluation Version"
Msg = MsgBox(Msg, vbOKOnly + vbExclamation)
Cancel = True
'Interupt here when saving this code for the first time. I type "bug" to interupt.
'Debug (delete bug) and reset- after reset the save process will continue.
'In future you can manually set ThisWorkbook.Saved property to True to save changes.
End If
End Sub

In the future when you manually set the ThisWorkbook.Saved property to True to save your changes, the "Saving Changes Is Disabled" may come up, but click OK and immediately hit save again- and it will save. I've put this through the ringer and it works everytime.

Win98, Office 2K.

[ This Message was edited by: dsnbld on 2002-05-18 05:58 ]

[dsnbld]

James, I sent this clarification to you. I'm posting it here for anyone else who might want it.


Private Sub Workbook_BeforeClose(Cancel As Boolean) 'this is all you do to start!
ThisWorkbook.IsAddin = True
End Sub

Save (not save as) & Close (close will ask if you want to save your changes- yes).

I'm in the habit of closing Excel after making and saving changes to a file and want to re-open it.

Re-open workbook and ENABLE MACROS at prompt. Book window is hidden, but you can still open VBA,
* delete the above procedure
* click VBA Save (this is a must)
* then enter the following code
* after you enter the code type the word "bug" between Cancel = True and End If
* click save (this will trigger the debugger because of the "bug")
* delete bug and click reset (the save process will continue because of the interuption)
* next time you open the file your set


Private Sub Workbook_Open()
ThisWorkbook.IsAddin = False
End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If ThisWorkbook.Saved = False Then
Msg = "Saving Changes Is Disabled In Evaluation Version"
Msg = MsgBox(Msg, vbOKOnly + vbExclamation)
Cancel = True
'Interupt here when saving this code for the first time. I type "bug" to interupt.
'Debug (delete bug) and reset- after reset the save process will continue.
'In future you can manually set ThisWorkbook.Saved property to True to save changes.
End If
End Sub

In the future when you manually set the ThisWorkbook.Saved property to True to save your changes, the "Saving Changes Is Disabled" may come up, but click OK and immediately hit save again- and it will save. I've put this through the ringer and it works everytime.

Win98, Office 2K.