Lock workbook

[Guest_imported]

How can I lock the possibility to close WorkBook with the cross (X) at the right top of the workbook?

 

[Chattin]

I think Tools>Protection>Workbook should do it - remember to select the Windows and Structure options and specify a password

 

[Guest_imported]

Yes; but if I want to do it in VBA procedure?

 

[Chattin]

I'm still learning VBA myself but if you record a macro it will give you the code to protect a work book something like (don't quote me - its off the top of my head!)

Activeworkbook.Protect Scenarios=True etc.

Insert
Password: "password of your choice"

immediately after the Activeworkbook.Protect bit - I think that should work

 

[kevinclark]

I think you will need to use API calls to do this. Try the following code and see if it works for you.


Public Const SC_CLOSE = &HF060
Public Const MF_BYCOMMAND = &H0
Declare Function GetActiveWindow Lib "User32" () As Integer
Public Declare Function GetSystemMenu Lib "User32" _
(ByVal hwnd As Long, ByVal bRevert As Long) As Long
Public Declare Function DeleteMenu Lib "User32" _
(ByVal hMenu As Long, ByVal nPosition As Long, ByVal wFlags As Long) As Long
Declare Function DrawMenuBar Lib "User32" (ByVal hwnd As Long) As Long




Public Sub DisableX()
Dim tmp As Long
tmp = GetActiveWindow()
Dim hMenu As Long
hMenu = GetSystemMenu(tmp, 0&)
If hMenu Then
DeleteMenu hMenu, SC_CLOSE, MF_BYCOMMAND
DrawMenuBar (tmp)
End If
End Sub


Public Sub RestoreX()
Dim hMenu As Long
Dim tmp As Long
tmp = GetActiveWindow()
hMenu = GetSystemMenu(hwnd, 1)
DrawMenuBar (tmp)
End Sub