Hideing Sheet Tabs

[Avatar]

Greetings,

Can someone tell me why this hides all sheet tabs on all open workbooks instead of just the active one please? I'll be damned if i can see why it's doing it..

Set aWorkbook = ActiveWorkbook

counterstop = 1
Do Until counterstop = Sheets.Count + 1
aWorkbook.Sheets(counterstop).Visible = False
counterstop = counterstop + 1
Loop

 

[Cbrine]

Avatar,

The counter is cycling through a total count of workbooks. On each interation it's doing

Sheets(1).visible =false

Next

Sheets(2).visible = false.

etc...

If you only want to hide the activeworkbook, then do.
Activeworkbook.visible = false
Without any looping

If you want to hide all but a specific sheet do this.

Set aWorkbook = ActiveWorkbook

counterstop = 1
Do Until counterstop = Sheets.Count + 1
If counterstop <> 1 then aWorkbook.Sheets(counterstop).Visible = False
counterstop = counterstop + 1
Loop

HTH
Cal

 

[Avatar]

Thanks for the responce, but i think i phrased that a little unclearly..

I meant i want it to hide all the sheets on the active workbook and not the inactive workbooks. currently it's hiding all the sheets on all workbooks instead of just the active workbook.

basically I hide all the sheets, then unhide the splash sheet (which is hidden during normal operation) then hide the last sheet. I then protect the workbook to prevent the other sheets from being unhidden by anyone else. At the moment, it's hiding all sheets on all workbooks and protecting all open workbooks which is intensly iritating, and getting me into trouble as i've managed to hide and protect 3 managment figure sheets without my knowledge.

 

[Avatar]

Incase it's somthing else i'm doing in the sub here's the whole thing including the Workbook_BeforeClose.

Private Sub Workbook_BeforeClose(Cancel As Boolean)

If LogicLickOut = True Then Exit Sub

LogicLickOut = True

If unauthorisedaccess = True Then Exit Sub

Call SaveAndExit

'Cancel = True

End Sub



Sub SaveAndExit()
Dim aWorksheet As Worksheet
Dim aWorkbook As Workbook

If logiccutout = True Then Exit Sub

logiccutout = True

hidesheets:

Application.ScreenUpdating = False
ProtectionWorkbookDeactivate

Set aWorkbook = ActiveWorkbook

On Error Resume Next
counterstop = 1
Do Until counterstop = Sheets.Count + 1
aWorkbook.Sheets(counterstop).Visible = False
counterstop = counterstop + 1
Loop

If ActiveSheet.Name <> "Login" Then sheetname1 = ActiveSheet.Name: aWorkbook.Sheets("Login").Visible = True: Sheets(sheetname1).Visible = False

ProtectionWorkbookActivate
Application.ScreenUpdating = True

If ActiveWorkbook.ReadOnly = False Then ActiveWorkbook.Save
'ActiveWorkbook.Close savechanges:=False

End Sub

If your wondering about the "LogicLickOut" and "logiccutout", it's because it has a rather iritating habbit of rerunning itself even without the last line in.

EDIT: LogicLickOut?? lol Opps, i never even noticed that typo lol

 

[Cbrine]

A couple of things I noticed.

1. You can't hide all the worksheet in a workbook, one must remain, you should put your filter within the loop.

2. You should qualify the sheets.count with the workbook object.(Don't think this is the problem, but you never know)


Do Until counterstop = aWorkbook.Sheets.Count + 1

3. Your commented close code
'ActiveWorkbook.Close savechanges:=False
causes a before close event to fire, which recursively calls the before close event.
Try this.

Application.enableevents =false
ActiveWorkbook.Close savechanges:=False
Application.enableevents = true

This should help eliminate the logiclick=true stuff.
Which I think I answered in another post for you. Which explains why you were asking about it.

Does your ProtectionWorkbookDeactivate act on only the activeworkbook?


HTH
Cal

I guess it was supposed to be logicClickOut?? I just thought you might work for a **** company.

 

[Avatar]

hrm.. I think i just found what the problem is..

I have a user version of this (this is used in the management version) with the same code, but doesn't have the "LogicLickOut" in it, and still has "ActiveWorkbook.Close savechanges:=False" active so it just keeps rerunning the same procedure over and over until all workbooks have only one sheet visible and are all protected.. doah! (still not sure why it changes the activeworkbook though?)

lol kinda funny though, but it will get me into some serious trouble as 400 people use it. - Best get to work on that VBE correction workbook i started..

Well anyhow, yeah you are right the ProtectionWorkbookDeactivate and ProtectionWorkbookAactivate work on the activeworkbook as below:

Public Sub ProtectionWorkbookDeactivate()
ActiveWorkbook.Unprotect password:=<Password Replaced>
End Sub

Public Sub ProtectionWorkbookActivate()
ActiveWorkbook.Protect password:=<Password Replaced>, structure:=True, Windows:=False
End Sub

Yeah i think you did answer the enableevents

ooh, and btw, the LogicLickOut was meant to be LogicKickOut

 

[Avatar]

Greetings,

I've just started to notice a problem with the section below.

Application.enableevents =false
ActiveWorkbook.Close savechanges:=False
Application.enableevents = true

It works fine to close the workbook, but it never runs the last line "Application.enableevents = true" so, Application.enableevents is still equal to false after closure. This means when you go to open another workbook the workbook_open sub is not run. You have to restart the machine or manually run code to enable events to get it to work again.

erm.. Help please?

 

[Cbrine]

Avatar,
You have the close code in the before_close event. Looking at the code I can understand what's happening. Since the sheet closes the enableevents = true, never gets executed.

With the close occurring without the save, we can't get around this, but I think we may be able to trick excel into thinking the worksheet is already saved, even when it is not. Give this a try.

Replace

Application.enableevents =false 
ActiveWorkbook.Close savechanges:=False 
Application.enableevents = true

activeworkbook.saved = true

I ran a quick test, and it seems to work by making excel think the current workbook is already saved, so it just closes without trying to save.

HTH
Cal

 

[Avatar]

Thanks

 

[Avatar]

Greetings,

I've noticed that it's still doing the same thing of hiding sheet tabs for other workbooks. It seems to happen as follows:

You have some workbooks open, you then open this document. It then applies this workbooks closing settings to every open workbook when you try to close them.

Does anyone know how i can stop it applying the closure settings to all workbooks that are open?