Solved: Workbook_BeforeClose

[Ger]

Is it possible that the workbook don't ask "saves the changes" but simply stores the changes made bij this macro?


[VBA]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
[/VBA]

Ger

[mancubus]

hi.

add this line before End Sub

[VBA]ThisWorkbook.Save[/VBA]

[Ger]

get an error:

Sub of function not defined

Ger

[Bob Phillips]

What does it show in the Microsoft Objects list for the project in question in the VBIDE. Maybe Dutch Excel is not ThisWorkbook?

[Ger]

oeps,

putted a gap between this and workbook.

It works fine.

Thx

Ger

[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]

What is pw2?

[GTO]

A total guess. Might pw2 be a Boolean indicating that the user entered an acceptable second level password?

[Bob Phillips]

I thought that, but tried to check.

At a guess, maybe this does what you want

[vba]

Private Sub Workbook_BeforeClose(Cancel As Boolean)
If pw1 Then

With Me

With .Worksheets("2012")

.Unprotect "zima"
.Range("A205:OC314", "D205284", "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
[/vba]

[Ger]

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]

I changed a few things (the , between the ranges gives an error).
This works fine.
What does "with me" do?

[VBA]Private Sub Workbook_BeforeClose(Cancel As Boolean)
If pw1 = True Then

With Me

With .Worksheets("2012")

.Unprotect "zima"
.Range("A205:OC314").FormulaHidden = True
.Range("D205284").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
[/VBA]

Ger

[Aflatoon]

'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]

Thx,

Ger

[Bob Phillips]

I changed a few things (the , between the ranges gives an error).

Odd, they worked for me.

[Aflatoon]

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.