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:
    [vba]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]
    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:
    [vba]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]
    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
    24,094
    Location
    [vba]

    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]
    ____________________________________________
    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.
    [VBA]
    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]

    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
    24,094
    Location
    Set var1 as a Global variable and check that in the procedure.

    You can improve that code

    [vba]

    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]
    ____________________________________________
    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
  •