Private function not working

[Pobek]

I have this private function before closing a workbook:

Private Sub Workbook_BeforeClose(Cancel As Boolean)

Call SO
End Sub



Sub SO()

Worksheets("setUp").Unprotect Password:="xcxcxcxc"
Sheets("setUp").Visible = True

If Range("NOP").Value < Now Then
Range("NOC").Value = Now
Else
Range("NOC").Value = Range("NOP").Value + 0.00012
End If

Worksheets("setup").Protect Password:="xcxcxcxc"




SO ais meant to perform some updates on some protected and hidden sheets before closing. SO runs FINE run by itself... HOWEVER it falls over (at this stage: "Range("NOC").Value = Now") unable to update information on a locked sheet when called by the private function. What am I doing wrong??
'
The Sub F8s fine by itself, but unable to conduct the same operation when called by a another sub that closes the workbook (thereby engaging the private function before closing)

 

[rlv01]

What is the specific excel/vba error message you are getting? If ranges "NOP" & "NOC" are both on worksheet "setUp" then try this

Sub SO()
    With Worksheets("setUp")
        .Unprotect Password:="xcxcxcxc"
        .Visible = True

        If .Range("NOP").Value < Now Then
            .Range("NOC").Value = Now
        Else
            .Range("NOC").Value = Range("NOP").Value + 0.00012
        End If

        .Protect Password:="xcxcxcxc"
    End With

BTW, for clarity when posting code:

 

[Pobek]

Cheers, But its still falling over.

 

[rlv01]

"Falling over" is not much to go on.

 

[Pobek]

"Falling over" is not much to go on.

Put it this way, your initial suggestion had no effect. The code is still crashing at the same spot of editing the sell in the "supposedly" unlocked sheet.

 

[Pobek]

What has been discovered is that the Sub: SO works fine (by itself), however if another sub tries to close down the workbook with a [workbook.close], thats when the code falls over.

 

[rlv01]

For some reason, the worksheet unprotect method does not work if the close command is from the vba workbook.close statement. You could work around it by explicitly unprotecting the worksheet just before you close it. The protect method seems to work so "SO" would re-lock the sheet once it runs.

Sub SomeOtherSubThatClosesTheWorkbook()
    Dim WorkBookToClose As Workbook

    'This example assumes the workbook is already open
    Set WorkBookToClose = Workbooks("MySOWorkbook.xlsm")    'Where "MySOWorkbook.xlsm" is your workbook that has the macro SO in it.

    WorkBookToClose.Worksheets("setUp").Unprotect Password:="xcxcxcxc"
    WorkBookToClose.Close False    'or True, your choice
End Sub

 

[Pobek]

For some reason, the worksheet unprotect method does not work if the close command is from the vba workbook.close statement. You could work around it by explicitly unprotecting the worksheet just before you close it. The protect method seems to work so "SO" would re-lock the sheet once it runs.

Sub SomeOtherSubThatClosesTheWorkbook()
    Dim WorkBookToClose As Workbook

    'This example assumes the workbook is already open
    Set WorkBookToClose = Workbooks("MySOWorkbook.xlsm")    'Where "MySOWorkbook.xlsm" is your workbook that has the macro SO in it.

    WorkBookToClose.Worksheets("setUp").Unprotect Password:="xcxcxcxc"
    WorkBookToClose.Close False    'or True, your choice
End Sub

Hey, thanks for your assistance. I will try this too and analyze the effect.

I did manage to get it working. You were right in the sense that the "workbook.close" statement was ineffective in editing the SETUP sheet. However I put in the code:

[userinterfaceonly:=True]

in the last line of the private function when opening (see below) and it ensured that the VBA could effect the desired editing.

[Worksheets("setup").Protect Password:="pobekpan", userinterfaceonly:=True]