View Full Version : Solved: Workbook_BeforeClose
Is it possible that the workbook don't ask "saves the changes" but simply stores the changes made bij this macro?
Private Sub Workbook_BeforeClose(Cancel As Boolean)
If pw1 = True Then
Sheets("2012").Select
ActiveSheet.Unprotect "zima"
Range("i205:OC284").Locked = True
Range("d205:d284").Locked = True
Range("A312:OC313").Locked = True
Range("A205:OC314").FormulaHidden = True
ActiveSheet.Protect password:="zima", DrawingObjects:=False, Contents:=False, Scenarios:= _
False, AllowFormattingCells:=False
Rows("205:314").Hidden = True
End If
End Sub
Ger
mancubus
12-06-2011, 02:11 AM
hi.
add this line before End Sub
ThisWorkbook.Save
get an error:
Sub of function not defined
Ger
Bob Phillips
12-06-2011, 02:20 AM
What does it show in the Microsoft Objects list for the project in question in the VBIDE. Maybe Dutch Excel is not ThisWorkbook?
oeps,
putted a gap between this and workbook.
It works fine.
Thx
Ger
Next problem. users with pw2 get an error because they open the workbook as read only.
which code must i use to exit the workbook without saving for users with pw2?
Ger
Bob Phillips
12-06-2011, 03:40 AM
What is pw2?
A total guess. Might pw2 be a Boolean indicating that the user entered an acceptable second level password?
Bob Phillips
12-06-2011, 03:52 AM
I thought that, but tried to check.
At a guess, maybe this does what you want
Private Sub Workbook_BeforeClose(Cancel As Boolean)
If pw1 Then
With Me
With .Worksheets("2012")
.Unprotect "zima"
.Range("A205:OC314", "D205:D284", "I205:OC284").FormulaHidden = True
.Rows("205:314").Hidden = True
.Protect Password:="zima"
End With
.Save
End With
ElseIf pw2 Then
Me.Saved = True
End If
End Sub
There are 2 kind of users. each with his own password (pw1 and pw2). Users with pw1 may change and save. Users with pw2 may only look (read only).
I think in the code there must be something like if pw2 = true then exit without saving elseif pw2 = true then Thisworkbook.save
Ger
I changed a few things (the , between the ranges gives an error).
This works fine.
What does "with me" do?
Private Sub Workbook_BeforeClose(Cancel As Boolean)
If pw1 = True Then
With Me
With .Worksheets("2012")
.Unprotect "zima"
.Range("A205:OC314").FormulaHidden = True
.Range("D205:D284").FormulaHidden = True
Range("I205:OC284").FormulaHidden = True
.Rows("205:314").Hidden = True
.Protect password:="zima"
End With
.Save
End With
ElseIf pw2 = True Then
Me.Saved = True
End If
End Sub
Ger
Aflatoon
12-06-2011, 06:10 AM
'Me' refers to the object in which the code is running (in this case ThisWorkbook). The With...End With block simply qualifies all the calls within it that are prefixed with a period - i.e. .Worksheets("2012") and .Save in this case - as relating to the ThisWorkbook object.
Bob Phillips
12-06-2011, 08:08 AM
I changed a few things (the , between the ranges gives an error).
Odd, they worked for me.
Aflatoon
12-06-2011, 08:30 AM
Odd, they worked for me.
The commas would need to be within the inverted commas, otherwise you are passing three arguments to the Range property.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.