Run code conditional on sheet visibility

[Sandy]

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

 

[Mike H]

Try this

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim runflag As Boolean
runflag = False
For x = 1 To Worksheets.Count
If Worksheets(x).Visible Then
Count = Count + 1
If Worksheets(x).Name = "LogGraph3" Then runflag = True
End If
Next
If Not runflag Then
Exit Sub
Else
If runflag And Count < 2 Then
Exit Sub
End If
End If
'some code
End Sub

Mike

 

[Mike]

Dim ws As Worksheet
Dim wsCount As Long
wsCount = 0
For Each ws In Worksheets

If ws.Visible = xlSheetVisible Then
wsCount = wsCount + 1
End If
Next

If wsCount = 1 Then
If Worksheets("Sheet2").Visible = True Then
MsgBox "One sheet visible and the were looking for"
Else
MsgBox "One sheet visible but not the were looking for"
End If
Else
MsgBox "More then one sheet visable"
End If

 

[Rick Rothstein]

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*******

 

[Sandy]

Hi Mike

Something going on I don't understand here - if I use the code you supplied
I get the Count as 19 however after a bit of adjustment the following
produces the correct result i = 1. I stepped it through immediately after
your code in the Sub. Thing is only the LogGraph3 sheet was showing in the
application when I did the check.

Dim ws As Worksheet
Dim i As Long

i = 0
For Each ws In ActiveWorkbook.Worksheets
If ws.Visible = True Then
i = i + 1
End If
Next

If Sheets("LogGraph3").Visible = True And i = 1 Then

etc etc

Sandy

 

[Sandy]

Hi Mike

Works perfectly!

Thank You

Dim ws As Worksheet
Dim wsCount As Long
wsCount = 0
For Each ws In Worksheets

If ws.Visible = xlSheetVisible Then
wsCount = wsCount + 1
End If
Next

If wsCount = 1 Then
If Worksheets("Sheet2").Visible = True Then
MsgBox "One sheet visible and the were looking for"
Else
MsgBox "One sheet visible but not the were looking for"
End If
Else
MsgBox "More then one sheet visable"
End If

 

[Sandy]

Hi Rick

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

Sandy

 

[Mike]

No Problem

Hi Mike

Works perfectly!

Thank You

 

[Rick Rothstein]

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*******

 

[Sandy]

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

 

[Don Guillett]

I just set up a workbook with Rick's code and it ran properly. It looks thru
all sheets and IF visible counts them. If there is only one and the desired
sheet is that one it will do this. If not it will do that. That can be blank
if desired.
If all else fails, you may send me your workbook, to my address below, along
with instructions and I will look.

 

[Rick Rothstein]

Can you post your code for the BeforeClose so we can see what is going on?

 

[Sandy]

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]

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).

 

[Don Guillett]

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]

I looked at the workbook and made corrections and suggestions before
returning to OP.
Best to have you open event call another sub instead of having in
incorporated. Easier to test.
Using with can do more with each worksheet, etc

Sub foreachws1() 'like workbookopen except more efficient.

On Error Resume Next ' to skip non existent
For i = 1 To 8
With Sheets("sheet" & i)
.Unprotect Password:="password"
.Activate
ActiveWindow.DisplayHeadings = False
.Visible = True
.Protect Password:="password"
'MsgBox .Name
End With
Next i
End Sub

 

[Rick Rothstein]

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.