|
Run code conditional on sheet visibility
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 |
Run code conditional on sheet visibility
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 "Sandy" wrote: 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 |
Run code conditional on sheet visibility
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" wrote: 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 |
Run code conditional on sheet visibility
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 |
Run code conditional on sheet visibility
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 "Mike H" wrote in message ... 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 "Sandy" wrote: 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 |
Run code conditional on sheet visibility
Hi Mike
Works perfectly! Thank You "Mike" wrote in message ... 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" wrote: 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 |
Run code conditional on sheet visibility
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 |
Run code conditional on sheet visibility
No Problem
"Sandy" wrote: Hi Mike Works perfectly! Thank You "Mike" wrote in message ... 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" wrote: 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 |
Run code conditional on sheet visibility
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 |
Run code conditional on sheet visibility
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 |
Run code conditional on sheet visibility
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 |
Run code conditional on sheet visibility
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 |
Run code conditional on sheet visibility
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 |
Run code conditional on sheet visibility
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 |
Run code conditional on sheet visibility
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 -- Don Guillett Microsoft MVP Excel SalesAid Software "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 |
Run code conditional on sheet visibility
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 |
| All times are GMT +1. The time now is 08:46 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com