I really do not understand why that change is necessary. Actually, I still
don't understand why the Abs function call (coupled with changing the minus
sign to a plus sign) that I proposed in my second posting should even have
been necessary either... for two reasons. First, VB knows about True so the
Visible property's True value should have been enough in the first place for
the calculation. True to VB is -1 (and False is 0), so subtracting each
sheet's Visible property (as I first proposed) should have worked originally
(and it *did* work in my testing before I posted the code originally).
Second, given that there was some reason to have to employ the Abs function
call (coupled with the sign change), I can see no reason what it is
necessary to deliberately test the Visible property's True value against
True just to have VB evaluate it as True for the purposes of a mathematical
calculation. I mean, after all, True is True, whether you use the True value
by itself or you use the True value that results from testing if True equals
True. I tell you, this really has me scratching my head... I honestly do not
understand why any of this should be necessary.

--
Rick (MVP - Excel)


"Don Guillett" wrote in message
...
I tested using the file and Rick's code seems to work as desired with this
small change.
Count = Count + Abs(Sheets(X).Visible)
to
Count = Count + Abs(Sheets(X).Visible = True)
=====================
Sub try1()
'******Does work now************
Dim X As Long
Dim Count As Long

Count = 0

For X = 1 To Sheets.Count
Count = Count + Abs(Sheets(X).Visible = True)
Next
MsgBox Count
If Not Sheets("LogGraph3").Visible Or Count 1 Then
MsgBox "more than one visible besides loggraph3"
' Do Closing Code
Else
' Do Early End Code
MsgBox "Only loggraph3 visible"

End If
'******Doesn't work************
End Sub
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Rick Rothstein" wrote in message
...
Better would be if you posted it to a website (there are free ones out
there that specialize in providing this service) so more than just me
would be able to work on it; however, failing that, feel free to send it
to me (remove the NO.SPAM stuff from my address).

--
Rick (MVP - Excel)


"Sandy" wrote in message
...
Hi Rick

I can send you a stripped down version of my workbook which displays the
aforementioned behaviour, if you like - size is only 147KB.

Sandy


"Rick Rothstein" wrote in message
...
Can you post your code for the BeforeClose so we can see what is going
on?

--
Rick (MVP - Excel)


"Sandy" wrote in message
...
Hi Rick,

Something strange is happening here.

I copied your code EXACTLY, and placed it at the top of the
Workbook_BeforeClose. The final numbers for x and Count are now 28 and
37 respectively.

1st - how can the count of visible sheets be greater than the actual
number of sheets (27)?

2nd - while watching the x and Count add up in the Locals window I
notice that for the first 8 sheets Count remains at 0 - from x=9 to 22
Count increases 2 at a time giving a total Count at this stage of 28.
When x changes to 23 the Count then increases to 29 (increments by 1)
from this point to x=27 the Count rises to 37(incrementing by 2
again). When x=28 the code moves on from the 'For Next' i.e. x and
Count complete.

3rd - only eight of my sheets are visible to the user - the rest are
hidden unless a particular sub (Sub Name()) is run which only leaves
the, normally hidden, "LogGraph3" sheet visible prior to closing.
Thus if "LogGraph3" sheet is the only visible sheet at this stage in
the close down there is no need to run Workbook_BeforeClose, because
pretty much all it does is leave "LogGraph3 sheet visible with a
message to enable macros at start up. Which is back to why I was
looking for a test for open or visible sheets prior to
Workbook_BeforeClose running.

I hope the above makes sense.

Sandy

"Rick Rothstein" wrote in
message ...
I don't see how that is possible as True in VBA evaluates to -1, so
the minus a minus number should result in adding, not subtracting.
Just so you know, I tested the code before I posted it and Count
always came out positive. In any event, whatever is causing the
negative values on your system should be able to be handled by this
modification to what I posted earlier...

'******Sub Starts******
Sub Workbook_BeforeClose(Cancel As Boolean)
Dim X As Long
Dim Count As Long
For X = 1 To Sheets.Count
Count = Count + Abs(Sheets(X).Visible)
Next
If Not Sheets("LogGraph3").Visible Or Count 1 Then
' Do Closing Code
Else
' Do Early End Code
End If
End Sub
'******Sub Ends*******

--
Rick (MVP - Excel)


"Sandy" wrote in message
...
Hi Rick

When I run your code I get X= 28 which is fine but the Count = -35.
??

Sandy

"Rick Rothstein" wrote in
message ...
Give this structure a try...

'******Sub Starts******
Sub Workbook_BeforeClose(Cancel As Boolean)
Dim X As Long
Dim Count As Long
For X = 1 To Sheets.Count
Count = Count - Sheets(X).Visible
Next
If Not Sheets("LogGraph3").Visible Or Count 1 Then
' Do Closing Code
Else
' Do Early End Code
End If
End Sub
'******Sub Ends*******

--
Rick (MVP - Excel)


"Sandy" wrote in message
...
Hi
I have a situation whereby if sheet "LogGraph3" is the only sheet
visible then I do not require the Workbook_BeforeClose to run, how
do I incorporate that into the following.

******Sub Starts******
Sub Workbook_BeforeClose(Cancel As Boolean)

If Sheets("LogGraph3") 'is the only visible sheet' Then
GoTo EarlyEnd

Else
Do closing code
End If

EarlyEnd:
Some Code

End Sub
******Sub Ends*******

Thanks
Sandy