| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| conditional visibility of check box | Excel Discussion (Misc queries) | |||
| help with code for conditional summary sheet | Excel Discussion (Misc queries) | |||
| Ensure macros enabled - C. Pearson's sheet visibility method | Excel Programming | |||
| Toggle sheet visibility | Excel Programming | |||
| Excel 2002? Where to store code for all spreadsheet visibility? | Excel Programming | |||
Hybrid Mode
