Excel Hints

Results 1 to 5 of 5

Thread: Solved: SaveAsUI.... cleanup before close.

  1. #1

    Solved: SaveAsUI.... cleanup before close.

    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:
    VB:
    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 
    
    
    Formatting tags added by mark007
    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:
    VB:
    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 
    
    
    Formatting tags added by mark007
    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!

  2. #2
    Distinguished Lord of VBAX VBAX Grand Master xld's Avatar
    Joined
    Apr 2005
    Posts
    23,993
    Location
    VB:
     
    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 
    
    
    Formatting tags added by mark007
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  3. #3

    Doh!


    Thanks,

    Any ideas on passing this a variable?
    The following is the code that allows access to said sheets.
    VB:
    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 
    
    
    Formatting tags added by mark007
    If I check the value of openSh when Workbook_BeforeSave runs it comes up false...

  4. #4
    Distinguished Lord of VBAX VBAX Grand Master xld's Avatar
    Joined
    Apr 2005
    Posts
    23,993
    Location
    Set var1 as a Global variable and check that in the procedure.

    You can improve that code

    VB:
     
    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 
    
    
    Formatting tags added by mark007
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  5. #5
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •