Save workbook structure

[dbischa]

Hi,

I have tried to do this on my own but have not succeeded. I want to have a code that saves the structure of the workbook like the visibility of spreadsheets if the user elects not to save changes. I have a code that I got from somewhere that can run code when the workbook is really closing (so the before close event doesn't get tricked) so I want to put this code in there.

Thanks

Daniel

 

[dbischa]

Sorry if my first post was a bit short. I will try to explain more about what I want to do.

I have a workbook that has multiple sheets, the first of which is called "start" and is the only visible sheet when a user opens the workbook. Part of the code that initiates when the workbook opens checks the environ (username) and only makes the other sheets visible if the username satisfies an if function. I want to be able to hide all the sheets when a user closes the worksheet. If the user saves their changes this is not a problem as my code deals with that really well, but if they don't want to save changes my code forces the changes to be saved.

If it's not possible to save the structure without saving changes, is it possible to revert changes back to the last save state and then save the workbook?

Thanks for any help in advance.

 

[dbischa]

Sorry about double post.

 

[GTO]

Hi,

I have tried to do this on my own but have not succeeded. I want to have a code that saves the structure of the workbook like the visibility of spreadsheets if the user elects not to save changes. I have a code that I got from somewhere that can run code when the workbook is really closing (so the before close event doesn't get tricked) so I want to put this code in there.

Thanks

Daniel

If I am understanding, try this instead:

1. upon opening, show whatever sheets based upon user.
2. At each save, use the BeforeSave event. In BeforeSave, set a flag to prevent recurse, kill the initiated save, hide sheets, Save, unhide sheets based on user, mark wb as saved.

This way works better than using before close.

Mark

 

[dbischa]

Thanks! I think that will work, and I have had a go at trying to do it but my knowledge of VBA isn't that good. The before close event code that I have uses a command button that is killed if the close event doesn't fire. Is this what you mean by a flag? Either way I'm not sure exactly how to do what you have said (although I know what it will do).

Would you mind telling me how to set the flag, kill the save and then mark the workbook as saved? The bits in between I can do myself (I think).

 

[dbischa]

No worries, I adapted to the beforeclose code I had to do the job just as you suggested.

In case anyone out there wants to know what the code looks like here it is:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
gbSaving = True
gdCheckTime = Now + TimeSerial(0, 0, 1) 'Define time
Application.OnTime gdCheckTime, "CheckIfSaved" 'Schedule macro
End Sub
 
 
Private Sub Workbook_Open()
Dim cb As CommandBar
Dim cbb As CommandBarButton
On Error Resume Next
Application.CommandBars("MyBar").Delete
On Error GoTo 0
Set cb = Application.CommandBars.Add("MyBar") 
Set cbb = cb.Controls.Add(msoControlButton)
cbb.Caption = "Tester"
cbb.Style = msoButtonCaption
cb.Visible = True
End Sub
 
 
Private Sub Workbook_Deactivate()
If gbSaving Then
On Error Resume Next
Application.CommandBars("MyBar").Delete
On Error GoTo 0
'Delete scheduled macro
Application.OnTime gdCheckTime, "CheckIfSaved", , False
 
'Hide, save and unhide code goes in here
 
End If
ThisWorkbook.Saved = True
End If
End Sub

And then in a standard module I have:

Public gdCheckTime As Date
Public gbSaving As Boolean
 
 
Sub CheckIfSaved() 
   'If workbook has not been deactivated, this macro
   'will run and reset the global variable.
   gbSaving = False
 
End Sub

 

[GTO]

Hi dbischa,

Thanks! I think that will work, and I have had a go at trying to do it but my knowledge of VBA isn't that good. The before close event code that I have uses a command button that is killed if the close event doesn't fire. Is this what you mean by a flag? Either way I'm not sure exactly how to do what you have said (although I know what it will do).

Would you mind telling me how to set the flag, kill the save and then mark the workbook as saved? The bits in between I can do myself (I think).

I am overly tired and quite often thick-headed, so didn't quite follow killing a button etc. It appears by your last however, that you have done well in solving. What I meant by my last, in an admittedly rudimentary fashion would be something like:

Blank/New wb with 3 sheets, using default codenames.

Option Explicit
    
Dim UserName As String
Dim Users() As Variant
Dim bInProcess As Boolean
    
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    
    If Not bInProcess Then
        bInProcess = True
        Sheet1.Visible = xlSheetVisible
        Sheet2.Visible = xlSheetVeryHidden
        Sheet3.Visible = xlSheetVeryHidden
        Me.Save
        Cancel = True
        If Not IsError(Application.Match(Environ("UserName"), Users, 0)) Then
            Sheet2.Visible = xlSheetVisible
            Sheet3.Visible = xlSheetVisible
            Sheet1.Visible = xlSheetHidden
            Me.Saved = True
        End If
        bInProcess = False
    End If
End Sub
    
Private Sub Workbook_Open()
    
    Users() = Array("JON", "MARK", "ALICIA")
    UserName = Environ("UserName")
    
    If Not IsError(Application.Match(UserName, Users, 0)) Then
        Sheet2.Visible = xlSheetVisible
        Sheet3.Visible = xlSheetVisible
        Sheet1.Visible = xlSheetHidden
        Me.Saved = True
    End If
End Sub

In gist, upon Open, see if we like the user. Yes? Show whatever sheets and (optionally) hide our "bug-off" sheet to non-authorized personnel.

If the user has macros disabled, no harm, as the other sheets are not exposed.

User OK + macros enabled: Ea time a save is called, we first hide stuff, then save, then unhide stuff and mark wb saved (just to eliminate pesky question from Excel if user then closes w/o further changes).

Thus, the wb is never actually in a saved status with our fanny hanging out (sheets visible).

As mentioned, I didn't exactly follow all the solution, but looks nice

As a small comment, when I want a custom commandbar available to the wb, I use the Activate and Deactivate events. Activate runs after Open anyways (and Deactivate is fired before BeforeClose), so the CBAr is always restricted to teh WB.

hope that helps,

Mark

 

[GTO]

ACK! Sorry - even as a simple example, that missed the mark, as choosing Save=Yes on closing an unsaved wb will loop...

Option Explicit
    
Dim UserName As String
Dim Users() As Variant
Dim bInProcess As Boolean
Dim bClosing As Boolean
    
Private Sub Workbook_BeforeClose(Cancel As Boolean)
    
    If Not bClosing Then
        bClosing = True
        If Not Me.Saved Then
            Select Case MsgBox("Not saved.  Save?", vbYesNo Or vbQuestion, "ACK!")
            Case vbYes
                Workbook_BeforeSave False, False
            Case Else
                ThisWorkbook.Close False
            End Select
        End If
    End If
End Sub
    
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    
    If Not bInProcess Then
        bInProcess = True
        Sheet1.Visible = xlSheetVisible
        Sheet2.Visible = xlSheetVeryHidden
        Sheet3.Visible = xlSheetVeryHidden
        Me.Save
        Cancel = True
        If Not IsError(Application.Match(Environ("UserName"), Users, 0)) Then
            Sheet2.Visible = xlSheetVisible
            Sheet3.Visible = xlSheetVisible
            Sheet1.Visible = xlSheetHidden
            Me.Saved = True
        End If
        bInProcess = False
    End If
End Sub
    
Private Sub Workbook_Open()
    
    Users() = Array("JON", "MARK", "ALICIA")
    UserName = Environ("UserName")
    
    If Not IsError(Application.Match(UserName, Users, 0)) Then
        Sheet2.Visible = xlSheetVisible
        Sheet3.Visible = xlSheetVisible
        Sheet1.Visible = xlSheetHidden
        Me.Saved = True
    End If
End Sub

 

[dbischa]

Thanks for your help Mark .

The code I posted in the end did not work because the code that was in the workbook_deactivate event needed to go in the workbook_beforesave event. Once I moved it there it worked just as I needed it to...except:

If I have another workbook open at the same time, I cannot select either of them! Is this what you were saying about the command bar? I think I am playing with things I don't understand.

I will try your code then and see if it works. Not sure if I will be able to give it a go today though.

Thanks again!

 

[dbischa]

Hi again,

I tried your code, but it doesn't allow saveas to work, it just saves over the top of the original file and I don't know how to fix this. I gave my own code another go and the same problem didn't occur, tried to replicate it but it didn't happen again. So I've stuck with my own solution and it is working fine.