Public Variable problem

  • Thread starter Thread starter ordnance1
  • Start date Start date
O

ordnance1

I have the line below in a module:

Public NoSave As Boolean

In my Auto_Open routine I have:

Sub Auto_Open()
'
Events.Enable_Events

If ActiveWorkbook.ReadOnly = True Then

NoSave = True
Protection.ProtectAllSheets
Else
NoSave = False
Protection.UnProtectAllSheets
End If

Module7.StartPoint
Module6.StartTimer

End Sub

Then I in ThisWorkbook ihave the following"

Private Sub Workbook_BeforeClose(Cancel As Boolean)

If NoSave = True Then
ThisWorkbook.Close False
End If

If NoSave = False Then
End If

End Sub

My problem is that when you click on either the Red X to close Excel or the
smaller X to close the workbook you are still prompted to save the workbook.
So it is not seeing NoSave as true, even when it was set to True in te
Auto_Open routine.
 
All depends on your code line;

If ActiveWorkbook.ReadOnly = True Then

BTW, Public Variables MUST reside in a Public Module.
 
Try

Private Sub Workbook_BeforeClose(Cancel As Boolean)

If NoSave = True Then
Cancel = True
Application.DisplayAlerts = False
ThisWorkbook.Close False
End If

End Sub
 
I have other code that uses the If ActiveWorkbook.ReadOnly = True Then line
and it runs fine, but when I click on the Large X (very upper right corner)
it does not see the active workbook as being ReadOnly. As far as I know
NoSave As Boolean is in a public module. When I commented out the line I got
a Variable not defined error.

I also tried simplifying the code to:

If ActiveWorkbook.ReadOnly = True Then
ThisWorkbook.Close False"
End If
 
I'd try:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
If NoSave = True Then
me.saved = true ' a white lie to excel
end if
End Sub

Since you lied to excel, it won't see any changes that need to be saved. So it
won't prompt the user. And the close will just continue just like normal.
 
Sorry I did not get back sooner, got called away for work.

Things still are not working for me and I think I have tracked down my
problem, just do not know how to fix it. The code below is a timer code to
close my workbook after 15 minutes and it prevents my Workbook_BeforeClose
from working.
When I click on the small x or select close from the menu it works, but when
I click on the large X it sees ActiveWorkbook.ReadOnly as false.


Private Sub Workbook_BeforeClose(Cancel As Boolean)

If ActiveWorkbook.ReadOnly = False Then
ThisWorkbook.Close True
End If

If ActiveWorkbook.ReadOnly = True Then
ThisWorkbook.Close False
End If

End Sub


'''''''''''''''''''''''''''''''''''''''''''''''''''

Code was found @
http://www.teachexcel.com/free-excel-macros/m-13,close-workbook-time-limit.html

Option Explicit
Sub StartTimer()
Dim Start, Finish, TotalTime, TotalTimeInMinutes, TimeInMinutes
Application.DisplayAlerts = True
Cancel = False

TimeInMinutes = 15 'Timer is set for 15 minutes; change as needed.
If TimeInMinutes > 5 Then
TotalTimeInMinutes = (TimeInMinutes * 60) - (5 * 60)
Start = Timer
Do While Timer < Start + TotalTimeInMinutes
DoEvents

If Cancel = True Then
GoTo Finished
End If

Loop
Finish = Timer
TotalTime = Finish - Start
Application.DisplayAlerts = False

End If

Start = Timer
Do While Timer < Start + (5 * 60)
DoEvents

If Cancel = True Then
GoTo Finished
End If

Loop
Finish = Timer
TotalTime = Finish - Start
Application.DisplayAlerts = False

Finished:

If ActiveWorkbook.ReadOnly = True Then
ThisWorkbook.Close False
Else
ThisWorkbook.Close True
End If

End Sub

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
 
Did you try all the suggestions?

Sorry I did not get back sooner, got called away for work.

Things still are not working for me and I think I have tracked down my
problem, just do not know how to fix it. The code below is a timer code to
close my workbook after 15 minutes and it prevents my Workbook_BeforeClose
from working.
When I click on the small x or select close from the menu it works, but when
I click on the large X it sees ActiveWorkbook.ReadOnly as false.

Private Sub Workbook_BeforeClose(Cancel As Boolean)

If ActiveWorkbook.ReadOnly = False Then
ThisWorkbook.Close True
End If

If ActiveWorkbook.ReadOnly = True Then
ThisWorkbook.Close False
End If

End Sub

'''''''''''''''''''''''''''''''''''''''''''''''''''

Code was found @
http://www.teachexcel.com/free-excel-macros/m-13,close-workbook-time-limit.html

Option Explicit
Sub StartTimer()
Dim Start, Finish, TotalTime, TotalTimeInMinutes, TimeInMinutes
Application.DisplayAlerts = True
Cancel = False

