Alt+F11 및 VBE창 금지/ 활성화

지식인

 

VBA에 암호를 거는 방법 이외에 VBA접근을 최대한 막는 방법입니다.

물론 최초 실행시에 매크로를 허용해줘야만 합니다.

 

현재는 Alt-F11 키와 Alt-F8키를 중지시키고

개발도구메뉴를 감춥니다.

Alt-F12를 누르면 다시 활성화합니다. 실제로 사용할 때는 이부분을 삭제해야겠습니다.

'https://www.mrexcel.com/board/threads/deactivate-alt-f11.23976/

Option Explicit

'// Tested Excel2000
'// Run DisableVBE from a Event procedure
'// eg. Workbook_Open or Worksheet_Activate
'// THEN run EnableVBE from the Opp Event procedure
'// eg. Workbook_Close or Worksheet_Deactivate
'//=====================================================================
'// Note:
'// In order to Disable access into the VBA Editor
'// you must Disable ALL references to Access the code
'// eg Macros...dialog,View Code - available via RighClick on Sheet Tab
'// Record New Macro..., and also Design Mode as the User can put the
'// workbook in design mode then select a control & double clcik to
'// view code. Alo you need to Disable the Custom Toolbar List AND
'// the fact that Double clicking ANY area of the commandbars will
'// also give you the Customize Toolbars Option Dialog.

'// The following Routine Takes care of this.

Sub Auto_Open()

    DisableVBE
    
End Sub

Private Sub DisableVBE()

    Application.VBE.MainWindow.Visible = False '// Close ALL windows 1st!
    CmdControl 30017, False 'Macro
    CmdControl 30062, False 'Macro
    CmdControl 859, False 'Macro
    CmdControl 1695, False '// Visual basics Editor
    CmdControl 186, False '// Macros...
    CmdControl 184, False '// Record New Macro...
    CmdControl 1561, False '// View Code
    CmdControl 1605, False '// Design Mode
    Application.OnDoubleClick = "Dummy"
    CommandBars("ToolBar List").Enabled = False
    Application.ShowDevTools = False
    Application.OnKey "%{F11}", "Dummy"
    Application.OnKey "%{F8}", "Dummy"
    '실제로는 아래 줄 삭제
    Application.OnKey "%{F12}", "EnableVBE"

End Sub

Private Sub EnableVBE()
    CmdControl 30017, True 'Macro
    CmdControl 30062, True 'Macro
    CmdControl 859, True 'Macro
    CmdControl 1695, True '// Visual basics Editor
    CmdControl 186, True '// Macros...
    CmdControl 184, True '// Record New Macro...
    CmdControl 1561, True '// View Code
    CmdControl 1605, True '// Design Mode
    Application.OnDoubleClick = ""
    CommandBars("ToolBar List").Enabled = True
    Application.ShowDevTools = True
    Application.OnKey "%{F11}"
    Application.OnKey "%{F8}"

End Sub

Function CmdControl(Id As Integer, TF As Boolean)

    Dim CBar As CommandBar
    Dim C As CommandBarControl
      
    On Error Resume Next
    For Each CBar In Application.CommandBars

        Set C = CBar.FindControl(Id:=Id, recursive:=True)
        If Not C Is Nothing Then C.Enabled = TF
    
    Next
    
End Function

Private Sub listCmdControl()

    Dim CBar As CommandBar
    Dim C As CommandBarControl
      
    On Error Resume Next
    For Each CBar In Application.CommandBars
        Debug.Print CBar.Name; CBar.NameLocal
        For Each C In CBar.Controls
            If InStr(C.Caption, "매크로") Then Debug.Print C.Id; C.Caption; C.DescriptionText
        Next C
    Next

End Sub

Private Sub Dummy()

    '// NoGo, leave blank OR Display a message eg.

    MsgBox "Sorry, this command is NOT available", vbCritical

End Sub

 

샘플 파일:

DisableVBE1.xlsm

0.02MB