PDA

View Full Version : Solved: Workbook_BeforeClose



Ger
12-06-2011, 02:07 AM
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

Ger
12-06-2011, 02:17 AM
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?

Ger
12-06-2011, 02:24 AM
oeps,

putted a gap between this and workbook.

It works fine.

Thx

Ger

Ger
12-06-2011, 02:32 AM
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?

GTO
12-06-2011, 03:49 AM
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

Ger
12-06-2011, 03:55 AM
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

Ger
12-06-2011, 05:28 AM
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.

Ger
12-06-2011, 06:21 AM
Thx,

Ger

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.