Event loop

[tleth]

Hi

Im experiencing a very strange bug in a VBA project im maintaining.

The workbook contains a list of testusers and the properties they have. The VBA is there to hide or show the password for each of the testusers depending on the person viewing.

In order to make sure that the passwords are always hidden as default (incase some user who isnt supposed to view the passwords has macroes disabled), there are some code in place to handle the BeforeSave and Open events.

The open event handler initializes some global variables, shows the passwords the person is allow to see and then sets ThisWorkbook.saved = True.
This way, if the person closes the workbook after they find a suitable testuser without making any changes, they wont see a dialog asking them to save their changes, and as a result hitting the x wont save the workbook with the passwords shown.

The BeforeSave method code is as follows:

    Application.EnableEvents = False
    Application.ScreenUpdating = False
    Call viewPasswords(False)
    ThisWorkbook.Save
    Call viewPasswords(True)
    ThisWorkbook.saved = True
 
    Application.ScreenUpdating = True
    Application.EnableEvents = True
    Cancel = True

Which from what I've gathered from various forums posts is the common way to do thing before and after the save occurs. (Hide passwords in the saved version, but then show them again).

The problem im having is that sometimes when a user makes a change to the workbook and hits the X to close, and then clicks yes to the dialog, the dialog keeps reappearing everytime they hit yes.

The really strange part is, that I've only been able to reproduce this bug when I have a second, totally unrelated, workbook open (can be empty, it doesn't matter). In this case the bug is very consistent.

Can the other workbook somehow effect the VBA code in this project?
If so how can I protect against it?

I thank you in advance for helping me

Kind regards
Troels

 

[tleth]

Forgot to mention, it is a Excel 2007 project under XP.

 

[tleth]

Bump

 

[GTO]

Hi there,

I'm clueless about having another wb open, but if you have ThisWorkbook.Save in BeforeSave, you are recursing to BeforeSave. A bit of a stab w/o seeing more (viewpasswords for instance) of the code, but I think you may want to Cancel the Save initially in BeforeSave, set a flag to prevent recurse, call your procedure, then Save. I hope that makes some sense...

 

[tleth]

Yes, it confues me greatly. I havn't experienced the bug when the only file that is open is the project.

Shouldn't Application.EnableEvents = False prevent recursion?
Changing the method to the following didn't solve he problem atleast.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    If saving Then
        Cancel = True
        Exit Sub
    End If
 
    saving = True
 
    Application.EnableEvents = False
 
    Application.ScreenUpdating = False
 
    Call viewPasswords(False)
 
    ThisWorkbook.Save
 
    Call viewPasswords(True)
 
    ThisWorkbook.saved = True
 
    Application.ScreenUpdating = True
    Application.EnableEvents = True
 
    saving = False
    Cancel = True
End Sub

Where saving is a globally defined variable.

The View passwords is defined as such:

Private Sub viewPasswords(view As Boolean)
    Dim brugerInfo As Worksheet, _
        authData As Worksheet, _
        raTestUsers As Range, _
        authed As Boolean, _
        edit As Boolean
    On Error GoTo ViewError
    Call unlockWS
 
    Set brugerInfo = ThisWorkbook.Worksheets.Item("users")
    Set authData = ThisWorkbook.Worksheets.Item("authData")
    Set raTestUsers = brugerInfo.Range(brugerInfo.Cells(5, 2), brugerInfo.Cells(authData.Range("E5").Value, 5))
 
    For Each user In raTestUsers.Rows
        authed = authData.Range(user.Cells(2).Address).Value
        edit = authData.Range("F5").Value
        With user
            If Not .Cells(1) = "" Then
            If authed And view Then
                .Cells(2).NumberFormat = "@"
                .Cells(2).Locked = False
 
                .Cells(3).NumberFormat = "@"
                .Cells(3).Locked = False
            Else
                .Cells(2).NumberFormat = ";;;"
                .Cells(2).Locked = True
 
                .Cells(3).NumberFormat = ";;;"
                .Cells(3).Locked = True
            End If
 
            If edit And view Then
                .Cells(4).Locked = False
            Else
                .Cells(4).Locked = True
            End If
            End If
        End With
    Next
    Call lockWS
    Exit Sub
 
ViewError:
    MsgBox ("Fejl i view")
End Sub

Also, I don't know it was clear in my original message, but the loop only arises when the user tries to exit the project, saving it with CTRL + s works just fine.

 

[tleth]

Shameless bump

 

[tleth]

Bump

 

[Jaafar Tribak]

Hi

Im experiencing a very strange bug in a VBA project im maintaining.

The workbook contains a list of testusers and the properties they have. The VBA is there to hide or show the password for each of the testusers depending on the person viewing.

In order to make sure that the passwords are always hidden as default (incase some user who isnt supposed to view the passwords has macroes disabled), there are some code in place to handle the BeforeSave and Open events.

The open event handler initializes some global variables, shows the passwords the person is allow to see and then sets ThisWorkbook.saved = True.
This way, if the person closes the workbook after they find a suitable testuser without making any changes, they wont see a dialog asking them to save their changes, and as a result hitting the x wont save the workbook with the passwords shown.

The BeforeSave method code is as follows:

    Application.EnableEvents = False
    Application.ScreenUpdating = False
    Call viewPasswords(False)
    ThisWorkbook.Save
    Call viewPasswords(True)
    ThisWorkbook.saved = True
 
    Application.ScreenUpdating = True
    Application.EnableEvents = True
    Cancel = True

Which from what I've gathered from various forums posts is the common way to do thing before and after the save occurs. (Hide passwords in the saved version, but then show them again).

The problem im having is that sometimes when a user makes a change to the workbook and hits the X to close, and then clicks yes to the dialog, the dialog keeps reappearing everytime they hit yes.

The really strange part is, that I've only been able to reproduce this bug when I have a second, totally unrelated, workbook open (can be empty, it doesn't matter). In this case the bug is very consistent.

Can the other workbook somehow effect the VBA code in this project?
If so how can I protect against it?

I thank you in advance for helping me

Kind regards
Troels

I think the Cancel = True line is what is causing the problem. Just remove it and leave the rest as is.