Solved: SaveAsUI.... cleanup before close.

[Papadopoulos]

So I have found some nifty code that will allow me to prevent users from saving the document under a different name or location (credit: OzGrid)
It seems to work fine until I try and do a little house keeping mid-stream.

This is the original code:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) 
    Dim iReply As Integer 
    If SaveAsUI = True Then 
        iReply = MsgBox("Sorry, you are not allowed to save this Workbook as another name. " _ 
        & "Do you wish to save this workbook.", vbQuestion + vbOKCancel) 
        Cancel = (iReply = vbCancel) 
        If Cancel = False Me.Save 
        Cancel = True 
    End If 
End Sub 

VBA tags courtesy of www.thecodenet.com

I however want to hide a few sheets that might have been made visible via code.
(I would really like to pass a variable to this bit of code so that it only tries to hide the sheets if they have been made visible - I would set the variable in the procedure that makes the sheets visible rather than going through the process of checking to see if they are visible)

My attempt at the sheet hiding part is as follows:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) 
    Dim iReply As Integer 
    If SaveAsUI = True Then 
        iReply = MsgBox("Sorry, you are not allowed to save this Workbook as another name. " _ 
        & "Do you wish to save this workbook.", vbQuestion + vbOKCancel) 
        Cancel = (iReply = vbCancel) 
        If Cancel = False Then ' to here
            Sheets("ref").Visible = False ' this line
            Sheets("otherRef").Visible = False 
            Sheets("stockRef").Visible = False 
            Sheets("sizeRef").Visible = False 
            Sheets("finishingRef").Visible = False 
            Sheets("apphistory").Visible = False 
            Me.Save 
            Cancel = True 
        End If 
    End Sub 

VBA tags courtesy of www.thecodenet.com

This version yields an error: Block If without End If.
If I back the line commented as "this line" up onto the end of the line commented as "to here" it works but doesn't hide sheet "ref"

Thanks again.
You guys rock!

[xld]

 
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) 
    Dim iReply As Integer 
    If SaveAsUI = True Then 
        iReply = MsgBox("Sorry, you are not allowed to save this Workbook as another name. " _ 
        & "Do you wish to save this workbook.", vbQuestion + vbOKCancel) 
        Cancel = (iReply = vbCancel) 
        If Cancel = False Then ' to here
            Sheets("ref").Visible = False ' this line
            Sheets("otherRef").Visible = False 
            Sheets("stockRef").Visible = False 
            Sheets("sizeRef").Visible = False 
            Sheets("finishingRef").Visible = False 
            Sheets("apphistory").Visible = False 
            Me.Save 
            Cancel = True 
        End If 
    End If 
End Sub 

VBA tags courtesy of www.thecodenet.com

[Papadopoulos]

Thanks,

Any ideas on passing this a variable?
The following is the code that allows access to said sheets.

Sub showSheets() 
     '
    Dim var1 As String 
    Dim ok As Boolean 
     
    var1 = InputBox("Access to these sheed requires a password.", "Password") 
    If var1 = "bis" Then 
        ok = True 
    Else 
        ok = False 
    End If 
     
    If ok = True Then 
        Sheets("ref").Visible = True 
        Sheets("otherRef").Visible = True 
        Sheets("stockRef").Visible = True 
        Sheets("sizeRef").Visible = True 
        Sheets("finishingRef").Visible = True 
        Sheets("apphistory").Visible = True 
        openSh = True 
        MsgBox openSh 
    Else 
        MsgBox ("You have not entered the correct password") 
    End If 
End Sub 

VBA tags courtesy of www.thecodenet.com

If I check the value of openSh when Workbook_BeforeSave runs it comes up false...

[xld]

Set var1 as a Global variable and check that in the procedure.

You can improve that code

 
Sub showSheets() 
     '
    Dim var1 As String 
     
    var1 = InputBox("Access to these sheed requires a password.", "Password") 
    If var1 = "bis" Then 
         
        Sheets("ref").Visible = True 
        Sheets("otherRef").Visible = True 
        Sheets("stockRef").Visible = True 
        Sheets("sizeRef").Visible = True 
        Sheets("finishingRef").Visible = True 
        Sheets("apphistory").Visible = True 
        openSh = True 
        MsgBox openSh 
    Else 
        MsgBox ("You have not entered the correct password") 
    End If 
End Sub 

VBA tags courtesy of www.thecodenet.com

[Papadopoulos]

I could clean up my code.... (that is probably a foregone conclusion, chuckle)
Thanks.
I actually managed to get my project to recognize that the sheets were made visible and hid them on save.

Thank you so much.
As time goes on I get farther and farther along before have to ask you guys for help.
Maybe someday I'll be able to help others.