TimeInMinutes = 15 'Timer is set for 15 minutes; change as needed.
If TimeInMinutes > 5 Then
TotalTimeInMinutes = (TimeInMinutes * 60) - (5 * 60)
Start = Timer
Do While Timer < Start + TotalTimeInMinutes
DoEvents

If Cancel = True Then
GoTo Finished
End If

Loop
Finish = Timer
TotalTime = Finish - Start
Application.DisplayAlerts = False

End If

Start = Timer
Do While Timer < Start + (5 * 60)
DoEvents

If Cancel = True Then
GoTo Finished
End If

Loop
Finish = Timer
TotalTime = Finish - Start
Application.DisplayAlerts = False

Finished:

If ActiveWorkbook.ReadOnly = True Then
ThisWorkbook.Close False
Else
ThisWorkbook.Close True
End If

End Sub

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Click to expand...
 
I'm sorry, I did try all suggestions offered. The only one that worked was
the one where I lied to Excel and said If ActiveWorkbook.ReadOnly = False
then perform the function that I wanted done when the document opened as
read only. The problem with that approach is that when the document is
opened, not as read only, it would close without saving. I have other
modules where I use the If ActiveWorkbook.ReadOnly = and the all work
correctly. Every thing comes back to the timer routine. If I do not run it
(it is started in the Auto_Open) everything works as expected. I even tried
referring to the sub (shown below). That sub resided in the same module as
the timer routine.
 
What happened when you tested my suggestion?
I'm sorry, I did try all suggestions offered. The only one that worked was
the one where I lied to Excel and said If ActiveWorkbook.ReadOnly = False
then perform the function that I wanted done when the document opened as
read only. The problem with that approach is that when the document is
opened, not as read only, it would close without saving. I have other
modules where I use the If ActiveWorkbook.ReadOnly = and the all work
correctly. Every thing comes back to the timer routine. If I do not run it
(it is started in the Auto_Open) everything works as expected. I even tried
referring to the sub (shown below). That sub resided in the same module as
the timer routine.
Click to expand...
 
Here is my Workbook_BeforeClose incorporating you suggestion, and it run
the NoSave False IF statement regardless if the workbook is opened read only
or not


If NoSave = True Then 'Workbook opened as read only
Me.Saved = True ' a white lie to excel
msgbox "NoSave = True"
End If

If NoSave = False Then 'Workbook not opened as read only
msgbox "NoSave = False"
End If
 
Again I think it is important to not that if I am not running the Timer
Routine everything works as it should.

ordnance1 said:
Here is my Workbook_BeforeClose incorporating you suggestion, and it run
the NoSave False IF statement regardless if the workbook is opened read
only or not


If NoSave = True Then 'Workbook opened as read only
Me.Saved = True ' a white lie to excel
msgbox "NoSave = True"
End If

If NoSave = False Then 'Workbook not opened as read only
msgbox "NoSave = False"
End If
Click to expand...
 
That means something is changing the value of that NoSave variable.

Or you don't have that variable declared as a Public variable in a General
module.

If all you're checking is the .readonly property, you could use:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
if me.readonly = true then
me.saved = true
end if
End Sub

I have no idea how that variable is used/set/changed by other routines, though.
Here is my Workbook_BeforeClose incorporating you suggestion, and it run
the NoSave False IF statement regardless if the workbook is opened read only
or not

If NoSave = True Then 'Workbook opened as read only
Me.Saved = True ' a white lie to excel
msgbox "NoSave = True"
End If

If NoSave = False Then 'Workbook not opened as read only
msgbox "NoSave = False"
End If
Click to expand...
 
Well I appreciate your help, but it still does not wok. Something that might
be of interest is, If I click on the large X the code does not work (even
though the workbook is read only) but if I click on cancel when asked if I
want to save changes, and then click on the large X again, the code works.
 
Start a new workbook.
Add that single procedure to the ThisWorkbook module.
Save the workbook (book1.xls is ok with me).

Close that workbook.
Reopen it in readonly mode.
Make some changes.
Close the workbook.

Do you get prompted to save the changes?



Reopen the workbook in normal (not readonly) mode.
Make some changes
Close the workbook.

Do you get prompted to save the changes?

Try the same tests when you close excel and when you close the single workbook.


Well I appreciate your help, but it still does not wok. Something that might
be of interest is, If I click on the large X the code does not work (even
though the workbook is read only) but if I click on cancel when asked if I
want to save changes, and then click on the large X again, the code works.
Click to expand...
 
It worked just as expected. And I can make my current workbook act correctly
by not running my Timer code (see below) something in that code prevents
the before close routine from seeing the workbook as read only.


Option Explicit
Sub StartTimer()
Dim Start, Finish, TotalTime, TotalTimeInMinutes, TimeInMinutes
Application.DisplayAlerts = True
TimerStop = False 'was cancel

