PDA

View Full Version : Solved: SaveAsUI.... cleanup before close.



Papadopoulos
11-03-2010, 02:34 PM
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
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
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!

Bob Phillips
11-03-2010, 03:01 PM
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

Papadopoulos
11-03-2010, 03:15 PM
:doh:
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


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

Bob Phillips
11-03-2010, 03:23 PM
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

Papadopoulos
11-03-2010, 03:58 PM
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.