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!