TimeInMinutes = 15 'Timer is set for 15 minutes; change as needed.
If TimeInMinutes > 5 Then
TotalTimeInMinutes = (TimeInMinutes * 60) - (5 * 60)
Start = Timer
Do While Timer < Start + TotalTimeInMinutes
DoEvents

If TimerStop = True Then 'was cancel
'Exit Do
GoTo finished
End If

Loop
Finish = Timer
TotalTime = Finish - Start
Application.DisplayAlerts = False

End If

Start = Timer
Do While Timer < Start + (5 * 60)
DoEvents

If TimerStop = True Then 'was cancel
'Exit Do
GoTo finished
End If

Loop
Finish = Timer
TotalTime = Finish - Start
Application.DisplayAlerts = False


If ActiveWorkbook.ReadOnly = True Then
ThisWorkbook.Close False
Else
ThisWorkbook.Close True
End If

End Sub
 
I don't understand why you have these lines of code:
If ActiveWorkbook.ReadOnly = True Then
ThisWorkbook.Close False
Else
ThisWorkbook.Close True
End If
Click to expand...

In this sub.

===========
I think I would drop your current code and visit Chip Pearson's site:
http://www.cpearson.com/excel/TimedClose.htm

But his code wants to close and save the workbook. So you'll have to change
this procedure from:

Public Sub SaveAndClose()
ThisWorkbook.Close savechanges:=True
End Sub

to

Public Sub SaveAndClose()
ThisWorkbook.Close 'savechanges:=True
End Sub

And you'll want to modify his _Beforeclose event to include your rules:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error Resume Next
Application.OnTime RunWhen, "SaveAndClose", , False
On Error GoTo 0
End Sub

becomes

Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error Resume Next
Application.OnTime RunWhen, "SaveAndClose", , False
On Error GoTo 0

If me.readonly = true Then
me.saved = true ' a white lie to excel
else
'stop any other events from firing
application.enableevents = false
me.save
application.enableevents = true
end if

End Sub

I don't know how/where you used that NoSave variable.
It worked just as expected. And I can make my current workbook act correctly
by not running my Timer code (see below) something in that code prevents
the before close routine from seeing the workbook as read only.

Option Explicit
Sub StartTimer()
Dim Start, Finish, TotalTime, TotalTimeInMinutes, TimeInMinutes
Application.DisplayAlerts = True
TimerStop = False 'was cancel

TimeInMinutes = 15 'Timer is set for 15 minutes; change as needed.
If TimeInMinutes > 5 Then
TotalTimeInMinutes = (TimeInMinutes * 60) - (5 * 60)
Start = Timer
Do While Timer < Start + TotalTimeInMinutes
DoEvents

If TimerStop = True Then 'was cancel
'Exit Do
GoTo finished
End If

Loop
Finish = Timer
TotalTime = Finish - Start
Application.DisplayAlerts = False

End If

Start = Timer
Do While Timer < Start + (5 * 60)
DoEvents

If TimerStop = True Then 'was cancel
'Exit Do
GoTo finished
End If

Loop
Finish = Timer
TotalTime = Finish - Start
Application.DisplayAlerts = False

If ActiveWorkbook.ReadOnly = True Then
ThisWorkbook.Close False
Else
ThisWorkbook.Close True
End If

End Sub
Click to expand...
 
They are there so that at the end of the timer period (in this case 15
minutes), if the file was opened as read only the file will close without
saving changes, if the file is not read only then changes will be saved upon
closing. Since this workbook is accessed by many people, the timer is there
to prevent the workbook from being left open on someone's desk top (and
unavailable to others) for an extended period of time. But I will follow
your advise and check out Chip Pearsons site.

I do want to thank you for the time and expertise you have offered.
 
But you're not testing anything before those lines execute.

So you'd be closing the workbook (saved or unsaved) when that portion of the
code ran.

Besides that, the code wouldn't compile for me. There was no label named
Finished (for example).
They are there so that at the end of the timer period (in this case 15
minutes), if the file was opened as read only the file will close without
saving changes, if the file is not read only then changes will be saved upon
closing. Since this workbook is accessed by many people, the timer is there
to prevent the workbook from being left open on someone's desk top (and
unavailable to others) for an extended period of time. But I will follow
your advise and check out Chip Pearsons site.

I do want to thank you for the time and expertise you have offered.
Click to expand...
 
That code ran at the end of the timer. But I checked out the site you
recommended and was able to incorporate Mr. Pearsons code (removed my timer
code) and everything is working flawlessly. Again I want to thank you for
all your help.
 
I don't understand how the old code ever worked, but that's not important now.

Glad you got the new code working the way you want.
That code ran at the end of the timer. But I checked out the site you
recommended and was able to incorporate Mr. Pearsons code (removed my timer
code) and everything is working flawlessly. Again I want to thank you for
all your help.
Click to expand...
 
Back
Top